文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >为什么选择更新几乎完全锁定了表？

问为什么选择更新几乎完全锁定了表？
EN

Stack Overflow用户
提问于 2021-07-27 06:16:09
回答 2查看 220关注 0票数 0

我已经学习了MySQL和InnoDB一段时间了，但是这个SQL和它的锁定仍然让我感到困惑。

当我在repeatable read时，使用一个小表，就像MySQL文档提供的那样。

代码语言：javascript
复制
mysql> desc child;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id    | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from child;
+-----+
| id  |
+-----+
|  89 |
|  90 |
| 102 |
| 151 |
+-----+
4 rows in set (0.00 sec)

在本例中，我开始一个事务并键入SQL，输入但尚未提交会话。

之后，我使用performance_schema.data_locks查询锁。

如你所见，这张桌子上现在有6把锁。

下键锁(151，正infinity)

  • Next-Key锁(89，90))下键锁(90,102]

  • Next-Key锁(102 )，151]

  • Next-Key锁(负无穷，89)

)

我们注意到这个表中只有4个记录，这个表中也有5个下键锁，因此几乎整个表都被锁定了。

代码语言：javascript
复制
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM child WHERE id >= 102 for update;
+-----+
| id  |
+-----+
| 102 |
| 151 |
+-----+
2 rows in set (0.00 sec)

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:1158:140616462870928
ENGINE_TRANSACTION_ID: 4592
            THREAD_ID: 80
             EVENT_ID: 14
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616462870928
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL // IX of table
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:1:140616474499104
ENGINE_TRANSACTION_ID: 4592
            THREAD_ID: 80
             EVENT_ID: 14
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499104
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record // Next-Key Lock (151, positive infinity)
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:2:140616474499104
ENGINE_TRANSACTION_ID: 4592
            THREAD_ID: 80
             EVENT_ID: 14
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499104
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 90 // Next-Key Lock (89, 90]
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:3:140616474499104
ENGINE_TRANSACTION_ID: 4592
            THREAD_ID: 80
             EVENT_ID: 14
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499104
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 102 // Next-Key Lock (90, 102]
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:4:140616474499104
ENGINE_TRANSACTION_ID: 4592
            THREAD_ID: 80
             EVENT_ID: 14
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499104
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 151 // Next-Key Lock (102, 151]
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:5:140616474499104
ENGINE_TRANSACTION_ID: 4592
            THREAD_ID: 80
             EVENT_ID: 14
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499104
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 89 // Next-Key Lock (negative infinity, 89]

我同时开始另一个会话，尝试插入一些行，但都失败了。

代码语言：javascript
复制
mysql> insert into child (id) values (88);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into child (id) values (88);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into child (id) values (91);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into child (id) values (100);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into child (id) values (103);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into child (id) values (152);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

在我看来，应该是3个下一个键锁，但在这种情况下不是5个。

因为InnoDB的医生说

InnoDB使用间隙锁或下键锁定扫描的索引范围。

因此，不应该出现(89，90)的下一个键锁，而(90,102 )的下一个键锁应该替换为id 102的记录锁。

看看这个SQL和响应，有人能告诉我发生了什么吗？

mysql
innodb
EN

回答 2

Stack Overflow用户

发布于 2021-07-27 07:12:40

谢谢@Luuk，我插入了更多行并重试。

这一次我得到了我的回应。

代码语言：javascript
复制
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from child;
+-----+
| id  |
+-----+
|  89 |
|  90 |
| 102 |
| 151 |
| 388 |
| 390 |
| 452 |
| 882 |
+-----+
8 rows in set (0.00 sec)

mysql> SELECT * FROM child WHERE id >= 388 for update;
+-----+
| id  |
+-----+
| 388 |
| 390 |
| 452 |
| 882 |
+-----+
4 rows in set (0.00 sec)

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:1158:140616462870928
ENGINE_TRANSACTION_ID: 4609
            THREAD_ID: 80
             EVENT_ID: 36
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616462870928
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL // IX for table
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:6:140616474499104
ENGINE_TRANSACTION_ID: 4609
            THREAD_ID: 80
             EVENT_ID: 36
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499104
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 388 // Record Lock for 388
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:1:140616474499448
ENGINE_TRANSACTION_ID: 4609
            THREAD_ID: 80
             EVENT_ID: 36
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499448
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record // Next-Key Lock for (882, +inf)
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:7:140616474499448
ENGINE_TRANSACTION_ID: 4609
            THREAD_ID: 80
             EVENT_ID: 36
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499448
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 390 // Next-Key Lock for (388, 390]
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:8:140616474499448
ENGINE_TRANSACTION_ID: 4609
            THREAD_ID: 80
             EVENT_ID: 36
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499448
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 452 // Next-Key Lock for (390, 452]
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140616303284368:3:4:9:140616474499448
ENGINE_TRANSACTION_ID: 4609
            THREAD_ID: 80
             EVENT_ID: 36
        OBJECT_SCHEMA: xhinliang_test
          OBJECT_NAME: child
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616474499448
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 882 // Next-Key Lock for (452, 882]
6 rows in set (0.00 sec)

在这种情况下，我们可以看到我们应该看到的锁。

用于388 452]

  • Next-Key

  • 下键锁的表

  • 记录锁(882，+inf)

  • Next密钥锁(388,390

H 110Next-Key Lock for (390,882]H 213f 214)的表

  • 记录锁
票数 0
EN

Stack Overflow用户

发布于 2021-07-27 06:40:30

这是执行SELECT FOR UPDATE时的正常行为。

您可以在这里查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

票数 -2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/68539829

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档