insert唯一索引死锁问题-1

梦江河

已于 2023-02-07 22:26:05 修改

阅读量419

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

于 2023-02-06 23:26:35 首次发布

本文链接：https://blog.csdn.net/weixin_42002747/article/details/128909850

版权

Mysql 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

MySQL版本：8.0.29
测试表：
在这里插入图片描述
测试数据：

开始测试：
事务1执行：

加锁分析：

mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140353238994056:1068:140353715991872
ENGINE_TRANSACTION_ID: 4885
            THREAD_ID: 50
             EVENT_ID: 33
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: staff
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140353715991872
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
1 row in set (0.00 sec)

事务2执行：发生阻塞
在这里插入图片描述
事务1执行：

事务2发生死锁了

加锁分析：

事务1的ID是4885
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140353238994056:1068:140353715991872
ENGINE_TRANSACTION_ID: 4885
            THREAD_ID: 50
             EVENT_ID: 33
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: staff
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140353715991872
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140353238994056:7:5:5:140353734831648
ENGINE_TRANSACTION_ID: 4885
            THREAD_ID: 51
             EVENT_ID: 22
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: staff
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_staff_no
OBJECT_INSTANCE_BEGIN: 140353734831648
            LOCK_TYPE: RECORD
            LOCK_MODE: X,REC_NOT_GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 7, 29
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140353238994056:7:5:5:140353734831992
ENGINE_TRANSACTION_ID: 4885
            THREAD_ID: 50
             EVENT_ID: 35
        OBJECT_SCHEMA: db_study
          OBJECT_NAME: staff
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_staff_no
OBJECT_INSTANCE_BEGIN: 140353734831992
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION
          LOCK_STATUS: GRANTED
            LOCK_DATA: 7, 29
3 rows in set (0.00 sec)

事务1持有锁：
1）表staff的意向排他锁
2）staff_no索引：记录(staff_no=7)排他锁
3）staff_no索引：间隙（5，7）的插入意向锁

分析：
事务1首先插入staff_no=7的唯一索引记录，这时候加的隐式锁，然后事务2插入staff_no=7的唯一索引记录，会阻塞（因为事务1已经在前面insert了，这时候事务2会给事务1加一把staff_no=7的记录X锁，然后给自己加staff_no索引上范围（5，7]的next-key lock，然后事务1插入staff_no=6的唯一索引记录。但因为事务2已经加了范围（5，7]的next-key lock，即事务2在等待事务1释放记录锁，而事务1在等待事务2释放next-key lock，所以发生了死锁。

参考资料