一、表的死锁

产生原因:

用户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. 事务1,从name索引出发 , 读到的[hdc, 1], [hdc, 6]均满足条件, 不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁, 加锁顺序为先[1,hdc,100], 后[6,hdc,10]
2. 事务2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。
3. 但是加锁时发现跟事务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. 事务1进行首先申请IX锁 (意向排它锁,因为是for update)；
2. 然后申请X锁进行查询是否存在 id = 1 的记录；
3. 存在该记录,因为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 ;;

五、死锁总结

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