InnoDB数据锁–第2.5部分“锁”（深入研究）

MySQLSE

发布于 2020-12-08 17:48:31

1.2K0

发布于 2020-12-08 17:48:31

作者：Kuba Łopuszański 译：徐轶韬

现在，我们将InnoDB数据锁-第2部分“锁”中了解到的所有知识放在一起，进行深入研究:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t FOR SHARE;
+----+
| id |
+----+
|  5 |
| 10 |
| 42 |
+----+
3 rows in set (0.00 sec)

mysql> DELETE FROM t WHERE id=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE
       FROM performance_schema.data_locks WHERE OBJECT_NAME='t';
+------------+-----------+------------------------+---------------+
| INDEX_NAME | LOCK_TYPE | LOCK_DATA              | LOCK_MODE     |
+------------+-----------+------------------------+---------------+
| NULL       | TABLE     | NULL                   | IS            |
| PRIMARY    | RECORD    | supremum pseudo-record | S             |
| PRIMARY    | RECORD    | 5                      | S             |
| PRIMARY    | RECORD    | 10                     | S             |
| PRIMARY    | RECORD    | 42                     | S             |
| NULL       | TABLE     | NULL                   | IX            |
| PRIMARY    | RECORD    | 10                     | X,REC_NOT_GAP |
| PRIMARY    | RECORD    | 4                      | S,GAP         |
+------------+-----------+------------------------+---------------+
8 rows in set (0.00 sec)

我们看到：

第一个SELECT * FROM t FOR SHARE;在5、10、42和supremum pseudo-record上创建S锁(在间隙和记录上)。这意味着整个轴都被锁覆盖。而这正是所需的，可以防止任何其他事务修改此查询的结果集。同样，这需要先对表t加IS锁。
接下来，DELETE FROM t WHERE id=10;首先获得的IX表锁以证明它打算修改表，然后获得的X，REC_NOT_GAP修改ID=10的记录
最后，INSERT INTO t VALUES (4);看到它已经具有IX，因此继续执行插入操作。这是非常棘手的操作，需要谈谈我们已抽象的细节。首先从临时闩锁（注意单词：“ latching”，而不是“ locking”！）开始，查看页面是否是放置记录的正确位置，然后在插入点右侧闩住锁系统队列并检查是否有*，GAP，S或X锁。我们的例子中没有记录，因此我们立即着手插入记录（它有一个隐式锁，因为它在“last modified by”字段中有我们的事务的id，希望这解释了为什么在记录4上没有显式的X,REC_NOT_GAP锁）。相反的情况是存在一些冲突的锁，为了显式地跟踪冲突，将创建一个等待的INSERT_INTENTION锁，以便在授予操作后可以重试。最后一步是在轴上插入新点会将已经存在的间隙分成两部分。对于旧间隙，已经存在的任何锁都必须继承到插入点左侧新创建的间隙。这就是我们在第4行看到S，GAP的原因：它是从第5行的S锁继承的。

这只是涉及到的真正复杂问题的冰山一角（我们还没有讨论从已删除的行继承锁，二级索引，唯一性检查..），但是从中可以得到一些更深层次的想法：

通常，要提供可串行性，您需要“锁定所见内容”，这不仅包括点，而且还包括点之间的间隙。如果您可以想象查询在扫描时如何访问表，那么您大都可以猜测它将必须锁定什么。这意味着拥有良好的索引很重要，这样您就可以直接跳到要锁定的点，而不必锁定整个扫描范围。
反之亦然：如果您不关心可串行性，您可以尝试不锁定某些东西。例如，在READ COMMITTED隔离级别较低的情况下，我们尝试避免锁定行之间的间隙（因此，其他事务可以在行之间插入行，这会导致所谓的“幻读”）
在InnoDB中，所有那些“正在插入”和“正在删除”的行，实际上都存在于索引中，因此出现在轴上并将其分成多个间隙。这与某些其他引擎形成对比，其他引擎将正在进行的更改保留在“暂存区”中，并且仅在提交时将其合并。这意味着即使在概念上并发事务之间没有交互（例如，在提交事务之前，我们不应该看到行被事务插入），但在低级别实现中，它们之间的交互仍然很多（例如，事务可以在尚未正式存在的行上有一个等待锁）。因此，看到Performance_schema.data_locks报告尚未插入或已被删除的行，不需要感到惊讶（后者将最终被清除）

