Previous article MySQL statement locking analysis

mysql learning column analyzes the locking of MySQL statements

Recommended ( Free): mysql learning (video)

Preparation

Create a store for the heroes of the Three Kingdomshero Table:

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

Then insert a few records into this table:

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

Then now the hero table has two indexes (one secondary index, one cluster Cluster index), the schematic diagram is as follows:

Statement lock analysis

In fact, "XXX statement "What lock should be added?" itself is a false proposition. The lock that a statement needs to add is subject to many conditions, for example:

• Isolation level of transaction

• The index used when the statement is executed (such as clustered index, unique secondary index, ordinary secondary index)

• Query conditions (for example, = , =, >=, etc.)

• Specific execution statement type

Before continuing to analyze the locking process of statements in detail, everyone must have a global concept: Locking only solves dirty writes, caused by concurrent transaction execution. Dirty read, Non-repeatable read, phantom read is a solution to these problems (MVCC is a solutionDirty read, Non-repeatable reading, phantom reading is a solution to these problems), we must realize that the starting point of locking is to solve these problems, different The problems to be solved in different scenarios are different, which leads to different locks. Never add locks just for the sake of locking, as it is easy to get yourself involved. Of course, sometimes due to the specific implementation of MySQL, locking in some scenarios is not easy to understand, so we have to memorize it by rote~

We divide the statements here into There are 3 major categories: ordinary SELECT statements, locked read statements, and INSERT statements. Let’s look at them separately.

Ordinary SELECT statement

Ordinary SELECT statement is in:

• READ UNCOMMITTEDUnder the isolation level, the latest version of the record is read directly without locking, and dirty reads, non-repeatable reads and may occur. Phantom reading problem.

• READ COMMITTEDUnder the isolation level, there is no locking, and a ReadView will be generated every time a normal SELECT statement is executed. , This solves the dirty read problem, but does not solve the non-repeatable read and phantom read problems.

• REPEATABLE READUnder the isolation level, no locking is performed, and a is generated only when the ordinary SELECT statement is executed for the first time. ReadView, this solves the problems of dirty reading, non-repeatable reading and phantom reading.

  But here is a small episode:

  # 事务T1，REPEATABLE READ隔离级别下
  mysql> BEGIN;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SELECT * FROM hero WHERE number = 30;
  Empty set (0.01 sec)
  # 此时事务T2执行了：INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交
  mysql> UPDATE hero SET country = '蜀' WHERE number = 30;
  Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0
  mysql> SELECT * FROM hero WHERE number = 30;
  | number | name    | country |
  |     30 | g关羽   | 蜀      |
  1 row in set (0.01 sec)

  Under the REPEATABLE READ isolation level, T1 executes a normal SELECT for the first time statement generated a ReadView, and then T2 inserted a new record into the hero table and submitted it, ReadView It does not prevent T1 from executing the UPDATE or DELETE statement to change the newly inserted record (because T2 has already submitted, change the record will not cause blocking), but in this way, the trx_id hidden column of this new record will become the transaction id of T1, and then When you use the ordinary SELECT statement in T1 to query this record, you can see this record and return this record to the client. Because of the existence of this special phenomenon, you can also think that MVCC in InnoDB cannot completely prohibit phantom reads.

• SERIALIZABLEUnder the isolation level, the discussion needs to be divided into two situations:

• In the system variableautocommit =0, that is, when automatic submission is disabled, the ordinary SELECT statement will be converted into a statement like SELECT ... LOCK IN SHARE MODE, that is, when reading Before fetching the record, you need to obtain the S lock of the record. The specific locking situation is the same as that under the REPEATABLE READ isolation level. We will analyze it later.

• When the system variable autocommit=1, that is, when automatic submission is enabled, the ordinary SELECT statement does not lock, but uses MVCC to generate a ReadView to read records.

  为啥不加锁呢？因为启用自动提交意味着一个事务中只包含一条语句，一条语句也就没有啥不可重复读、幻读这样的问题了。

锁定读的语句

我们把下边四种语句放到一起讨论：

• 语句一：SELECT ... LOCK IN SHARE MODE;

• 语句二：SELECT ... FOR UPDATE;

• 语句三：UPDATE ...

• 语句四：DELETE ...

我们说语句一和语句二是MySQL中规定的两种锁定读的语法格式，而语句三和语句四由于在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读。

