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: 0mysql> 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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_tenmaoeout: 0          trx_is_read_only: 0trx_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: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_tenmaoeout: 0          trx_is_read_only: 0trx_autocommit_non_locking: 02 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';