记录锁的压缩（以及丢失的LOCK_DATA）

在上面的示例中，您看到了一个非常有用的LOCK_DATA列，该列为您显示了放置记录锁的索引列的行值。这对于分析情况非常有用，但是将“ LOCK_DATA”显式存储在内存对象中会很浪费，所以当你查询performance_schema时，这些数据实际上是实时重建的。data_locks表来自锁系统内存中可用的压缩信息，它与缓冲池页面中的可用数据结合在一起。也就是说，锁系统根据记录<space_id, page_no>所在的页面和页面中的记录heap_no编号来标识记录锁。（这些数字通常不必与页面上记录值的顺序相同，因为它们是由小型堆分配器分配的，在删除、插入和调整行大小时，尽量重用页面内的空间）。这种方法具有一个很好的优点，即可以使用三个固定长度的数字来描述一个点：space_id, page_no, heap_no。此外，一个查询必须在同一页上锁定几行是一个常见的情况，所有锁(仅heap_no不同）都一起存储在一个有足够长的位图的单一对象，这样heap_no第一位可以表示给定记录是否应被此锁实例覆盖。（这里需要权衡取舍，因为即使我们只需要锁定一条记录，我们也会“浪费”整个位图的空间。值得庆幸的是，每页记录的数量通常足够小，您可以负担n / 8个字节）

因此，即使Performance_schema.data_locks分别报告每个记录锁，它们通常也仅对应于同一对象中的不同位，并且通过查看OBJECT_INSTANCE_BEGIN列可以看到：

> CREATE TABLE t(id INT PRIMARY KEY);
> insert into t values (1),(2),(3),(4);
> delete * from t where id=3;
> insert into t values (5);
> BEGIN;
> SELECT * FROM t FOR SHARE;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
|  5 |
+----+
> SELECT OBJECT_INSTANCE_BEGIN,INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE 
  FROM performance_schema.data_locks WHERE OBJECT_NAME='t';
+-----------------------+------------+-----------+------------------------+-----------+
| OBJECT_INSTANCE_BEGIN | INDEX_NAME | LOCK_TYPE | LOCK_DATA              | LOCK_MODE |
+-----------------------+------------+-----------+------------------------+-----------+
|         3011491641928 | NULL       | TABLE     | NULL                   | IS        |
|         3011491639016 | PRIMARY    | RECORD    | supremum pseudo-record | S         |
|         3011491639016 | PRIMARY    | RECORD    | 1                      | S         |
|         3011491639016 | PRIMARY    | RECORD    | 2                      | S         |
|         3011491639016 | PRIMARY    | RECORD    | 5                      | S         |
|         3011491639016 | PRIMARY    | RECORD    | 4                      | S         |
+-----------------------+------------+-----------+------------------------+-----------+

请注意，SELECT..FROM t..返回的行以其语义顺序（以id递增）表示，这意味着扫描主索引的最简单方法实际上是以主键的顺序访问行，因为它们在页面堆中形成了一个链表。但是，SELECT..from performance_schema.data_locks揭示了内部实现的一些提示：id = 5的新插入行进入了id = 3的已删除行留下的空缺。我们看到所有记录锁都存储在同一个对象实例中，并且我们可以猜测，这个实例的位图为heap_no设置了与所有实际行和最高伪记录对应的位。

现在，让我们证明锁系统并不真正知道列的值，因此我们必须查看缓冲池中实际页的内容以填充LOCK_DATA列。可以将缓冲池视为磁盘上实际页面的缓存（抱歉，过于简化：实际上，它可能比磁盘页面上的数据更新，因为它还包含存储在重做日志增量中的页补丁）。Performance_schema仅使用来自缓冲池的数据，而不使用来自磁盘的数据，如果它无法在其中找到页面，不会尝试从磁盘获取数据，而是在LOCK_DATA列中报告NULL。我们如何强制从缓冲池中逐出页？总的来说：我不知道。似乎可行的方法是将更多的新页推入缓冲池以达到其容量，并且逐出最早的页。为此，我将打开一个新客户端并创建一个表，使其太大而无法容纳在缓冲池中。有多大？

con2> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

好的，我们需要推送128MB的数据。（可以通过将缓冲池的大小调整为较小的值来简化此实验，通常可以动态地进行此操作，不幸的是，“块”的默认大小很大，以至于无论如何我们都无法将其减小到128MB以下）

con2> CREATE TABLE big(
        id INT PRIMARY KEY AUTO_INCREMENT,
        blah_blah CHAR(200) NOT NULL
      );
con2> INSERT INTO big VALUES (1,REPEAT('a',200));
con2> INSERT INTO big (blah_blah) SELECT blah_blah FROM big;
con2> INSERT INTO big (blah_blah) SELECT blah_blah FROM big;
con2> INSERT INTO big (blah_blah) SELECT blah_blah FROM big;
...
con2> INSERT INTO big (blah_blah) SELECT blah_blah FROM big;
Query OK, 262144 rows affected (49.14 sec)
Records: 262144  Duplicates: 0  Warnings: 0

..就足够了。让我们再次查看performance_schema.data_locks：

> SELECT OBJECT_INSTANCE_BEGIN,INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE
  FROM performance_schema.data_locks WHERE OBJECT_NAME='t';
+-----------------------+------------+-----------+------------------------+-----------+
| OBJECT_INSTANCE_BEGIN | INDEX_NAME | LOCK_TYPE | LOCK_DATA              | LOCK_MODE |
+-----------------------+------------+-----------+------------------------+-----------+
|         3011491641928 | NULL       | TABLE     | NULL                   | IS        |
|         3011491639016 | PRIMARY    | RECORD    | supremum pseudo-record | S         |
|         3011491639016 | PRIMARY    | RECORD    | NULL                   | S         |
|         3011491639016 | PRIMARY    | RECORD    | NULL                   | S         |
|         3011491639016 | PRIMARY    | RECORD    | NULL                   | S         |
|         3011491639016 | PRIMARY    | RECORD    | NULL                   | S         |
+-----------------------+------------+-----------+------------------------+-----------+

哈！你看，在LOCK_DATA列中有NULL。但是请不要担心，这只是将信息呈现给人类的方式-Lock System仍然知道哪个页面的heap_no被锁定，如果您尝试从另一个客户端访问这些记录，则必须等待：

con2> DELETE FROM t WHERE id = 2;
⌛

如果在LOCK_DATA中看到NULL，请不要惊慌。这仅表示该页面当前在缓冲池中不可用。

正如你所期望的，运行DELETE会将页面带到内存，你现在可以看到数据没有问题:

> SELECT ENGINE_TRANSACTION_ID,INDEX_NAME,LOCK_DATA,LOCK_MODE,LOCK_STATUS 
  FROM performance_schema.data_locks 
  WHERE OBJECT_NAME='t' AND LOCK_TYPE='RECORD';
+-----------------------+------------+------------------------+---------------+-------------+
| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_DATA              | LOCK_MODE     | LOCK_STATUS |
+-----------------------+------------+------------------------+---------------+-------------+
|                  2775 | PRIMARY    | 2                      | X,REC_NOT_GAP | WAITING     |
|       284486501679344 | PRIMARY    | supremum pseudo-record | S             | GRANTED     |
|       284486501679344 | PRIMARY    | 1                      | S             | GRANTED     |
|       284486501679344 | PRIMARY    | 2                      | S             | GRANTED     |
|       284486501679344 | PRIMARY    | 5                      | S             | GRANTED     |
|       284486501679344 | PRIMARY    | 4                      | S             | GRANTED     |
+-----------------------+------------+------------------------+---------------+-------------+

锁拆分

如前所述，“轴”与“点”和“点之间的间隙”(理论上)可以在锁系统中以两种不同的方式建模:

选项A：两种不同的资源。间隙（15,33）是一种资源，而点[33]是另一种资源。可以使用一组简单的访问模式（例如，仅X和S）独立地请求和授予每种权限
选项B：一个单一的资源，用于记录和前面的间隙的组合，以及一组更宽的访问模式，用于对间隙和记录做的事情进行编码（X， X，REC_NOT_GAP，X，GAP，S，S，REC_NOT_GAP，S，GAP，...）

InnoDB（目前）使用选项B。我看到的主要好处是在常见的情况下（当事务需要在扫描期间锁定间隙和记录时），它只需要一个内存中的对象即可，而不是两个，这不仅节省了空间，而且需要更少的内存查找以及对列表中的单个对象使用快速路径。

但是，这种设计决策并非一成不变，因为从概念上讲，它认为X = X，GAP + X，REC_NOT_GAP，S = S，GAP + S，REC_NOT_GAP 并且InnoDB 8.0.18可以通过下面描述的所谓的“锁拆分”技术来利用这些方程式。

事务必须等待甚至死锁的常见原因是因为它已经有记录但没有间隙（例如，它具有X，REC_NOT_GAP）并且必须“升级”以弥补在记录之前的间隙（例如，它请求X），可惜它不得不等待另一个事务（例如，另一个事务正在等待S，REC_NOT_GAP）。（通常，事务不能忽略仍在等待的请求是为了避免使等待者饿死。您可以在deadlock_on_lock_upgrade.test中看到这种情况的详细描述）

“锁拆分”技术使用上面给出的方程式，并从它们得出needed - possessed = missing：在我们的示例中： X – X，REC_NOT_GAP = X，GAP，因此对X的事务请求被悄悄地转换为更适度的请求：仅针对X ，GAP。在这种特殊情况下，这意味着可以立即授予该请求（回想一下*，GAP请求不必等待任何东西），从而避免了等待和死锁。

二级索引

如前所述，每个索引都可以看作是一个单独的轴，具有自己的点和间隙，可以锁定这些点和间隙，这会稍微有些复杂。通过遵循一些常识规则，您可能会发现自己对于给定的查询必须锁定哪些点和间隙。基本上，您要确保如果某个事务修改了会影响另一事务的结果集的内容，则此读取事务所需的锁必须与进行修改的事务所需的锁互斥，而不管查询计划如何。有几种方法可以设计规则来实现这一目标。

例如，考虑一个简单的表：

CREATE TABLE point2D(
  x INT NOT NULL PRIMARY KEY,
  y INT NOT NULL UNIQUE 
);
INSERT INTO point2D (x,y) VALUES
  (0,3),      
        (1,2), 
                    (3,1),
             (2,0);

让我们尝试通过以下方式找出需要哪些锁：

DELETE FROM point2D WHERE x=1;

有两个轴：x和y。似乎合理的是我们至少应锁定x轴上的point（1）。y轴呢？我们可以避免在y轴上锁定任何东西吗？老实说，我相信这取决于数据库的实现，但是请考虑

SELECT COUNT(*) FROM point2D WHERE y=2 FOR SHARE;

如果锁仅存储在x轴上，则必须运行。SELECT将从y列上的索引来找到匹配的行开始，但是要知道它是否被锁定，就必须知道其x值。这是一个合理的要求。实际上，InnoDB确实在每个二级索引条目中存储了主键的列（示例中的x），因此在索引中为y查找x的值并不重要。但是，请回想一下，在InnoDB中，锁并不真正与x的值绑定（例如，这可能是一个相当长的字符串），而是与heap_no（我们用作位图中的偏移量的短数字）相关联–您需要知道heap_no检查锁的存在。因此，您现在必须进入主索引并加载包含该记录的页，以便了解该记录的heap_no值。

另一种方法是确保无论使用哪个索引来查找x = 1的行，它的锁将被发现，而不需要查阅任何其他索引。这可以通过将点锁定在y轴上且由y = 2来完成。上面提到的SELECT查询在尝试获取自己的锁时将看到它已被锁定。SELECT应该带什么锁？同样，这可以通过几种方式实现：它可以仅锁定y = 2的y轴上的点，或者也可以跳至主索引并使用x = 1锁定x上的点。正如我已经说过的，出于性能原因，第一种方法似乎更快，因为它避免了在主索引中的查找。

让我们看看我们的怀疑是否符合现实。首先，让我们检查通过二级索引进行选择的事务持有的锁（有时，优化器会选择一个扫描主索引的查询计划，而不是使用一个二级索引，即使在您认为这是疯狂的查询——在这样的决策中存在探索/利用权衡。此外，我们人类关于什么更快的直觉可能是错误的)）