READ UNCOMMITTED/READ COMMITTED隔离级别下

在READ UNCOMMITTED下语句的加锁方式和READ COMMITTED隔离级别下语句的加锁方式基本一致，所以就放到一块儿说了。值得注意的是，采用加锁方式解决并发事务带来的问题时，其实脏读和不可重复读在任何一个隔离级别下都不会发生（因为读-写操作需要排队进行）。

对于使用主键进行等值查询的情况

• 使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：

  SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

  这个语句执行时只需要访问一下聚簇索引中number值为8的记录，所以只需要给它加一个S型正经记录锁就好了，如图所示：

• 使用SELECT ... FOR UPDATE来为记录加锁，比方说：

  SELECT * FROM hero WHERE number = 8 FOR UPDATE;

  这个语句执行时只需要访问一下聚簇索引中number值为8的记录，所以只需要给它加一个X型正经记录锁就好了，如图所示：

• 小贴士： 为了区分S锁和X锁，我们之后在示意图中就把加了S锁的记录染成蓝色，把加了X锁的记录染成紫色。

• 使用UPDATE ...来为记录加锁，比方说：

  UPDATE hero SET country = '汉' WHERE number = 8;

  这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。

  如果UPDATE语句中更新了二级索引列，比方说：

  UPDATE hero SET name = 'cao曹操' WHERE number = 8;

  该语句的实际执行步骤是首先更新对应的number值为8的聚簇索引记录，再更新对应的二级索引记录，所以加锁的步骤就是：

1. 为number值为8的聚簇索引记录加上X型正经记录锁（该记录对应的）。

2. 为该聚簇索引记录对应的idx_name二级索引记录（也就是name值为'c曹操'，number值为8的那条二级索引记录）加上X型正经记录锁。

画个图就是这样：

小贴士： 我们用带圆圈的数字来表示为各条记录加锁的顺序。

• 使用DELETE ...来为记录加锁，比方说：

  DELETE FROM hero WHERE number = 8;

  我们平时所说的“DELETE表中的一条记录”其实意味着对聚簇索引和所有的二级索引中对应的记录做DELETE操作，本例子中就是要先把number值为8的聚簇索引记录执行DELETE操作，然后把对应的idx_name二级索引记录删除，所以加锁的步骤和上边更新带有二级索引列的UPDATE语句一致，就不画图了。

对于使用主键进行范围查询的情况

• 使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：

  SELECT * FROM hero WHERE number 
  这个语句看起来十分简单，但它的执行过程还是有一丢丢小复杂的：

1. 先到聚簇索引中定位到满足number 的第一条记录，也就是number值为1的记录，然后为其加锁。

2. 判断一下该记录是否符合索引条件下推中的条件。

   我们前边介绍过一个称之为索引条件下推（ Index Condition Pushdown，简称ICP）的功能，也就是把查询中与被使用索引有关的查询条件下推到存储引擎中判断，而不是返回到server层再判断。不过需要注意的是，索引条件下推只是为了减少回表次数，也就是减少读取完整的聚簇索引记录的次数，从而减少IO操作。而对于聚簇索引而言不需要回表，它本身就包含着全部的列，也起不到减少IO操作的作用，所以设计InnoDB的大叔们规定这个索引条件下推特性只适用于二级索引。也就是说在本例中与被使用索引有关的条件是：number ，而number列又是聚簇索引列，所以本例中并没有符合索引条件下推的查询条件，自然也就不需要判断该记录是否符合索引条件下推中的条件。

3. 判断一下该记录是否符合范围查询的边界条件

   因为在本例中是利用主键number进行范围查询，设计InnoDB的大叔规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件，也就是number 这个条件。如果符合的话将其返回给server层继续处理，否则的话需要释放掉在该记录上加的锁，并给server层返回一个查询完毕的信息。

   对于number值为1的记录是符合这个条件的，所以会将其返回到server层继续处理。

4. 将该记录返回到server层继续判断。

   server层如果收到存储引擎层提供的查询完毕的信息，就结束查询，否则继续判断那些没有进行索引条件下推的条件，在本例中就是继续判断number 这个条件是否成立。噫，不是在第3步中已经判断过了么，怎么在这又判断一回？是的，设计InnoDB的大叔采用的策略就是这么简单粗暴，把凡是没有经过索引条件下推的条件都需要放到server层再判断一遍。如果该记录符合剩余的条件（没有进行索引条件下推的条件），那么就把它发送给客户端，不然的话需要释放掉在该记录上加的锁。

