MySQL的事务和锁机制

首先，我们从事务的四大特性(ACID)出发：

• 原子性：事务是最小的执行单位，不允许分割
• 一致性：执行事务前后，数据库状态一致
• 隔离性：事务之间是相互独立的，一个事务不能被其它事务干扰
• 持久性：事务提交之后对数据库的修改是永久的，即使发生崩溃也能恢复

事务具有这样的特性，但是当多个事务并发执行时，结果可能与我们预期的不同，可能会出现以下几类问题：

• 丢失修改：事务A的操作被事务B覆盖
• 脏读：事务A读取了事务B还未提交的数据，可能根据这个数据做出错误的操作
• 不可重复读：一个事务内先后两次查询到的数据不一致
• 幻读：一个事务内先后同样的查询得到的数据多了或者少了

我们都知道MySQL提供了四种隔离级别，不同的隔离级别可以解决相应的问题。

• 读未提交：允许读取还没有提交的数据，可能导致脏读、幻读和不可重复读
• 读已提交：允许读取已经提交的数据，解决脏读，可能导致幻读和不可重复读
• 可重复读：安全，解决脏读、不可重复读、幻读
• 串行化：事务串行执行，安全

那么，MySQL究竟是如何实现这些隔离级别效果(即解决并发事务可能出现的各种效果)的呢？我们从以下几方面来分析，这些机制并不是相互独立的。

1、锁

MySQL提供了两种锁：

• 共享锁(读锁)：允许事务读取一行数据，兼容读锁但不兼容写锁
• 排他锁(写锁)：允许事务删除或更新一行数据，不兼容读锁和写锁，其它锁均需要等待释放后再获取

通过加锁可以解决一些并发问题，MySQL中根据操作是否加锁有快照读和当前读两个概念：

• 快照读：读取记录数据的可见版本，不加锁，普通的SELECT语句都是快照读
• 当前读：读取记录数据的最新版本，显式加锁(FOR UPDATE、LOCK IN SHARE MODE)的是当前读，此外，更新操作(INSERT、UPDATE、DELETE)也是当前读，会加锁

所以，当一个事务在更新一行数据时，其它事务就不能对这行数据进行操作，只有等当前事务提交并释放锁之后其它事务才能读取或修改，也就避免了数据库层面的丢失修改问题。基于此机制，即使是读未提交级别也能避免丢失修改问题。通过显式加锁(FOR UPDATE、LOCK IN SHARE MODE)也可以避免脏读问题。

2、MVCC

MVCC即多版本并发控制，每个数据都有多个版本，MySQL通过MVCC来实现读已提交和可重复读隔离级别，可以避免脏读和不可重复读问题。

首先说说MVCC的流程：当一个事务读一个数据时，会比较数据的版本，判断该数据是否对自己可见，如果不可见则通过read view和undo log回滚版本，直到找到对自己可见的数据版本。

要理解MVCC的具体实现，需要理解涉及的一些底层原理：

• 事务版本号：每个事务都有自己的ID，用于判断先后顺序

• undo log：回滚日志，一种逻辑日志

• 隐式字段：每个数据记录都有隐式字段

  • trx_id：记录操作这个数据的事务ID
  • roll_pointer：指向回滚段的undo日志
  • row_id(没有主键和非NULL唯一键时才有，是单调递增的行ID)

• read_view：一致性视图，基于整库的快照。

  • m_ids：系统中活跃的事务(即未提交的事务)ID，是一个list
  • min_limit_id： m_ids中最小的ID
  • max_limit_id： m_ids中最大ID+1
  • create_trx_id：创建当前read view的事务ID，即当前事务的ID

如何判断数据是否可见？

通过比较trx_id与min_limit_id、max_limit_id来判断，具体：

①trx_id < min_limit_id：说明操作这个版本的事务已经提交，可见

②trx_id >= max_limit_id：说明操作这个版本的事务在生成read view之后才开启，不可见

③min_limit_id < trx_id <max_limit_id ：

​ 若trx_id不在m_ids中，说明操作这个版本的事务已经提交，可见；