con1> BEGIN;
con1> SELECT COUNT(*) FROM point2D WHERE y=2 FOR SHARE;
con1> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE 
      FROM performance_schema.data_locks WHERE OBJECT_NAME='point2D';
+------------+-----------+-----------+---------------+
| INDEX_NAME | LOCK_TYPE | LOCK_DATA | LOCK_MODE     |
+------------+-----------+-----------+---------------+
| NULL       | TABLE     | NULL      | IS            |
| y          | RECORD    | 2, 1      | S,REC_NOT_GAP |
+------------+-----------+-----------+---------------+

这符合我们的期望。我们看到整个表（IS）上有一个意图锁，并且特定记录上有一个锁，但之前没有间隙（S，REC_NOT_GAP），两者都是“共享的”。请注意，LOCK_DATA列将该记录描述为2,1，因为它以与存储在该行的辅助索引条目中的顺序相同的顺序列出各列。首先是索引列（y），然后是缺少的主键片段（ X）。所以2,1表示<y = 2，x = 1>。

让我们用ROLLBACK使该事务返回到原始状态，我们检查一下DELETE单独使用了哪些锁：

con1> COMMIT;
con1> BEGIN;
con1> DELETE FROM point2D WHERE x=1;
con1> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE FROM performance_schema.data_locks WHERE OBJECT_NAME='point2D';
+------------+-----------+-----------+---------------+
| INDEX_NAME | LOCK_TYPE | LOCK_DATA | LOCK_MODE     |
+------------+-----------+-----------+---------------+
| NULL       | TABLE     | NULL      | IX            |
| PRIMARY    | RECORD    | 1         | X,REC_NOT_GAP |
+------------+-----------+-----------+---------------+