5. 然后刚刚查询得到的这条记录（也就是number值为1的记录）组成的单向链表继续向后查找，得到了number值为3的记录，然后重复第2，3，4、5这几个步骤。

小贴士： 上述步骤是在MySQL 5.7.21这个版本中验证的，不保证其他版本有无出入。

但是这个过程有个问题，就是当找到number值为8的那条记录的时候，还得向后找一条记录（也就是number值为15的记录），在存储引擎读取这条记录的时候，也就是上述的第1步中，就得为这条记录加锁，然后在第3步时，判断该记录不符合number 这个条件，又要释放掉这条记录的锁，这个过程导致number值为15的记录先被加锁，然后把锁释放掉，过程就是这样：

这个过程有意思的一点就是，如果你先在事务T1中执行：

# 事务T1BEGIN;SELECT * FROM hero WHERE number 
然后再到事务T2中执行：
# 事务T2BEGIN;SELECT * FROM hero WHERE number = 15 FOR UPDATE;
是没有问题的，因为在T2执行时，事务T1已经释放掉了number值为15的记录的锁，但是如果你先执行T2，再执行T1，由于T2已经持有了number值为15的记录的锁，事务T1将因为获取不到这个锁而等待。
我们再看一个使用主键进行范围查询的例子：
SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;
这个语句的执行过程其实和我们举的上一个例子类似。也是先到聚簇索引中定位到满足number >= 8这个条件的第一条记录，也就是number值为8的记录，然后就可以沿着由记录组成的单向链表一路向后找，每找到一条记录，就会为其加上锁，然后判断该记录符不符合范围查询的边界条件，不过这里的边界条件比较特殊：number >= 8，只要记录不小于8就算符合边界条件，所以判断和没判断是一样一样的。最后把这条记录返回给server层，server层再判断number >= 8这个条件是否成立，如果成立的话就发送给客户端，否则的话就结束查询。不过InnoDB存储引擎找到索引中的最后一条记录，也就是Supremum伪记录之后，在存储引擎内部就可以立即判断这是一条伪记录，不必要返回给server层处理，也没必要给它也加上锁（也就是说在第1步中就压根儿没给这条记录加锁）。整个过程会给number值为8、15、20这三条记录加上S型正经记录锁，画个图表示一下就是这样：

• 使用SELECT ... FOR UPDATE语句来为记录加锁：

  和SELECT ... FOR UPDATE语句类似，只不过加的是X型正经记录锁。

• 使用UPDATE ...来为记录加锁，比方说：

  UPDATE hero SET country = '汉' WHERE number >= 8;

  这条UPDATE语句并没有更新二级索引列，加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。

  如果UPDATE语句中更新了二级索引列，比方说：

  UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

  这时候会首先更新聚簇索引记录，再更新对应的二级索引记录，所以加锁的步骤就是：

1. 为number值为8的聚簇索引记录加上X型正经记录锁。

2. 然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。

3. 为number值为15的聚簇索引记录加上X型正经记录锁。

4. 然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。

5. 为number值为20的聚簇索引记录加上X型正经记录锁。

6. 然后为上一步中的记录索引记录对应的idx_name二级索引记录加上X型正经记录锁。

画个图就是这样：

如果是下边这个语句：

UPDATE hero SET namey = '汉' WHERE number 
则会对number值为1、3、8聚簇索引记录以及它们对应的二级索引记录加X型正经记录锁，加锁顺序和上边语句中的加锁顺序类似，都是先对一条聚簇索引记录加锁后，再给对应的二级索引记录加锁。之后会继续对number值为15的聚簇索引记录加锁，但是随后InnoDB存储引擎判断它不符合边界条件，随即会释放掉该聚簇索引记录上的锁（注意这个过程中没有对number值为15的聚簇索引记录对应的二级索引记录加锁）。具体示意图就不画了。

• 使用DELETE ...来为记录加锁，比方说：

  DELETE FROM hero WHERE number >= 8;

  和

  DELETE FROM hero WHERE number 
  这两个语句的加锁情况和更新带有二级索引列的UPDATE语句一致，就不画图了。

对于使用二级索引进行等值查询的情况

