mysql事务的四种隔离级别

这篇笔记主要对mysql事务进行一些学习。

业务里一般当进行转账等操作的时候，会用到mysql事务。

mysql事务有ACID特性：

Atomicity，原子性。即事务里的操作要么都成功，要么都失败
Consistency，一致性。数据库总是从一个一致性的状态转换到另外一个一致性的状态。
Isolation，隔离性。一个事务所做的修改在最终提交前，对其他事务是不可见的。
Durability，持久性。一旦事务提交，则其所做的修改就会永久保存到数据库中。

mysql事务有四种隔离级别:

serializable 可串行化。强制让事务串行执行，serializable会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁竞争的问题。
当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续访问。
- 这个隔离级别隔离度最高，但是性能最低。实际应用中很少使用到这个隔离级别，只有在非常需要确保数据一致性而且可以接受没有大并发的情况下，才考虑采用该级别。
repeatable read 可重复读。一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
- 这个隔离级别有幻读问题。幻读是指，当某个事务读取某范围的记录时，另一个事务又在该范围插入了新的记录，当前事务再次读取该范围的记录时，会产生幻行。
read committed 读已提交。一个事务提交之后，它做的变更才会被其他事务看到。
- 这个隔离级别多了个不可重复读问题。不可重复读是指，两次执行同样的查询，可能会得到不一样的结果。
read uncommitted 读未提交。一个事务还没提交时，它做的变更就能被别的事务看到。
- 这个隔离级别又多了脏读问题。也就是说，假如当前有两个事务A和B，A修改了某个数据，B读取到了这个修改后的数据，但是A因为某些原因回滚了，那么B读到的数据其实就是脏数据。

事务在mysql中默认采用的是自动提交模式（AUTOCOMMIT），也就是说，如果不是显式地开始一个事务，则每个查询都被当做一个事务执行提交操作。

可以使用show variables like '%autocommit%';来查看你自己的mysql关于自动提交的配置:

mysql autocommit

ON意味着autocommit=1，OFF意味着autocommit=0。

set autocommit=0这个命令会关掉自动提交。也就是是说，如果你只执行一个select语句，那么事务就启动了，并且不会自动提交，这个事务会持续存在知道你主动执行commit或rollback或断开连接。

所以在实际生产环境中，建议set autocommit=1

虽然mysql规范里面制定了四种事务的隔离级别，但是真正实现事务的是具体的存储引擎。例如Innodb就支持事务，而MyISAM是不支持事务的。具体可以使用show engines命令查看

查看支持的存储引擎

关于这四个隔离级别，我们可以实际操作体验一下。

我的环境是Mysql 5.6.34，使用的是InnoDB引擎

首先创建一张表，表结构如下：

create table testtransaction (
  `c1` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `c2` varchar(255),
  `c3` char(11),
  `c4` varchar(255),
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT;

读已提交

我们首先要设置隔离级别为读已提交: set session transaction isolation level READ COMMITTED;

在第一个事务里面我们执行如下操作：

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


mysql> insert into testtransaction values('a', 'b', 'c');
Query OK, 1 row affected (0.03 sec)


mysql> select * from testtransaction;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | a    | b    | c    |
+----+------+------+------+
1 row in set (0.00 sec)

然后在第二个事务里面我们执行如下操作：

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


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


mysql> select * from testtransaction;
Empty set (0.00 sec)

我们可以看到，在第二个事务里面select *的时候，并没有读到第一个事务未提交的数据。当第一个事务提交之后，我们在第二个事务里面再次执行select * from testtransaction的时候就不是空了，而是读到了第一个事务提交的数据。所以这就是不可重复读问题。

读未提交

我们首先要设置隔离级别为读未提交:set session transaction isolation level READ UNCOMMITTED;

在第一个事务里面，修改某个数据

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

mysql> update testtransaction set c2='hi' where c1=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testtransaction where c1=1;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | hi   | b    | c    |
+----+------+------+------+
1 row in set (0.00 sec)

在另外一个事务，执行以下操作：可以看到第一个事务还没提交的数据。如果直接拿查到的数据返回给用户或者进行后续的业务操作，但是第一个事务却回滚了，那么相当于拿到的就是脏数据。这就是脏读问题。

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

mysql> select * from testtransaction where c1=1;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  1 | hi   | b    | c    |
+----+------+------+------+
1 row in set (0.00 sec)

可串行化

我们首先要设置隔离级别为可串行化:set session transaction isolation level SERIALIZABLE;

在第一个事务里面查询c1=1的记录

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

mysql> select * from testtransaction where c1=1;
+----+-------+------+------+
| c1 | c2    | c3   | c4   |
+----+-------+------+------+
|  1 | hi    | b    | c    |
+----+-------+------+------+
1 row in set (0.00 sec)

在另外一个客户端里面对c1=1的记录进行修改。它会进入等待状态，如果超时会返回ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update testtransaction set c2='hello' where c1=1;

可重复读

我们首先要设置隔离级别为可重复读:set session transaction isolation level REPEATABLE READ;

在第一个事务里面，我们查询c1在(4,8)之间的记录

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

mysql> select * from testtransaction where c1 > 4 and c1 < 8;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  6 | a    | s    | d    |
|  7 | h    | j    | k    |
+----+------+------+------+
2 rows in set (0.00 sec)

在第二个事务里面，我们插入一条记录

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

mysql> insert into testtransaction values(5, '5', '5', '5');
Query OK, 1 row affected (0.00 sec)

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

然后再次在第一个事务里面查询c1在(4,8)之间的记录：

mysql> select * from testtransaction where c1 > 4 and c1 < 8;
+----+------+------+------+
| c1 | c2   | c3   | c4   |
+----+------+------+------+
|  6 | a    | s    | d    |
|  7 | h    | j    | k    |
+----+------+------+------+
2 rows in set (0.00 sec)

MySQL的默认的隔离级别是可重复读，但是按照文章开头的理论可重复读是会存在幻读问题的，照理说这里会查出来3条记录（c1=5,6,7），但我们实际只查出来2条记录，也就是说在第二个事务里面插入的数据并没有对第一个事务有影响。因为我们使用的是Innodb存储引擎，Innodb实现了间隙锁，可以防止幻读问题的发生。

在使用事务的时候，如果混用了存储引擎，那么要注意不同的存储引擎是否支持事务。就如同上面所说的，InnoDB是支持事务的，但是MyISAM是不支持事务的。如果是混用这两种存储引擎的表，并且因为某些原因事务回滚了，那么MyISAM里的表的变更就无法撤销了。

mysql事务的四种隔离级别

Nemo

读已提交

读未提交

可串行化

可重复读

Topics