哈，这是令人费解的:我们在整个表(IX)上看到了预期的意图锁，我们在主索引记录本身上看到了锁，两者都是“独占的”，但我们在二级索引上没有看到任何锁。如果DELETE只在主索引上加锁，SELECT只在二级索引上加锁，那么InnoDB如何防止两者并发执行呢?让我们保持这个删除事务打开，并启动另一个客户端，看看它是否能够看到删除的行:

con2> BEGIN;
con2> SELECT COUNT(*) FROM point2D WHERE y=2 FOR SHARE;
⌛

嗯..SELECT被阻止了（很好），让我们检查Performance_schema.data_locks以确定情况如何：

con1> SELECT ENGINE_TRANSACTION_ID trx_id,INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE,LOCK_STATUS 
      FROM performance_schema.data_locks WHERE OBJECT_NAME='point2D';
+-----------------+------------+-----------+-----------+---------------+-------------+
|          trx_id | INDEX_NAME | LOCK_TYPE | LOCK_DATA | LOCK_MODE     | LOCK_STATUS |
+-----------------+------------+-----------+-----------+---------------+-------------+
| 283410363307272 | NULL       | TABLE     | NULL      | IS            | GRANTED     |
| 283410363307272 | y          | RECORD    | 2, 1      | S             | WAITING     |
|            1560 | NULL       | TABLE     | NULL      | IX            | GRANTED     |
|            1560 | PRIMARY    | RECORD    | 1         | X,REC_NOT_GAP | GRANTED     |
|            1560 | y          | RECORD    | 2, 1      | X,REC_NOT_GAP | GRANTED     |
+-----------------+------------+-----------+-----------+---------------+-------------+

