Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL死锁案例_唯一索引

MySQL死锁案例_唯一索引

1. 情况描述

近期在MySQL数据库中产生了死锁的情况，与通常的死锁不同，由于表中有唯一索引，所以加锁方式也比较有趣，本文将对于该例进行阐述(本文将对数据进行脱敏操作)：

环境描述:
+ 隔离级别：READ-COMMITTED

表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

show create table  \G

CREATE TABLE if not exists `uniq` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`aa` int(11) DEFAULT NULL,

`bb` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_ab` (`aa`,`bb`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意其中存在唯一索引：UNIQUE KEY uniq_ab (aa,bb)

2. 分析

SQL执行顺序：
SQL执行顺序

问题解析：
+ 对于唯一索引，insert成功后，会加上X lock；

  • 对于唯一索引的插入，需要在插入前进行duplicate key的检查，所以需要申请加上S lock，由于S lock与X lock不兼容，所以产生锁等待；
  • 由于GAP与INSERT_INTENTION不兼容，所以产生锁等待，至此死锁产生。

3. 基本概念

MySQL Innodb中的锁：
(参照：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html)：

  • S LOCK：共享锁，也称读锁
  • X LOCK：排它锁，也称写锁
  • IS LOCK：暗示一个事务需要加共享锁
  • IX LOCK：暗示一个事务需要加排它锁

对应的兼容关系：
锁兼容关系

此外，还有一套更为精准的判断逻辑，以符合更多场景，所以MySQL还具有以下几类的锁类型：

  • Record lock(记录锁)：加在索引行上的锁。
  • GAP lock(间隙锁)：加在record两侧间隙上的锁，但是不包括数据本身。
  • Next-key lock：Record lock + GAP lock
  • INSERT_INTENTION lock(插入意向锁)：在insert之前，需要申请INSERT_INTENTION lock

举例:

1
2
3
4
5
6
7
8
9
10
11

   Session1:                                   
   mysql> CREATE TABLE if not exists child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;                             
   mysql> INSERT INTO child (id) values (90),(102);                                           
   mysql> START TRANSACTION;                                           
   mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;                     

   Session2: 
   mysql> START TRANSACTION;                                                                
   mysql> INSERT INTO child (id) VALUES (101);

则Session2会产生INSERT_INTENTION lock waiting

Insert对于唯一索引的加锁方式：
对于insert的行，加上X lock，在Insert之前，需要加上 INSERT_INTENTION lock。

4. 问题解决

（1）方案一(调整后端)：
由于事发的逻辑为：一个事务中进行多次insert。在数据库层面的展示即为多次申请锁资源，并且是并发的事务，所以当插入的数据出现资源抢占时，容易发生死锁。

所以建议：insert时插入多个值，一次性申请该sql的所有锁资源。这样，则可以避免多次申请锁资源，同时在性能上也能得以提升。

（2）方案二(调整前端逻辑)：
规避重复触发的情况，防止不同事务在唯一索引上插入相同数据。

5. 附加

innodb-record-level-locks

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
间隙锁定可以显式禁用：将事务隔离级别更改为READ-COMMITTED或启用innodb_locks_unsafe_for_binlog系统变量。在这种情况下，对于搜索和索引扫描，间隙锁定将被禁用，此时gap锁仅用于外键约束检查和重复键检查。

comments powered by Disqus