MySQL数据库中的事务和隔离级别

思考(yiyin)一下场景

乔峰着急用钱,欲将游戏装备中的"汗血宝马"以180元的价格转让与张无忌。

# 装备表
CREATE TABLE `equipment`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`equipment` VARCHAR(50),
`price` DECIMAL(7,2),
`owner` TINYINT
)ENGINE=INNODB CHARSET=utf8;

# 用户表
CREATE TABLE `user`(
id INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR(50),
`money` DECIMAL(7,2)
)ENGINE=INNODB CHARSET=utf8; 

# 准备测试数据
INSERT INTO user(`username`,`money`) VALUES('乔峰',5);
INSERT INTO user(`username`,`money`) VALUES('张无忌',300);
INSERT INTO equipment(`equipment`,`price`,`owner`) VALUES('汗血宝马',180,1);
  1. 判断张无忌是否有180元

    select money from user where username = '张无忌';
  2. 从张无忌的账户中转出180元

    update user set money=money-180 where username = '张无忌';
  3. 向乔峰的账户中转入同样的数额

    update user set money=money+180 where username = '乔峰';
  4. 把“汗血宝马”的拥有者变成张无忌
    update equipment set owner=2 where equipment = '汗血宝马' and owner = 1;

思考问题: 假如:2/3/4步骤中有一条SQL执行其他SQL执行成功 会出现什么效果

使用事务解决

USE jkdb;
START TRANSACTION;  /*也可 begin*/
UPDATE USER SET money=money-180 WHERE username = '张无忌';
UPDATE USER SET money=money+180 WHERE username = '乔峰';
UPDATE equipment SET OWNER=2 WHERE equipment = '汗血宝马' AND OWNER = 1;

ROLLBACK;
SELECT * FROM USER;
SELECT * FROM equipment;

UPDATE USER SET money=money-180 WHERE username = '张无忌';
UPDATE USER SET money=money+180 WHERE username = '乔峰';
UPDATE equipment SET OWNER=2 WHERE equipment = '汗血宝马' AND OWNER = 1;
COMMIT;    /*提交*/
SELECT * FROM USER;
SELECT * FROM equipment;

一、什么是事务

事务是由多个SQL语句组成的一组操作,每个SQL语句相互依赖,如果其中一条SQL语句一旦执行失败,这组操作将会回滚。所有受影响的数据将返回事务开始以前的状态。如果一组中的所有SQL都执行成功,则这组操作才算执行成功。

MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理!其他的类型是不支持的! SHOW ENGINES; //查看当前存储引擎

二、事务的四个属性(简称ACID)

原子性(Atomicity)

原子性是指一个事务必须被视为一个不可分割的最小的工作单元,只有事务中所有的数据库操作都执行成功,才算整个事务执行成功。事务中如果有任何一个SQL语句执行失败,已经执行成功的SQL语句也必须撤销,数据库的状态退回执行事务前的状态。

一致性(Consistency)

一致性是指在事务处理时,无论执行成功还是失败,都要保证数据库系统处于一致的状态,保证数据库系统不会返回到一个未处理的事务中。MySQL中的一致性主要由日志机制实现,通过日志记录数据库的所有变化,为事务恢复提供了跟踪记录。

隔离性(IsoLation)

隔离性是指当一个事务在执行时,不会受到其他事务的影响。保证了未完成事务的所有操作与数据库系统的隔离,直到事务完成为止,才能看到事务的执行结果。隔离性相关的技术有并发控制、可串行化、锁等。当多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离

持久性(Durability)

持久性是指事务一旦提交,其对数据库的修改就是永久性。但是打雷劈服务器不算

三、PHP中使用事务

$link = mysqli_connect('localhost','root','root','demo') or die('数据库查询');

if( !mysqli_set_charset($link,'utf8') ){
    exit('字符集设置失败!');
}
# 开启事务
mysqli_query($link,'start transaction');

# 张无忌是否有购买能力
$sql = "select money from user where username = '张无忌'";
$result = mysqli_query($link,$sql);
if( !$result ){
    die('查询失败');
}
$rows = mysqli_fetch_assoc($result);
if($rows['money'] >= 20){
    $mark = true;

    $sql = "update user set money=money-20 where username = '张无忌'";
    $result = mysqli_query($link,$sql);
    if( !$result || mysqli_affected_rows($link) < 1 ){
        $mark = false;
    }

    $sql = "update user set money=money+20 where username = '乔峰2'";
    $result = mysqli_query($link,$sql);
    if( !$result || mysqli_affected_rows($link) < 1 ){
        $mark = false;
    }

    $sql = "update equipment set owner=2 where equipment = '汗血宝马' and owner = 1";
    $result = mysqli_query($link,$sql);
    if( !$result || mysqli_affected_rows($link) < 1 ){
        $mark = false;
    }

    if($mark){
        mysqli_query($link,'commit');
    }else{
        mysqli_query($link,'rollback');
    }
}

