MySQL：介于普通读和锁定读的加锁方式(1)，MySQL最全整理

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）。这个就是事务在读取记录之前，需要先获取该记录对应的锁。当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素。
• 半一致性读（英文名：Semi-Consistent Read）。

最后

针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。

最新整理面试题

上述的面试题答案都整理成文档笔记。
也还整理了一些面试资料&最新2021收集的一些大厂的面试真题，有需要的可以 点击这里，免费获取

最新整理电子书

最新整理大厂面试文档

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。