​ 若trx_id在m_ids且不等于create_trx_id：说明操作这个版本的事务还未提交，且不是当前事务，不可见

你可能要问了，为什么MVCC不仅能实现可重复读，还能实现已提交读呢？两种隔离级别下MVCC的实现有什么区别？

这是因为MVCC在RR(可重复读)和RC(读已提交)下生成read view的时机不同：

• RR：事务开始时创建read view，之后事务里的查询都用这个一致性视图
• RC：每一个语句执行前都创建一个read view

注意一点，可重复读隔离级别下，普通的SELECT操作是快照读，但如果要更新数据，那就不能根据数据的历史版本来了，会造成丢失修改，所以不可重复读中两次读到的数据不一样指的是在自己没有操作的情况下，因为别的事务修改导致的不一样，如果自己在事务中进行了UPDATE，是当前读，根据最新的版本进行，之后再读到的就是自己修改后的新数据了。

3、Next-Key Lock

InnoDB中提供了三种锁算法：

• Record Lock ：锁单个记录
• Gap Lock(间隙锁)：锁一个范围，但不包括记录本身
• Next-Key Lock： Record Lock+Gap Lock

Next-Key Lock可以解决幻读问题。

通过一个例子来说明“什么是幻读”这个问题：

首先创建一个表并插入数据，结果如下：

CREATE TABLE `test`(
		id INT,
		a INT,
		PRIMARY KEY(id),
		KEY a(a)
);

INSERT INTO `test` VALUES(1,3),(2,5),(3,7);

SELECT * FROM test;

+----+---+
| id | a |
+----+---+
|  1 | 3 |
|  2 | 5 |
|  3 | 7 |
+----+---+

开启两个会话，并将隔离级别设置为读已提交，该隔离级别下没有间隙锁

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

首先从会话1中开启事务，查询a=3的记录

BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
+----+---+
1 row in set (0.02 sec)

这时从会话B中插入一条数据：

mysql> INSERT INTO test VALUES(4,3);
Query OK, 1 row affected (0.01 sec)

这时会话1再执行相同的查询语句：

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
|  4 | 3 |
+----+---+
2 rows in set (0.02 sec)

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

从会话1的角度来看整个过程就是这样的：一个事务内没有进行插入，先后两次执行同样的查询，但却多出来一条数据，就像发生了幻觉！这便是幻读。

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

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
+----+---+
1 row in set (0.02 sec)

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
|  4 | 3 |
+----+---+
2 rows in set (0.02 sec)

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

行锁锁住的只是a=3记录行，而新插入记录是在记录之间的“间隙”

接下来将隔离级别设置为可重复读，并将表复原，再执行上述过程：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这时你会发现会话B的插入语句执行后就没有反应了(被阻塞了)，会话A中两次查询结果也是一致的

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

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
+----+---+
1 row in set (0.02 sec)

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
+----+---+
1 row in set (0.03 sec)

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

直到会话A中的事务提交之后，会话B的语句才能执行,耗时38.05 sec就是由于阻塞。

mysql> INSERT INTO test VALUES(4,3);
Query OK, 1 row affected (38.05 sec)

这时会话A再查询，才能看到会话B插入的数据

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;
+----+---+
| id | a |
+----+---+
|  1 | 3 |
|  4 | 3 |
+----+---+
2 rows in set (0.03 sec)

下面我们讨论具体实现。

以上表的数据为例：

+----+---+
| id | a |
+----+---+
|  1 | 3 |
|  2 | 5 |
|  3 | 7 |
+----+---+

当执行这条更新语句时，Next-Key Lock会给记录a=3这一行加锁，还会给间隙(-∞,3)U(3,5)加锁

mysql> SELECT * FROM test WHERE a = 3 FOR UPDATE;

进行测试：

INSERT INTO test VALUES(4,2);		//阻塞
INSERT INTO test VALUES(5,3);		//阻塞
INSERT INTO test VALUES(6,4);		//阻塞
INSERT INTO test VALUES(7,5);		//正常执行
INSERT INTO test VALUES(8,-1);		//阻塞

所以，Next-Key Lock就是幻读的解决办法。