四、使用事务注意点

  1. MySQL中的事务不允许嵌套,在开启一个事务的时候,会默认把上一个未提交的事务提交
  2. 事务处理是针对的数据表中的数据操作,不包括库、表结构操作

五、保存点

在上面例子中,如果我就想撤销一部分,可以用保存点来实现。

SAVEPOINT jk;
ROLLBACK TO SAVEPOINT jk;//回滚到指定保存点
RELEASE SAVEPOINT jk;  //删除保存点

整个栗子:

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 300.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-180 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint jk01;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-20 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 100.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> rollback to savepoint jk01;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 120.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

六、事务的隔离级别

MySQL数据库是一个多用户的连接的系统,MySQL允许多线程并发访问,因此多个用户就会开启不同的事务。而事务之间的隔离也就很重要了

查看隔离级别

事务隔离级别的作用范围分为两种

  • 全局 针对所有的会话,即所有连接MySQL的用户
  • 会话 只对当前登录的用户
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@tx_isolation;
+-----------------------+
| @@tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

当前的隔离级别:REPEATABLE-READ 表示可重复读

MySQL的四种隔离级别

1.读取未提交(read uncommitted)

该级别是事务中最低级别,在该级别下的事务可以读到其他事务中未提交的数据。也叫脏读(Dirty Read).如上面"汗血宝马"例子,张无忌开启事务后,真实转账(update),但是不提交事务,便通知乔峰来查询,这个时候乔峰就会看到张无忌那个无提交事务的数据。 张无忌的窗口:

mysql> use demo;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.02 sec)

mysql>  update user set money=money-180 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  update user set money=money+180 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 120.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

乔峰的窗口


mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 120.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 120.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

2.读取提交(read committed)

在读取提交隔离级下只能读取其他事务已经提交的数据,避免了脏读数据的现象。但是在该隔离级别下,会出现不可重复读的问题 乔峰的窗口:

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 300.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 300.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

张无忌有修改金额的操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money+180 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

从上面的例子可以看出:乔峰在开启事务后,在一个时间段内,查看到两个不同的金额。为了避免这种情况,我们可以将乔峰的隔离级别设置成可重复读(repeatable read)

3.可重复读(repeatable read)

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 300.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 300.00 |
+----+----------+--------+
2 rows in set (0.02 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     | 185.00 |
|  2 | 张无忌   | 300.00 |
+----+----------+--------+
2 rows in set (0.00 sec)

张无忌端继续修改金额

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set money=money-180 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

可重复读是MySQL的默认级别。效果是对方提交之后的数据,我还是读取不到,比如上面的update操作,这种隔离级别可以避免“不可重复读取”,达到可重复读取;

幻读

虽然可以达到可重复读取,但是这种隔离又会导致出现“幻读”。

幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 的记录。举个例子通俗点说,我开始一个事务,准备插入一个名叫jack的用户信息,插入前我查询了数据表,发现不存在,然后准备插入。然后此时另一个终端在极短的时间里插入了一个“jack”的用户并提交了事务。此刻当我执行插入的时候,会报错提示jack用户已经存在,但是我查询(select)依然没有记录,所以此时会以为出现幻觉(遇见鬼了,哈哈)

A端窗口:

准备插入一个"聂哥"的记录,插入前进行查询,发现没有"聂哥"记录

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 300.00 |
|  3 | 谢逊     | 200.00 |
|  4 | 灭迹     | 300.00 |
+----+----------+--------+
4 rows in set (0.01 sec)
mysql > insert into user(username,money) values('聂哥','8888');  // 准备SQL 但是先不执行
B端窗口

此时B端开始事务优先执行了插入"聂哥"这条记录,并提交了事务

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> use demo;
Database changed
mysql> insert into user(username,money) values('聂哥','8888');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

mysql>
A端窗口

此时的A端执行插入语句,发现用户名重复报错.但是继续查询,记录里仍没有username为"聂哥"的记录

mysql> insert into user(username,money) values('聂哥','8888');
ERROR 1062 (23000): Duplicate entry '聂哥' for key 'NewIndex1'
mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 300.00 |
|  3 | 谢逊     | 200.00 |
|  4 | 灭迹     | 300.00 |
+----+----------+--------+
4 rows in set (0.00 sec)
解决幻读
  • 使用可串行化(serializable)隔离级别
  • 显式的加锁

4. 可串行化(serializable)

最高级别的隔离级,它在每个读的数据行上加锁,使之不会发生冲突,从而解决了脏读、不可重复读和幻读的问题。但是由于加锁可能导致超时和锁竞争想象。因此除非为了数据的稳定性,需要强制减少并发的情况,才会选择这个级别 乔峰端:


mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+----------+--------+
| id | username | money  |
+----+----------+--------+
|  1 | 乔峰     |   5.00 |
|  2 | 张无忌   | 300.00 |
|  3 | 谢逊     | 200.00 |
|  4 | 灭迹     | 300.00 |
+----+----------+--------+
4 rows in set (0.01 sec)

张无忌端一直等待,等待乔峰提交,知道超时后。

mysql> update user set money=money+1 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>