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';