哈！我们的事务（283410363307272）正在等待获取二级索引记录<y = 2，x = 1>上的S锁（及其前面的间隙），我们可以看到它必须等待的原因可能是该事务正在执行DELETE（ 1560）使用X，REC_NOT_GAP锁定相同的<y = 2，x = 1> 。

但是……当我们检查1560持有的锁时，仅仅一秒钟之前我们还没有看到任何这样的锁–这个锁只是现在才出现，怎么来的？鉴于1560目前还没有“主动做任何事情”，这更加令人困惑-它如何获得锁？

回想一下Performance_schema.metadata_locks仅显示显式锁，但不显示隐式锁，并且隐式锁可以在需要跟踪谁必须等待谁时立即转换为显式锁。实际上，这意味着当283410363307272请求锁系统授予对<y = 2，x = 1>的S锁时，锁系统首先检查这条记录上是否存在它可以推断的隐式锁。这是一个相当复杂的过程（您可以尝试从源代码lock_sec_rec_some_has_impl 开始跟踪）：

检查page_get_max_trx_id(page)的值——对于每个页面，我们存储了修改过这个二级索引页的所有事务的最大id。删除操作确实将它“撞”到它自己的id(除非它已经更大了)。
然后，我们将max_trx_id与一些trx_rw_min_trx_id（）进行比较，将跟踪仍处于活动状态的事务中的最小ID。换句话说，我们试探性地确定某个活动事务是否有可能对二级索引具有隐式锁，并在此处进行一些权衡：
- 二级索引,我们不跟踪每个记录的max_trx_id ,我们跟踪它整个页面,因此会使用更少的存储,我们可能会假意地认为，我们的记录被修改是合理的，尽管实际上这种修改是应用到同一页上的其他记录
- 我们不会非常仔细地检查这个trx ID是否属于活动事务集，而只是将其与其中的最小ID进行比较（坦率地说，鉴于先前的简化，我们必须采用这种方式来保持正确性：不知道修改该行的事务的实际ID，仅知道其上限）
如果进行试探后发现没有人对此记录持有隐式锁，我们可以在这里停止，因为没有活动的事务的ID低于此页面上提到的修改记录的事务的最大ID。这意味着我们不必查询主索引。
否则，事情会变得混乱。我们进入row_vers_impl_x_locked，它将：
- 在主索引中定位记录（在某些情况下，由于与清除线程的竞争，该记录可能已经丢失了）
- 检索最后一个事务的trx_id来修改此特定行（请注意，这是上面第一个启发式方法的更精确的模拟），并且
- 检查trx_id是否仍处于活动状态（请注意，这是如何更精确地模拟上面的第二个启发式）
- 如果事务仍然处于活动状态，则可能仍然是*在二级索引*上没有隐式锁。您会看到，它可以修改一些非索引的列，在这种情况下，二级索引条目在概念上不受影响，因此不需要隐式锁。为了进行检查，我们必须繁琐地检索该行的先前版本，并精确地检查是否有任何索引列受到某种方式的影响，这在概念上意味着需要锁定。这非常复杂。我不会在这里解释，但是如果您好奇，可以在row_clust_vers_matches_sec 和row_vers_impl_x_locked_low中阅读我的注释
最后，如果认为隐式锁是必需的，则代表其合法所有者（主索引记录头中的trx_id）将其转换为显式锁（始终为X，REC_NOT_GAP类型）。

