在共享锁模式和更新的情况下，间隙锁是如何工作的？

Question

mysql version: 5.7.31
isolation level: RR

建表语句如下所示：

CREATE TABLE `lockt` (
  `id` int(11) NOT NULL,
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `col1_ind` (`col1`),
  KEY `col2_ind` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里有一些要测试的数据：

INSERT INTO `lockt` (`id`, `col1`, `col2`)
VALUES
    (1,1,1),
    (2,2,3),
    (5,5,5),
    (6,6,9),
    (10,10,25),
    (123,123,8),
    (1007,10077,144),
    (1008,1008,220),
    (1019,1019,200),
    (1020,1020,201),
    (1111,1111,32),
    (1234,1234,33);

在学习mysql中的间隙锁的过程中，我遇到了一个让我很难理解的案例：

在事务1中：

set autocommit=0;
begin;
select * from lockt;
select * from lockt where col2=25 lock in share mode;

set autocommit=0;
begin;
select * from lockt;
update lockt set col2=66666 where col2 > 33;

但是我弄清楚update语句是我的意见，SQL "select * from lock in share mode“将申请一个共享锁，并在范围(9，25]，(25,32]上添加间隙锁，但是范围(33，+∞]超出了这些范围，为什么第二个事务仍然被阻塞，这超出了我的预期。我搞不懂它为什么会这样。在间隙锁上有什么我误解的地方吗？任何能帮助我摆脱这个问题的人都将不胜感激。

Answer 1

添加一些关于锁的背景知识(并使这个答案比预期的要长一点)，重要的是要理解，一般来说，MySQL只会考虑在执行查询时锁定它所查看的行/间隙。

所有其他行/间隙都是不相关的:如果第二个查询既不查看相同的行/间隙，也不会更改这些行/间隙(例如，通过添加一行)，则它不能更改第一个查询的结果，也不能更改第二个查询的结果，无论第一个查询做什么。

因此，要调查锁，了解MySQL查看哪些行绝对至关重要。(如果锁实际上是冲突的，将取决于查询和隔离级别，这是您试图尝试的，但实际上不是这个答案的一部分，因为这不是造成混淆的原因。)

那么MySQL如何找到您的行呢？显然，MySQL可以在col2上使用索引。如果MySQL使用索引查找行，它会同时锁定索引和行本身(从技术上讲，这意味着它会锁定主键中的一行)。

这就是您所期望的: MySQL查找带有col2 = 25的行，锁定它(在索引和主键中)，然后应该查找带有col2 > 33的行，并且查找那些使用索引的行应该不会发生冲突。

这是正确的。如果MySQL使用索引，则没有重叠。令人困惑的是，MySQL有一种不同的执行查询的方式:它可以遍历整个表并更新符合条件的行。

这实际上可能更快(这是优化器所关心的全部)，因为使用索引查找行比遍历整个表要慢。这只是一个数字游戏:在某些时候，只读取所有行(每行速度更快)要比使用索引只查找正确的行(每行速度更低)更快。

显然，对于col2 > 33，MySQL决定这么做。由于它这样做了，它所查看的行现在都是行。这将与col2 = 25上的锁冲突(已使用col2`上的索引锁定在主键中)。这并不是因为gaplock (您正在尝试调查它)，而是一个简单的、老式的锁定行。

您可以使用更大的值重试查询，然后MySQL可能会决定使用索引。您可以通过运行explain update lockt ...来检查MySQL使用了哪个索引，根据您的评论，临界值(对于您的特定数据)似乎是col2 > 144。为此，执行计划应该显示它使用col2上的索引(列key中的值)，而对于col2 > 143，它应该使用主键。

实际上，可以通过索引提示强制MySQL使用您希望它使用的索引(按照您期望的那样锁定)，如下所示

update lockt force index (col2_ind) set col2=66666 where col2 > 33;

再次强调: MySQL不必锁定它所查看的所有行和/或间隙，如果未使用(例如，如果它们没有更新)，它可以释放锁，并且并不是所有的锁都与所有的锁冲突。关于这一点的详细信息将取决于查询和隔离级别，并将允许MySQL进行广泛的实际锁定行为。

因此，简而言之，回答您的问题:基于索引的MySQL锁，因此，如果您尝试使用锁，请确保检查索引。

Answer 2

同意@Solarflare

在会话1中:使用共享锁锁定col2数据行。在会话2中:在条件col2>33和查询锁定的情况下执行udpate语句。然后我们可以使用"show engine innodb status“来显示锁的详细信息。如下所示：

TRANSACTIONS
------------
Trx id counter 8182
Purge done for trx's n:o < 8177 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281479682839888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479682837176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281479682838984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 8181, ACTIVE 45 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 25, OS thread handle 123145535516672, query id 3434 localhost root updating
update lockt set col2=111 where col2 >33
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 3 n bits 96 index PRIMARY of table
 `demo`.`lockt` trx id 8181 **lock_mode X waiting**
Record lock, heap no 17 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 000000001fed; asc       ;;
 2: len 7; hex b3000001270110; asc     '  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 80000019; asc     ;;

这是会话2中的update语句导致的X锁，而不是会话1中的查询语句。

我们还可以使用大于更大的值( col2 > 200)的col2来更新数据，或者使用较小的范围(col2> 32和col2< 36)来更新数据，以缩小x锁的范围。

    mysql> update lockt set col2=36 where col2 > 33 and col2 < 36;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0
    
    mysql> update lockt set col2=35 where col2 > 32 and col2 < 36;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> update lockt set col2=35 where col2 > 200;
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> update lockt set col2=35 where col2 > 33;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction