为了故事的顺利发展，我们先建一个表，并向表中插入一些记录，下边是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 STATUSG
... 省略了很多内容
------------
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)。
这个就是事务在读取记录之前，需要先获取该记录对应的锁。当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素，具体可参见：
半一致性读(英文名：Semi-Consistent Read)。
这是一种夹在普通读和锁定读之间的一种读取方式。它只在READ COMMITTED
隔离级别下(或者在开启了innodb_locks_unsafe_for_binlog系统变量的情况下)使用UPDATE
语句时才会使用。具体的含义就是当UPDATE
语句读取已经被其他事务加了锁的记录时，InnoDB
会将该记录的最新提交的版本读出来，然后判断该版本是否与UPDATE
语句中的WHERE
条件相匹配，如果不匹配则不对该记录加锁，从而跳到下一条记录；如果匹配则再次读取该记录并对其进行加锁。这样子处理只是为了让UPDATE
语句尽量少被别的语句阻塞。
小贴士：
半一致性读只适用于对聚簇索引记录加锁的情况，并不适用于对二级索引记录加锁的情况。
很显然，我们上边所唠叨的例子中是因为事务T2
执行UPDATE
语句时使用了半一致性读，判断number
列值为8
和15
这两条记录的最新提交版本的country
列值均不为UPDATE
语句中WHERE
条件中的'吴'
，所以直接就跳过它们，不对它们加锁了。
本知识点容易被忽略，各位同学在工作过程中分析的时候别忘记考虑一下Semi-Consistent Read
喔，码字不易，有帮助帮着转发喔，么么哒～
小册
想看更多MySQL进阶知识可以到小册中查看：《MySQL是怎样运行的：从根儿上理解MySQL》的链接
。小册的内容主要是从小白的角度出发，用比较通俗的语言讲解关于MySQL进阶的一些核心概念，比如记录、索引、页面、表空间、查询优化、事务和锁等，总共的字数大约是四十多万字，配有上百幅原创插图。主要是想降低普通程序员学习MySQL进阶的难度，让学习曲线更平滑一点～
614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
