Duplicate Key引发的死锁
- 2019 年 12 月 19 日
- 筆記
在依赖事务的项目中，如果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_TRXG *************************** 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';