小贴士： 在READ UNCOMMITTED和READ COMMITTED隔离级别下，使用普通的二级索引和唯一二级索引进行加锁的过程是一样的，所以我们也就不分开讨论了。

• 使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：

  SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

  这个语句的执行过程是先通过二级索引idx_name定位到满足name = 'c曹操'条件的二级索引记录，然后进行回表操作。所以先要对二级索引记录加S型正经记录锁，然后再给对应的聚簇索引记录加S型正经记录锁，示意图如下：

• 这里需要再次强调一下这个语句的加锁顺序：

1. 先对name列为'c曹操'二级索引记录进行加锁。

2. 再对相应的聚簇索引记录进行加锁

小贴士： 我们知道idx_name是一个普通的二级索引，到idx_name索引中定位到满足name= ‘c曹操’这个条件的第一条记录后，就可以沿着这条记录一路向后找。可是从我们上边的描述中可以看出来，并没有对下一条二级索引记录进行加锁，这是为什么呢？这是因为设计InnoDB的大叔对等值匹配的条件有特殊处理，他们规定在InnoDB存储引擎层查找到当前记录的下一条记录时，在对其加锁前就直接判断该记录是否满足等值匹配的条件，如果不满足直接返回（也就是不加锁了），否则的话需要将其加锁后再返回给server层。所以这里也就不需要对下一条二级索引记录进行加锁了。

现在要介绍一个非常有趣的事情，我们假设上边这个语句在事务T1中运行，然后事务T2中运行下边一个我们之前介绍过的语句：

UPDATE hero SET name = '曹操' WHERE number = 8;

这两个语句都是要对number值为8的聚簇索引记录和对应的二级索引记录加锁，但是不同点是加锁的顺序不一样。这个UPDATE语句是先对聚簇索引记录进行加锁，后对二级索引记录进行加锁，如果在不同事务中运行上述两个语句，可能发生一种贼奇妙的事情 ——

• 事务T2持有了聚簇索引记录的锁，事务T1持有了二级索引记录的锁。

• 事务T2在等待获取二级索引记录上的锁，事务T1在等待获取聚簇索引记录上的锁。

两个事务都分别持有一个锁，而且都在等待对方已经持有的那个锁，这种情况就是所谓的死锁，两个事务都无法运行下去，必须选择一个进行回滚，对性能影响比较大。

• 使用SELECT ... FOR UPDATE语句时，比如：

  SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

  这种情况下与SELECT ... LOCK IN SHARE MODE语句的加锁情况类似，都是给访问到的二级索引记录和对应的聚簇索引记录加锁，只不过加的是X型正经记录锁罢了。

• 使用UPDATE ...来为记录加锁，比方说：

  与更新二级索引记录的SELECT ... FOR UPDATE的加锁情况类似，不过如果被更新的列中还有别的二级索引列的话，对应的二级索引记录也会被加锁。

• 使用DELETE ...来为记录加锁，比方说：

  与SELECT ... FOR UPDATE的加锁情况类似，不过如果表中还有别的二级索引列的话，对应的二级索引记录也会被加锁。

对于使用二级索引进行范围查询的情况

• 使用SELECT ... LOCK IN SHARE MODE来为记录加锁，比方说：

  SELECT * FROM hero FORCE INDEX(idx_name)  WHERE name >= 'c曹操' LOCK IN SHARE MODE;

  小贴士： 因为优化器会计算使用二级索引进行查询的成本，在成本较大时可能选择以全表扫描的方式来执行查询，所以我们这里使用FORCE INDEX(idx_name)来强制使用二级索引idx_name来执行查询。

  这个语句的执行过程其实是先到二级索引中定位到满足name >= 'c曹操'的第一条记录，也就是name值为c曹操的记录，然后就可以沿着这条记录的链表一路向后找，从二级索引idx_name的示意图中可以看出，所有的用户记录都满足name >= 'c曹操'的这个条件，所以所有的二级索引记录都会被加S型正经记录锁，它们对应的聚簇索引记录也会被加S型正经记录锁。不过需要注意一下加锁顺序，对一条二级索引记录加锁完后，会接着对它相应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推～ 画个图表示一下就是这样：

