MySQL模拟死锁以及分析和解决方案

什么是死锁

官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。
这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人。你让对面放人,对面让你放人。

如果处理死锁

MySQL有两种死锁处理方式

1.等待,直到超时（innodb_lock_wait_timeout=50s）
2.发起死锁检测,主动回滚一条事务,让其他事务继续执行（innodb_deadlock_detect=on)为了提高性能,一般都是使用死锁检测来进行处理死锁。

死锁的解锁

InnoDB存储引擎会选择回滚undo量最小的事务

死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图,判断有向图是否存在环,存在即有死锁。

回滚

检测到死锁之后,选择插入更新或者删除的行数最少的事务回滚,基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

如何避免发生死锁

收集死锁信息

1.利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
2.调试阶段开启 innodb_print_all_deadlocks,收集所有死锁日志。

死锁案例

CREATE TABLE `test_deadlock` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `test_deadlock` VALUES ('1', 'aa');
INSERT INTO `test_deadlock` VALUES ('2', 'bb');
INSERT INTO `test_deadlock` VALUES ('3', 'cc');
INSERT INTO `test_deadlock` VALUES ('4', 'dd');

案例一

(窗口一)session1

– 第一步
begin;
select * from test_deadlock where id = 1 for update;
– 第三步单独执行下面语句
– update test_deadlock set name = ‘mu’ where id =2;

(窗口二)session2

– 第二步
begin;
delete from test_deadlock where id = 2;
– 第四步单独执行下面语句
– delete from test_deadlock where id = 1;

执行顺序

1.第一步2条语句
2.第二部2条语句
3.第三步一条语句
4.第四歩一条语句

执行结果

操作界面

案例二

(窗口一)session1

– 第一步
begin;
select * from test_deadlock where id = 1 lock in share mode;
– 第三步单独执行下面语句
– update test_deadlock set name = ‘mu’ where id =1;

(窗口二)session2

– 第二步
begin;
select * from test_deadlock where id = 1 lock in share mode;
– 第四步单独执行下面语句
– update test_deadlock set name = ‘cc’ where id = 1;

执行顺序

1.第一步2条语句
2.第二部2条语句
3.第三步一条语句
4.第四歩一条语句

执行结果

操作界面

锁的释放与堵塞

锁释放有二种情况:

1.事务结束(commit/rollback)
2.客户端连接断开

获取锁的等待时间,默认是50秒:

show VARIABLES like ‘innodb_lock_wait_timeout’

死锁发生的条件

1.互斥:同一时刻只能有一个事务持有这把锁;
2.不可剥夺:其他事务需要在这个事务释放锁之后才能获取锁,而不可以强行剥夺;
3.形成等待环路:当多个事务形成等待环路的时候,即发生死锁

死锁了怎么办

查看最近一次死锁的日志

通过show engine innodb status查看到最近的一次死锁日志,通过这个sql语句,我们就能确定造成死锁的事务

死锁日志内容

