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