• 再来看下边这个语句：

  SELECT * FROM hero FORCE INDEX(idx_name) WHERE name 
  这个语句的加锁情况就有点儿有趣了。前边说在使用number 这个条件的语句中，需要把number值为15的记录也加一个锁，之后又判断它不符合边界条件而把锁释放掉。而对于查询条件name 的语句来说，执行该语句需要使用到二级索引，而与二级索引相关的条件是可以使用索引条件下推这个特性的。设计InnoDB的大叔规定，如果一条记录不符合索引条件下推中的条件的话，直接跳到下一条记录（这个过程根本不将其返回到server层），如果这已经是最后一条记录，那么直接向server层报告查询完毕。但是这里头有个问题呀：先对一条记录加了锁，然后再判断该记录是不是符合索引条件下推的条件，如果不符合直接跳到下一条记录或者直接向server层报告查询完毕，这个过程中并没有把那条被加锁的记录上的锁释放掉呀！！！。本例中使用的查询条件是name ，在为name值为'c曹操'的二级索引记录以及它对应的聚簇索引加锁之后，会接着二级索引中的下一条记录，也就是name值为'l刘备'的那条二级索引记录，由于该记录不符合索引条件下推的条件，而且是范围查询的最后一条记录，会直接向server层报告查询完毕，重点是这个过程中并不会释放name值为'l刘备'的二级索引记录上的锁，也就导致了语句执行完毕时的加锁情况如下所示：

• 这样子会造成一个尴尬情况，假如T1执行了上述语句并且尚未提交，T2再执行这个语句：

  SELECT * FROM hero WHERE name = 'l刘备' FOR UPDATE;

  T2中的语句需要获取name值为l刘备的二级索引记录上的X型正经记录锁，而T1中仍然持有name值为l刘备的二级索引记录上的S型正经记录锁，这就造成了T2获取不到锁而进入等待状态。

  小贴士： 为啥不能释放不符合索引条件下推中的条件的二级索引记录上的锁呢？这个问题我也没想明白，人家就是这么规定的，如果有明白的小伙伴可以加我微信 xiaohaizi4919 来讨论一下哈～ 再强调一下，我使用的MySQL版本是5.7.21，不保证其他版本中的加锁情景是否完全一致。

• 使用SELECT ... FOR UPDATE语句时：

  和SELECT ... FOR UPDATE语句类似，只不过加的是X型正经记录锁。

• 使用UPDATE ...来为记录加锁，比方说：

  UPDATE hero SET country = '汉' WHERE name >= 'c曹操';

  小贴士： FORCE INDEX只对SELECT语句起作用，UPDATE语句虽然支持该语法，但实质上不起作用，DELETE语句压根儿不支持该语法。

  假设该语句执行时使用了idx_name二级索引来进行锁定读，那么它的加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为对应的二级索引记录进行加锁，就不赘述了。不过还有一个有趣的情况，比方说：

  UPDATE hero SET country = '汉' WHERE name 
  我们前边说的索引条件下推这个特性只适用于SELECT语句，也就是说UPDATE语句中无法使用，那么这个语句就会为name值为'c曹操'和'l刘备'的二级索引记录以及它们对应的聚簇索引进行加锁，之后在判断边界条件时发现name值为'l刘备'的二级索引记录不符合name 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示：

• 使用DELETE ...来为记录加锁，比方说：

  DELETE FROM hero WHERE name >= 'c曹操';

  和

  DELETE FROM hero WHERE name 
  如果这两个语句采用二级索引来进行锁定读，那么它们的加锁情况和更新带有二级索引列的UPDATE语句一致，就不画图了。

全表扫描的情况

比方说：

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型正常记录锁，然后返回给server层，如果server层判断country = '魏'这个条件是否成立，如果成立则将其发送给客户端，否则会释放掉该记录上的锁，画个图就像这样：

使用SELECT ... FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型正经记录锁，就不赘述了。

对于UPDATE ...和DELETE ...的语句来说，在遍历聚簇索引中的记录，都会为该聚簇索引记录加上X型正经记录锁，然后：

• 如果该聚簇索引记录不满足条件，直接把该记录上的锁释放掉。

• 如果该聚簇索引记录满足条件，则会对相应的二级索引记录加上X型正经记录锁（DELETE语句会对所有二级索引列加锁，UPDATE语句只会为更新的二级索引列对应的二级索引记录加锁）。                                                        

The above is the detailed content of Previous article MySQL statement locking analysis. For more information, please follow other related articles on the PHP Chinese website!