=====================================
2020-09-15 14:46:28 0x7f732fcff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 37 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 609 srv_active, 0 srv_shutdown, 23969851 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 100
OS WAIT ARRAY INFO: signal count 98
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 29, rounds 870, OS waits 25
RW-sx spins 1, rounds 30, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 30.00 RW-excl, 30.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-15 14:46:15 0x7f7350cf3700
*** (1) TRANSACTION:
TRANSACTION 10298, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7623, OS thread handle 140132789073664, query id 6006191 127.0.0.1 root updating
update medicine_control set current_count=1 where id='2'
 
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10298 lock_mode X locks rec but not gap
Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 6; hex 00000000283a; asc     (:;;
 2: len 7; hex 020000012510db; asc     %  ;;
 3: len 6; hex e5a5b6e5a5b6; asc       ;;
 4: len 12; hex e79b98e5b0bce8a5bfe69e97; asc             ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 80000005; asc     ;;
 7: len 4; hex 80000000; asc     ;;
 8: len 5; hex 6a65656367; asc jeecg;;
 9: len 5; hex 99a60eadf7; asc      ;;
 10: len 3; hex 6a6f62; asc job;;
 11: len 5; hex 99a75e0780; asc   ^  ;;
 
 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10298 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 6; hex 00000000283b; asc     (;;;
 2: len 7; hex 01000002012bd8; asc      + ;;
 3: len 6; hex e788b7e788b7; asc       ;;
 4: len 6; hex e69f90e69f90; asc       ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 80000002; asc     ;;
 7: len 4; hex 80000000; asc     ;;
 8: len 5; hex 6c6979616e; asc liyan;;
 9: len 5; hex 99a67b3730; asc   {70;;
 10: len 3; hex 6a6f62; asc job;;
 11: len 5; hex 99a75e0780; asc   ^  ;;
 
 
*** (2) TRANSACTION:
TRANSACTION 10299, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 7625, OS thread handle 140133576603392, query id 6006195 127.0.0.1 root updating
update medicine_control set current_count=2 where id='1'
 
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10299 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 6; hex 00000000283b; asc     (;;;
 2: len 7; hex 01000002012bd8; asc      + ;;
 3: len 6; hex e788b7e788b7; asc       ;;
 4: len 6; hex e69f90e69f90; asc       ;;
 5: len 4; hex 80000002; asc     ;;
 6: len 4; hex 80000002; asc     ;;
 7: len 4; hex 80000000; asc     ;;
 8: len 5; hex 6c6979616e; asc liyan;;
 9: len 5; hex 99a67b3730; asc   {70;;
 10: len 3; hex 6a6f62; asc job;;
 11: len 5; hex 99a75e0780; asc   ^  ;;
 
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 88 index PRIMARY of table `jeecg-boot`.`medicine_control` trx id 10299 lock_mode X locks rec but not gap waiting
Record lock, heap no 21 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 1; hex 31; asc 1;;
 1: len 6; hex 00000000283a; asc     (:;;
 2: len 7; hex 020000012510db; asc     %  ;;
 3: len 6; hex e5a5b6e5a5b6; asc       ;;
 4: len 12; hex e79b98e5b0bce8a5bfe69e97; asc             ;;
 5: len 4; hex 80000001; asc     ;;
 6: len 4; hex 80000005; asc     ;;
 7: len 4; hex 80000000; asc     ;;
 8: len 5; hex 6a65656367; asc jeecg;;
 9: len 5; hex 99a60eadf7; asc      ;;
 10: len 3; hex 6a6f62; asc job;;
 11: len 5; hex 99a75e0780; asc   ^  ;;
 
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 10301
Purge done for trx's n:o < 10301 undo n:o < 0 state: running but idle
History list length 61
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421608706154464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706153592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706152720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706151848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706150976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706150104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706148360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706147488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706146616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706145744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706144872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421608706144000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 10298, ACTIVE 24 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 7623, OS thread handle 140132789073664, query id 6006198 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2048 OS file reads, 24777 OS file writes, 11472 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.59 writes/s, 0.54 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
0.00 hash searches/s, 0.27 non-hash searches/s
---
LOG
---
Log sequence number          2246453180
Log buffer assigned up to    2246453180
Log buffer completed up to   2246453180
Log written up to            2246453180
Log flushed up to            2246453180
Added dirty pages up to      2246453180
Pages flushed up to          2246453180
Last checkpoint at           2246453180
9242 log i/o's done, 0.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 835752
Buffer pool size   8192
Free buffers       6046
Database pages     2131
Old database pages 788
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1923, created 208, written 13739
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2131, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=920, Main thread ID=140133220153088 , state=sleeping
Number of rows inserted 416, updated 2599, deleted 440, read 821958
0.00 inserts/s, 0.08 updates/s, 0.00 deletes/s, 0.11 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

其中：

=====================================
2020-09-15 14:46:28 0x7f732fcff700 INNODB MONITOR OUTPUT
=====================================

这段记录的是查询死锁日志的时间

------------------------
LATEST DETECTED DEADLOCK
------------------------

这段后面记录的就是此次死锁的信息,有几部分

1.事务1信息

也就是这一部分:

其中：

TRANSACTION 10298,是此事务的id。

ACTIVE 11 sec,活跃时间11秒。

starting index read,事务当前正在根据索引读取数据。

starting index read这个描述还有其他情况：

mysql tables in use 1, locked 1,表示此事务修改了一个表，锁了一行数据。

MySQL thread id 7623,这是线程id

query id 6006191,这是查询id

127.0.0.1 root updating,数据库ip地址,账号,更新语句。

update medicine_control set current_count=1 where id=‘2’,这是正在执行的sql。

2.事务1持有的锁

也就是这段:

其中：

RECORD LOCKS,表示持有的是行级锁。

index PRIMARY,表示锁的是主键索引。

table jeecg-boot.medicine_control,表示锁的具体是哪个表。

trx id 10298,事务id,和上面的TRANSACTION相同。

lock_mode X locks,锁模式：排它锁。（X：排他锁，S：共享锁）

but not gap,非间隙锁

后面的0至11,代表锁的具体哪一行,0至11指的是表的第1至第12个字段,0开头的这行表示id列,可见锁的是id=1的那一行,可知这里的事务1就是上面的事务A。

3.事务1正在等待的锁

也就是这段：

其中：

index PRIMARY,表示等待的是主键的锁。

table jeecg-boot.medicine_control,表示等待的表。

trx id 10298,当前事务1的id。注意这里不是持有目标锁的事务的id,而是当前事务id。

lock_mode X locks,表示目标锁是排它锁。

but not gap,表示非间隙锁。

waiting,表示当前事务正在等待。

后面的0至11,表示等待的行,可见等待的是id=2的行的锁。

4.事务2信息

也就是这一段:

格式和事务1信息相同。

TRANSACTION 10299,表示事务id是10299。

update medicine_control set current_count=2 where id=‘1’,表示事务2正在执行的sql。

5.事务2正在持有的锁

也就是这段：

可见事务2持有id=2的行锁,也就是说这里的事务2就是上面的事务B。

6.事务2正在等待的锁

也就是这段：

可见事务2正在等待id=1的行锁。

7.死锁处理结果

也就是这段:
表示MySQL最终决定回滚事务2,也就是上面的事务B,这和上面事务B返回的死锁信息是一致的。
另外,日志里还记录当前SESSION和事务列表,也就是这段：



可见多数的SESSION下的事务都没开始,注意最后的这段：

表示id为10298的事务(也就是事务1)还没提交。

死锁后怎么恢复

发现死锁

新版本Mysql发现了死锁,并选择回滚其中一个事务解决了死锁问题,但是老版本的mysql没有死锁检测机制,如果出现死锁,连接可能都会处于等待状态,直到50S的锁等待超时,这会长时间占据数据库连接,导致数据库连接池连接耗尽,tomcat无法获取数据库连接,一直处于等待状态,随后tomcat队列排满后,整个服务就会处于僵死状态，在生产环境中是非常大的事故。如果发现mysql执行SQL语句长时间不响应。我们可以通过show full processlist 命令查看当前所有数据库连接状态,如果连接在等待锁资源,在State状态会显示waiting for table metadata lock信息,同时可以通过info信息查看数据库连接执行了哪一条sql语句,如果所有的等待锁的连接执行的sql语句都涉及到了同一张表,那么就能断定哪站表发生了死锁

查找连接

知道哪张表被锁定仍然无法解决我们的问题,我们需要知道哪个数据库连接对表加了锁,才能kill连接,从mysql5.5开始,information_schema增加了三个关于锁的表,通过这三张表,我们能够找到连接id

查看锁信息

show status like ‘innodb_row_lock_%’; --show命令是一个概要信息,里面包括一些行锁的信息

innodb_locks

这张表提供了各个事务请求的数据库锁但是仍然没有获取的数据库锁。这张表提供的最重要的信息是请求锁的事务id

select * from information_schema.innodb_locks; – 当前出现的锁

innodb_trx

通过这张表我们能查到当前innodb引擎执行的所有事务id以及当前执行事务的数据库连接id,于是便能通过kill命令杀死数据库连接,更关键的是我们能查找到事务正在执行的sql语句

select * from information_schema.innodb_trx; --当前运行的所有事务,还有具体的语句

innodb_lock_waits

这张表中requesting_trx_id代表了申请锁资源的事务ID,requesting_lock_id代表申请的锁id,blocking_trx_id代表了阻塞事务70E的事务id,blocking_lock_id代表了阻塞事务70E的锁的ID

select * from information_schema.innodb_lock_waits – 锁等待的对应关系

出现死锁后,我们可以通过innodb_lock_waits获取相互等待的事务id,通过事务id从innodb_trx查找到数据库连接id,然后使用kill杀死连接

怎么避免死锁

1.在程序中,操作多张表时,尽量以相同的顺序来访问(避免形成等待环路);
2.批量操作单张表数据的时候,先对数据进行排序(避免形成等待环路)；
3.申请足够级别的锁,如果要操作数据,就申请排他锁;日志
4.尽量使用索引访问数据,避免没有where条件的操作,避免锁表;
5.如果可以,大事务化成小事务;
6.使用等值查询而不是范围查询查询数据,命中记录,避免间隙锁对并发的影响。

MySQL的八种锁

1.行锁（Record Locks）

行锁是作用在索引上的。

2.间隙锁（Gap Locks）

间隙锁是锁住一个区间的锁。

这个区间是一个开区间,范围是从某个存在的值向左直到比他小的第一个存在的值，所以间隙锁包含的内容就是在查询范围内,而又不存在的数据区间。

比如有id分别是1,10,20,要修改id<15的数据,那么生成的间隙锁有以下这些：(-∞,1)，(1,10)，(10,20),此时若有其他事务想要插入id=11的数据,则需要等待。

间隙锁是不互斥的。

作用是防止其他事务在区间内添加记录,而本事务可以在区间内添加记录,从而防止幻读。

在可重复读这种隔离级别下会启用间隙锁,而在读未提交和读已提交两种隔离级别下,即使使用select … in share mode或select … for update,也不会有间隙锁,无法防止幻读。

3.临键锁（Next-key Locks）

临键锁=间隙锁+行锁,于是临键锁的区域是一个左开右闭的区间。

隔离级别是可重复读时,select … in share mode或select … for update会使用临键锁,防止幻读。普通select语句是快照读,不能防止幻读。

4.共享锁/排他锁（Shared and Exclusive Locks）

共享锁和排它锁都是行锁。共享锁用于事务并发读取,比如select … in share mode。排它锁用于事务并发更新或删除。比如select … for update

5.意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

意向共享锁和意向排他锁都是表级锁。

官方文档中说,事务获得共享锁前要先获得意向共享锁,获得排它锁前要先获得意向排它锁。

意向排它锁互相之间是兼容的。

6.插入意向锁（Insert Intention Locks）

插入意向锁锁的是一个点,是一种特殊的间隙锁,用于并发插入。

插入意向锁和间隙锁互斥。插入意向锁互相不互斥。

7.自增锁（Auto-inc Locks）

自增锁用于事务中插入自增字段。5.1版本前是表锁,5.1及以后版本是互斥轻量锁。

自增所相关的变量有：

auto_increment_offset,初始值

auto_increment_increment,每次增加的数量

innodb_autoinc_lock_mode,自增锁模式

其中：

innodb_autoinc_lock_mode=0,传统方式,每次都产生表锁。此为5.1版本前的默认配置。

innodb_autoinc_lock_mode=1,连续方式。产生轻量锁,申请到自增锁就将锁释放，simple insert会获得批量的锁,保证连续插入。此为5.2版本后的默认配置。

innodb_autoinc_lock_mode=2,交错锁定方式。不锁表,并发速度最快。但最终产生的序列号和执行的先后顺序可能不一致,也可能断裂。

8.预测锁

主要用于存储了空间数据的空间索引