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
条件中的 '吴'
,所以直接就跳过它们,不对它们加锁了。
本知识点容易被忽略,各位同学在工作过程中分析的时候别忘记考虑一下 Semi-Consistent Read
喔,码字不易,有帮助帮着转发喔,么么哒~
小青蛙历史文章(历史文章,不容错过):
MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!
长按关注小青蛙,都是干货喔
原文链接为《MySQL是怎样运行的: 从根儿上理解MySQL》小册链接