这里的重点是，在最坏的情况下，您不仅需要从undo日志中检索主索引记录，还需要检索其先前版本，目的是为了确定是否存在隐式锁。在最佳情况下，您只需查看二级索引页面并说“ 没有”。

好的，所以看起来线程执行DELETE有些懒惰，并且SELECT线程正在做一些额外的工作来使DELETE隐式的内容变得明确。

但是，这应该使您感到好奇。如果首先执行SELECT操作，然后再开始DELETE-如果SELECT 仅锁定二级索引，并且DELETE似乎没有获得任何二级索引锁，那么怎么可能被未提交的SELECT阻止呢？在这种情况下，我们也执行隐式到显式的转换吗？考虑到SELECT不应修改任何行，因此不应将其trx_id放在行或页面标题中，这似乎是不可信的，因此没有任何痕迹可以推断出隐式锁。

也许我们发现了一个错误？让我们回滚

con1> ROLLBACK;
con2> ROLLBACK;

并检查以下新场景：

con2> BEGIN;
con2> SELECT COUNT(*) FROM point2D WHERE y=2 FOR SHARE;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

现在在另一个客户端DELETE：

con1> BEGIN;
con1> DELETE FROM point2D WHERE x=1;
⌛

似乎没有错误，就像等待DELETE一样。让我们看看显式锁：

