在依赖事务的项目中，如果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';
