Table of Contents

一、表的死锁

产生原因:

用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

用户A–》A表（表锁）–》B表（表锁）

用户B–》B表（表锁）–》A表（表锁）

解决方案：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

二、行级锁死锁

产生原因1：

如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案1：

SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划”对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

产生原因2：

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。

产生原因3：每个事务只有一个SQL，但是有些情况还是会发生死锁。

事务1,从name索引出发 , 读到的[hdc, 1], [hdc, 6]均满足条件, 不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁, 加锁顺序为先[1,hdc,100], 后[6,hdc,10]
事务2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。
但是加锁时发现跟事务1的加锁顺序正好相反，两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

解决方案: 如上面的原因2和原因3, 对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；

三、死锁案例演示

接下来我们通过一个案例,来演示一下对于发生死锁的分析过程 :

1. 数据准备

create table test_deadLock(
  id int primary key,
  name varchar(50),
  age int
);

insert into test_deadLock values(1,'lisi',11),(2,'zhangsan',22),(3,'wangwu',33);

2. 数据库隔离级别查看

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

3. 查看加锁信息

-- information_schema.innodb_trx: 当前出现的锁
select * from information_schema.innodb_locks;

-- information_schema.innodb_trx: 当前运行的所有事务
select * from information_schema.innodb_trx;

-- information_schema.innodb_lock_waits: 锁等待的对应关系
select * from information_schema.innodb_lock_waits;

4. 查看InnoDB状态 ( 包含最近的死锁日志信息 )

show engine innodb status;

四、案例分析

作为第一个示例，这里我们进行细致的分析，两个事物每执行一条SQL，可以查看下innodb锁状态及锁等待信息以及当前innodb事务列表信息，最后可以通过 show engine innodb status ;查看最近的死锁日志信息。

1、事务1, 执行begin开始事务执行一条SQL,查询 id=1 的数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_deadLock where id = 1 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | lisi | 11 |
+----+------+------+
1 row in set (0.00 sec)

分析加锁过程:

事务1进行首先申请IX锁 (意向排它锁,因为是for update)；
然后申请X锁进行查询是否存在 id = 1 的记录；
存在该记录,因为id字段是唯一索引,所以添加的是 Record Lock；

2、查看
information_schema.innodb_trx表，发现存在事务1 的信息

select
  trx_id '事务id',
  trx_state '事务状态',
  trx_started '事务开始时间',
  trx_weight '事务权重',
  trx_mysql_thread_id '事务线程ID',
  trx_tables_locked '事务拥有多少个锁',
  trx_lock_memory_bytes '事务锁住的内存大小',
  trx_rows_locked '事务锁住的行数',
  trx_rows_modified '事务更改的行数'
from information_schema.innodb_trx;

3、执行事务2的 delete语句, 删除成功,因为id=3的数据并没有被加锁

mysql> delete from test_deadLock where id = 3; -- 删除成功

查看事务信息,innodb_trx 已经有T1 T2两个事务信息。

select
  trx_id '事务id',
  trx_state '事务状态',
  trx_started '事务开始时间',
  trx_weight '事务权重',
  trx_mysql_thread_id '事务线程ID',
  trx_tables_locked '事务拥有多少个锁',
  trx_lock_memory_bytes '事务锁住的内存大小',
  trx_rows_locked '事务锁住的行数',
  trx_rows_modified '事务更改的行数'
from information_schema.innodb_trx;

4、事务1对 id=3 的记录进行修改操作，发生阻塞。因为id=3的数据的X锁已经被事务2拿到，其他事务的操作只能被阻塞。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_deadLock where id = 1 for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | lisi | 11 |
+----+------+------+
1 row in set (0.00 sec)

mysql> update test_deadLock set name = 'aaa' where id = 3;
-- 阻塞

5、查看当前锁信息

-- 查看当前锁信息
select
  lock_id '锁ID',
  lock_trx_id '拥有锁的事务ID',
  lock_mode '锁模式',
  lock_type '锁类型' ,
  lock_table '被锁的索引',
  lock_space '被锁的表空间号',
  lock_page '被锁的页号',
  lock_rec '被锁的记录号',
  lock_data '被锁的数据'
from information_schema.innodb_locks;

lock_rec=4 表示是对唯一索引进行的加锁。 lock_mode= X 表示这里加的是X锁。