> SELECT ENGINE_TRANSACTION_ID trx_id,INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE,LOCK_STATUS 
  FROM performance_schema.data_locks WHERE OBJECT_NAME='point2D';
+-----------------+------------+-----------+-----------+---------------+-------------+
| trx_id          | INDEX_NAME | LOCK_TYPE | LOCK_DATA | LOCK_MODE     | LOCK_STATUS |
+-----------------+------------+-----------+-----------+---------------+-------------+
|            2077 | NULL       | TABLE     | NULL      | IX            | GRANTED     |
|            2077 | PRIMARY    | RECORD    | 1         | X,REC_NOT_GAP | GRANTED     |
|            2077 | y          | RECORD    | 2, 1      | X,REC_NOT_GAP | WAITING     |
| 283410363307272 | NULL       | TABLE     | NULL      | IS            | GRANTED     |
| 283410363307272 | y          | RECORD    | 2, 1      | S,REC_NOT_GAP | GRANTED     |
+-----------------+------------+-----------+-----------+---------------+-------------+

给超级敏锐读者的技术说明：283410363307272不仅是一个可疑的长数字，而且与我们在前面的示例中看到的ID完全相同。这两个谜团的解释很简单：对于只读事务，InnoDB不会浪费分配真正单调事务ID的时间，而是从trx的内存地址临时派生它）

很酷，我们得到的结果与前一个结果有些对称，但是这次是SELECT具有GRANTED锁，DELETE具有WAITING的锁。（另一个区别是，这一次SELECT有S，REC_NOT_GAP而不是S，坦率地说，我不记得为什么我们还需要前一种情况的间隙锁）

好的，即使我们看到DELETE单独执行并没有创建这样的锁，为什么现在正在执行的DELETE事务具有显式的WAITING锁？

答案是：DELETE确实尝试对二级索引进行了锁定（通过调用lock_sec_rec_modify_check_and_lock），但这涉及到棘手的优化：当Lock System确定可以授予这个锁时（因为已经没有冲突锁，所以我们不创建显式锁），克制了它，因为调用者通知它可以根据需要推断出隐式锁。（为什么？可能避免分配lock_t对象：考虑一个DELETE 操作会影响在主键上形成连续范围的许多行–与它们对应的二级索引条目可能无处不在，因此无法从压缩机制中受益。另外，只要InnoDB中有使用隐式锁的地方，您都必须检查它们，并且如果无论如何都必须检查隐式锁，那么您可能会在适用的情况下使用它们，因为你已经付过“检查费”了）

在我们的案例中，锁系统确定存在冲突，因此创建了一个明确的等待锁来跟踪它。

总而言之，当前版本的InnoDB使用哪种解决方案来防止DELETE与SELECT二级索引之间的冲突？

DELETE锁定两个索引，SELECT锁定一个？
DELETE仅锁定主要对象，SELECT检查两者？

它很复杂，但更像第一种方法，但要注意的是，DELETE在任何可能的情况下二级索引上的锁都是隐式的。

好的，现在我们已经准备好讨论死锁检测，这是我们的下一个话题。

感谢您使用MySQL！

感谢您关注“MySQL解决方案工程师”！

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-11-25，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

本文分享自 MySQL解决方案工程师微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

登录后参与评论

0 条评论

热度