Duplicate Key引发的死锁

在依赖事务的项目中，如果SQL语句设计不合理或者执行顺序不合理，就容易引发死锁。本文介绍一个因为Duplicate Key引发的死锁

场景描述

• 数据表user

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  #唯一索引
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 当前数据

mysql> SELECT * FROM user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tenmao  | NULL |
+----+------+------+
1 row in set (0.00 sec)

• 事务1

BEGIN;
# Duplicate key(所以插入失败)
INSERT INTO user VALUES(null, 'tenmao', 3);
# 此时再执行事务2的语句（会等待）
# 插入失败则更新
UPDATE user SET age=3 WHERE name='tenmao';
COMMIT;

• 事务2

# 单SQL事务（默认）
UPDATE user SET age =3 WHERE name ='tenmao';

死锁复现

在事务1执行INSERT INTO user VALUES(null, 'tenmao', 3);失败后，执行事务2，事务2等待后，再继续执行事务1，触发死锁。

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

mysql> INSERT INTO user VALUES(null, 'tenmao', 3);
ERROR 1062 (23000): Duplicate entry 'tenmao' for key 'uk_name'
mysql> UPDATE user SET age=3 WHERE name='tenmao';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

• 另外一个单SQL事务

mysql> UPDATE user SET age =3 WHERE name ='tenmao';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看锁的情况

当事务2等待时，查看InnoDB锁的情况，分别是INFORMATION_SCHEMA.INNODB_LOCKS, INFORMATION_SCHEMA.INNODB_LOCK_WAITS, INFORMATION_SCHEMA.INNODB_TRX

# 获取锁的情况
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+---------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| lock_id       | lock_trx_id | lock_mode | lock_type | lock_table      | lock_index | lock_space | lock_page | lock_rec | lock_data |
+---------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
| 50505:361:4:2 | 50505       | X         | RECORD    | `tenmao`.`user` | uk_name    |        361 |         4 |        2 | 'tenmao'     |
| 50504:361:4:2 | 50504       | S         | RECORD    | `tenmao`.`user` | uk_name    |        361 |         4 |        2 | 'tenmao'     |
+---------------+-------------+-----------+-----------+-----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

# 查看锁等待情况
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 50505             | 50505:361:4:2     | 50504           | 50504:361:4:2    |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

# 查看事务状态
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 50505
                 trx_state: LOCK WAIT
               trx_started: 2019-12-13 14:55:00
     trx_requested_lock_id: 50505:361:4:2
          trx_wait_started: 2019-12-13 14:59:49
                trx_weight: 2
       trx_mysql_thread_id: 2557
                 trx_query: update user set age =3 where name ='tenmao'
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_tenmaoeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 50504
                 trx_state: RUNNING
               trx_started: 2019-12-13 14:54:25
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4
       trx_mysql_thread_id: 2556
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_tenmaoeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

死锁分析

• 事务1

BEGIN;
# Duplicate key(所以插入失败)
INSERT INTO user VALUES(null, 'tenmao', 3);
# 此时因为重复键，事务拿到记录的`S锁`
# 此时再执行事务2的语句（会等待）
# 插入失败则更新（获取X锁，但是因为事务2排在前面，需要事务2释放`X锁`，另一方面事务2也在等待事务1释放`S锁`，所以形成死锁）
UPDATE user SET age=3 WHERE name='tenmao';
COMMIT;

• 事务2

# 单SQL事务（尝试获取X锁，等待事务1释放`S锁`）
UPDATE user SET age =3 WHERE name ='tenmao';

参考

• 记录一次Mysql死锁排查过程