-- 查看锁等待的对应关系select  requesting_trx_id '请求锁的事务ID',  requested_lock_id '请求锁的锁ID',  blocking_trx_id '当前拥有锁的事务ID',  blocking_lock_id '当前拥有锁的锁ID'from information_schema.innodb_lock_waits;

6、事务2 执行删除操作，删除 id = 1的数据成功。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test_deadLock where id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test_deadLock where id = 1;
Query OK, 1 row affected (0.00 sec)

7、但是事务1已经检测到了死锁的发生

mysql> update test_deadLock set name = 'aaa' where id = 3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

--事务1 commit,更新操作失败
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_dead;
-- ERROR 1146 (42S02): Table 'test_lock.test_dead' doesn't exist

mysql> select * from test_deadLock;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | lisi | 11 |
| 2 | zhangsan | 22 |
+----+----------+------+
2 rows in set (0.00 sec)

-- 事务2 commit ,删除操作成功
mysql> commit;

mysql> select * from test_deadLock;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | lisi | 11 |
| 2 | zhangsan | 22 |
+----+----------+------+
2 rows in set (0.00 sec)

8、查看死锁日志

ACTIVE 309秒 sec : 表示事务活动时间；
starting index read : 表示读取索引；
tables in use 1: 表示有一张表被使用了；
LOCK WAIT 3 lock struct(s): 表示该事务的锁链表的长度为3,每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等；
heap size 1136 : 为事务分配的锁堆内存大小；
3 row lock(s): 表示当前事务持有的行锁个数/gap锁的个数；

LATEST DETECTED DEADLOCK
------------------------
2022-04-04 06:22:01 0x7fa66b39d700
*** (1) TRANSACTION: 事务1
TRANSACTION 16472, ACTIVE 309 sec starting index read
-- 事务编号 16472,活跃秒数 309,starting index read 表示事务状态为根据索引读取数据.

mysql tables in use 1, locked 1
-- 表示有一张表被使用了 ,locked 1 表示表上有一个表锁,对于DML语句为LOCK_IX

LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 20, OS thread handle 140352739985152, query id 837 localhost
root updating

update test_deadLock set name = 'aaa' where id = 3
--当前正在等待锁的SQL语句.

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table
`test_lock`.`test_deadLock` trx id 16472 lock_mode X locks rec but not gap
waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000004059; asc @Y;;
2: len 7; hex 4100000193256b; asc A %k;;
3: len 6; hex 77616e677775; asc wangwu;;
4: len 4; hex 80000021; asc !;;

*** (2) TRANSACTION:
TRANSACTION 16473, ACTIVE 300 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 140352740251392, query id 838 localhost
root updating
delete from test_deadLock where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table
`test_lock`.`test_deadLock` trx id 16473 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000004059; asc @Y;;
2: len 7; hex 4100000193256b; asc A %k;;
3: len 6; hex 77616e677775; asc wangwu;;
4: len 4; hex 80000021; asc !;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 72 index PRIMARY of table
`test_lock`.`test_deadLock` trx id 16473 lock_mode X locks rec but not gap
waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000403d; asc @=;;
2: len 7; hex b0000001240110; asc $ ;;
3: len 4; hex 6c697369; asc lisi;;
4: len 4; hex 8000000b; asc ;;

五、死锁总结

对索引加锁顺序的不一致很可能会导致死锁, 所以如果可以, 尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候, 如果事先对数据排序, 保证每个线程按固定的顺序来处理记录, 也可以大大降低出现死锁的可能。
间隙锁往往是程序中导致死锁的真凶, 由于默认情况下 MySQL 的隔离级别是 RR,所以如果能确定幻读和不可重复读对应用的影响不大, 可以考虑将隔离级别改成 RC, 可以避免 Gap 锁导致的死锁。
为表添加合理的索引, 如果不走索引将会为表的每一行记录加锁, 死锁的概率就会大大增大。
避免大事务, 尽量将大事务拆成多个小事务来处理。因为大事务占用资源多, 耗时长, 与其他事务冲突的概率也会变高。
避免在同一时间点运行多个对同一表进行读写的脚本, 特别注意加锁且操作数据量比较大的语句。
设置锁等待超时参数：innodb_lock_wait_timeout，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。