MySQL介于普通读和锁定读的加锁方式

在阅读本文前最好先看过三篇语句加锁分析文章：

事前准备

为了故事的顺利发展，我们先建一个表，并向表中插入一些记录，下边是SQL语句：

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

现在 hero 表中的记录情况就如下所示：

mysql> SELECT * FROM hero;
+--------+------------+---------+
| number | name       | country |
+--------+------------+---------+
|      1 | l刘备      | 蜀      |
|      3 | z诸葛亮    | 蜀      |
|      8 | c曹操      | 魏      |
|     15 | x荀彧      | 魏      |
|     20 | s孙权      | 吴      |
+--------+------------+---------+
5 rows in set (0.01 sec)

现象

在小册答疑群里有一位同学提了一个问题：说是在 READ COMMITTED 隔离级别下发生了一件百思不得其解的事儿。好的，首先构造环境，将当前会话默认的隔离级别设置成 READ COMMITTED ：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

事务 T1 先执行：

# T1中，隔离级别为READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
+--------+---------+---------+
| number | name    | country |
+--------+---------+---------+
|      8 | c曹操   | 魏      |
|     15 | x荀彧   | 魏      |
+--------+---------+---------+
2 rows in set (0.01 sec)

country 列并不是索引列，所以本条语句执行时肯定是使用扫描聚簇索引的全表扫描方式来执行， EXPLAIN 语句也证明了我们的想法：

mysql> EXPLAIN SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | hero  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

我们之前学过MySQL语句的加锁分析，知道在 READ COMMITTED 隔离级别下，如果采用全表扫描的方式执行查询语句时，InnoDB存储引擎将依次对每条记录加正经记录锁，在server层测试该记录是否符合WHERE条件，如果不符合则将加在该记录上的锁释放掉。本例中使用 FOR UPDATE 语句，肯定加的是X型正经记录锁。只有两条记录符合 WHERE 条件，所以最终其实只对这两条符合条件的记录加了 X型正经记录锁 （就是 number 列值为 8 和 15 的两条记录）。当然，我们可以使用 SHOW ENGINE INNODB STATUS 命令证明我们的分析：

mysql> SHOW ENGINE INNODB STATUS\G
... 省略了很多内容

------------
TRANSACTIONS
------------
Trx id counter 39764
Purge done for trx's n:o < 39763 undo n:o < 0 state: running but idle
History list length 36
Total number of lock structs in row lock hash table 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479653009568, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 281479653012832, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 39763, ACTIVE 468 sec
2 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 19, OS thread handle 123145470611456, query id 586 localhost 127.0.0.1 root
TABLE LOCK table `xiaohaizi`.`hero` trx id 39763 lock mode IX
RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39763 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000009b4a; asc      J;;
 2: len 7; hex 80000001d3012a; asc       *;;
 3: len 7; hex 63e69bb9e6938d; asc c      ;;
 4: len 3; hex e9ad8f; asc    ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 6; hex 000000009b4a; asc      J;;
 2: len 7; hex 80000001d30137; asc       7;;
 3: len 7; hex 78e88d80e5bda7; asc x      ;;
 4: len 3; hex e9ad8f; asc    ;;

 ... 省略了很多内容

其中 id 为 39763 的事务就是指 T1 ，可以看出它为 heap no 值为 4 和 5 的两条记录加了 X型正经记录锁 （lock_mode X locks rec but not gap）。

然后再开启一个隔离级别也为 READ COMMITTED 的事务 T2 ，在其中执行：

# T2中，隔离级别为READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM hero WHERE country = '吴' FOR UPDATE;
(进入阻塞状态)

很显然，这条语句也会采用全表扫描的方式来执行，会依次去获取每一条聚簇索引记录的锁。不过因为 number 值为 8 的记录已经被 T1 加了 X型正经记录锁 ， T2 想得却得不到，只能眼巴巴的进行阻塞状态，此时的 SHOW ENGINE INNODB STATUS 也能证明我们的猜想（只截取了一部分）：

---TRANSACTION 39764, ACTIVE 34 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 20, OS thread handle 123145471168512, query id 590 localhost 127.0.0.1 root Sending data
SELECT * FROM hero WHERE country = '吴' FOR UPDATE
------- TRX HAS BEEN WAITING 34 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39764 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000008; asc     ;;
 1: len 6; hex 000000009b4a; asc      J;;
 2: len 7; hex 80000001d3012a; asc       *;;
 3: len 7; hex 63e69bb9e6938d; asc c      ;;
 4: len 3; hex e9ad8f; asc    ;;

可以看到 T2 正在等待获取 heap no 为 4 的记录上的 X型正经记录锁 （lock_mode X locks rec but not gap waiting）。

以上是很正常的阻塞逻辑，我们都可以分析出来，不过如果在 T2 中执行下边的 UPDATE 语句：

# T2中，隔离级别为READ COMMITTED
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE hero SET name = 'xxx' WHERE country = '吴';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

WTF? 竟然没有阻塞，就这么随意地执行成功了？同样的 WHERE 条件，同样的执行计划，怎么 SELECT ... FOR UPDATE 和 UPDATE 语句的加锁情况不一样？

原因

哈哈，是的，的确不一样。其实MySQL支持3种类型的读语句：

普通读（也称一致性读，英文名：Consistent Read）。

这个就是指普通的SELECT语句，在末尾不加 FOR UPDATE 或者 LOCK IN SHARE MODE 的SELECT语句。普通读的执行方式是生成 ReadView 直接利用MVCC机制来进行读取，并不会对记录进行加锁。

小贴士：对于SERIALIZABLE隔离级别来说，如果autocommit系统变量被设置为OFF，那普通读的语句会转变为锁定读，和在普通的SELECT语句后边加LOCK IN SHARE MODE达成的效果一样。
锁定读（英文名：Locking Read）。

这个就是事务在读取记录之前，需要先获取该记录对应的锁。当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素，具体可参见：超全面MySQL语句加锁分析（上篇）（求转）
半一致性读（英文名：Semi-Consistent Read）。

这是一种夹在普通读和锁定读之间的一种读取方式。它只在 READ COMMITTED 隔离级别下（或者在开启了innodb_locks_unsafe_for_binlog系统变量的情况下）使用 UPDATE 语句时才会使用。具体的含义就是当 UPDATE 语句读取已经被其他事务加了锁的记录时， InnoDB 会将该记录的最新提交的版本读出来，然后判断该版本是否与 UPDATE 语句中的 WHERE 条件相匹配，如果不匹配则不对该记录加锁，从而跳到下一条记录；如果匹配则再次读取该记录并对其进行加锁。这样子处理只是为了让 UPDATE 语句尽量少被别的语句阻塞。

小贴士：半一致性读只适用于对聚簇索引记录加锁的情况，并不适用于对二级索引记录加锁的情况。

很显然，我们上边所唠叨的例子中是因为事务 T2 执行 UPDATE 语句时使用了半一致性读，判断 number 列值为 8 和 15 这两条记录的最新提交版本的 country 列值均不为 UPDATE 语句中 WHERE 条件中的 '吴' ，所以直接就跳过它们，不对它们加锁了。