MySQL - 死锁的产生及解决方案

MySQL - 死锁的产生及解决方案

大家好,我是一安~

简介

死锁指两个或多个事务相互等待对方释放锁,从而导致进程无法继续执行的一种情况。当一个事务需要锁定一些资源时,如果这些资源已经被其他事务锁定,则该事务必须等待其他事务释放锁,才能继续执行。如果多个事务同时等待对方释放锁,就会发生死锁。

总结:

  • 互斥条件:一个资源每次只能被一个进程使用。
  • 请求和保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
  • 不剥夺条件:进程已获得的资源,在未使用完之前,不能被其他进程强行剥夺,只能由该进程自己释放。
  • 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

产生死锁的必要满足以上4个条件,缺一不可

案例分析

表锁

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

行锁

如果在事务中执行了一条没有索引条件的查询,引发全表扫描,把行级锁上升为全表记录锁定(等价于表级锁),多个这样的事务执行后,就很容易产生死锁和阻塞。图片

如何解决

以上死锁基本是由于程序的BUG产生的,在对数据库的多表或单表操作时,尽量按照相同的顺序进行处理且避免同时锁定两个资源。

必须同时锁定两个资源时,要保证在任何时刻都应该按照相同的顺序来锁定资源。

select for update:

  • 如果有唯一索引,命中了唯一记录:行锁,互斥锁;
  • 如果有唯一索引,没命中:gap锁,另一个事务也可以获得这个gap锁,但是不能插入数据;(后续有死锁可能)
  • 如果有普通索引,命中了记录:行锁+gap锁;(后续有死锁可能)
  • 如果有普通索引,没有命中记录:gap锁,和情况2相同;(后续有死锁可能)
  • 如果没有索引,直接锁全表,互斥,直接阻塞别的事务

演示

说明:

  • 共享锁(S):允许多个事务同时读取同一份数据,但不允许对数据进行修改。当一个事务获得共享锁时,其他事务只能获得共享锁,不能获得排他锁。
  • 排他锁(X):只允许一个事务对数据进行修改,其他事务不能读取或修改该数据。当一个事务获得排他锁时,其他事务不能获得任何类型的锁。
  • 意向共享锁(IS):表示事务想要在某个数据上获得共享锁,但并不是真正的共享锁,只是一个辅助锁。当一个事务获得意向共享锁时,其他事务可以获得共享锁或意向共享锁,但不能获得排他锁。
  • 意向排他锁(IX):表示事务想要在某个数据上获得排他锁,但并不是真正的排他锁,只是一个辅助锁。当一个事务获得意向排他锁时,其他事务只能获得意向共享锁,不能获得任何类型的锁。
CREATE TABLE `test_lock` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
 `age` int DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_name` (`user_name`),
 KEY `idx_city` (`city`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;

insert into test_lock(id,user_name,age,city) values(1,'杰伦',20,'深圳');
insert into test_lock(id,user_name,age,city) values(5,'奕迅',25,'湛江');
insert into test_lock(id,user_name,age,city) values (9,'俊杰',29,'广州');

案例1(主键索引):

BEGIN;
select * from test_lock where id = 1 for update;

select * from performance_schema.data_locks;

ROLLBACK;
图片

查询条件是主键索引,命中数据库表记录时,一共会加两把锁:一把IX意向排他锁(表锁,不影响插入),一把对应主键的X排他锁(行锁,影响对应主键那一行的插入)。

案例2(唯一索引):

BEGIN;
select * from test_lock where user_name ='杰伦' for update;

select * from performance_schema.data_locks;

ROLLBACK;
图片

查询条件是唯一索引,命中数据库表记录时,一共会加三把锁:一把IX意向排他锁 (表锁,不影响插入),一把对应主键的X排他锁(行锁),一把对应唯一索引的X排他锁 (行锁)。

案例3(普通索引):

BEGIN;
select * from test_lock where city ='深圳' for update;

select * from performance_schema.data_locks;

ROLLBACK;
图片

查询条件是普通索引,命中数据库表记录时,一共会加四把锁:一把IX意向排他锁 (表锁,不影响插入),一把对应主键的X排他锁(行锁),一把对应普通索引的X排他锁 (行锁),一把对应普通索引的Gap间隙锁 (锁住一个范围,会影响插入)。

案例4(非索引):

BEGIN;
select * from test_lock where age =20 for update;

select * from performance_schema.data_locks;

ROLLBACK;
图片

查询条件不是索引,命中或非命中数据库表记录时,都会加一个IX锁(表锁,不影响插入),每一行实际记录行的X锁,还有对应于supremum pseudo-record的虚拟全表行锁(通俗点讲,其实就是锁表了)

案例5(索引未命中):

BEGIN;
select * from test_lock where id = 4 for update;

select * from performance_schema.data_locks;

ROLLBACK;
图片

查询条件是主键,未命中数据库表记录时,查询条件是中间值,会加一个IX锁(表锁,不影响插入),一把对应主键索引的X排他锁 (行锁),一把对应主键索引的Gap间隙锁 (锁住一个范围,会影响插入)

BEGIN;
select * from test_lock where id = 10  for update;

select * from performance_schema.data_locks;

ROLLBACK;
图片

查询条件是主键,未命中数据库表记录时,查询条件非中间值,会加一个IX锁(表锁,不影响插入),每一行实际记录行的X锁,还有对应于supremum pseudo-record的虚拟全表行锁(通俗点讲,其实就是锁表了)

死锁排查

查看死锁日志

通过 show engine innodb status命令查看近期死锁日志信息,主要关注日志中的 LATEST DETECTED DEADLOCK部分:

=====================================
2023-04-19 15:27:25 0x9b1c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 147 srv_active, 0 srv_shutdown, 587314 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3596
OS WAIT ARRAY INFO: signal count 3509
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-04-19 15:27:18 0x121c
*** (1) TRANSACTION:
TRANSACTION 39296, ACTIVE 26 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 383, OS thread handle 37880, query id 9857 localhost 127.0.0.1 root statistics
select * from test_lock where id = 5  for update

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 899 page no 4 n bits 72 index PRIMARY of table `springcloud_test`.`test_lock` trx id 39296 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000009974; asc      t;;
 2: len 7; hex 81000000b00110; asc        ;;
 3: len 6; hex e69db0e4bca6; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 6; hex e6b7b1e59cb3; asc       ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 899 page no 4 n bits 72 index PRIMARY of table `springcloud_test`.`test_lock` trx id 39296 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000993a; asc      :;;
 2: len 7; hex 81000000990110; asc        ;;
 3: len 6; hex e5a595e8bf85; asc       ;;
 4: len 4; hex 80000019; asc     ;;
 5: len 6; hex e6b99be6b19f; asc       ;;


*** (2) TRANSACTION:
TRANSACTION 39297, ACTIVE 14 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 384, OS thread handle 38372, query id 9861 localhost 127.0.0.1 root statistics
select * from test_lock where id = 1  for update

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 899 page no 4 n bits 72 index PRIMARY of table `springcloud_test`.`test_lock` trx id 39297 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00000000993a; asc      :;;
 2: len 7; hex 81000000990110; asc        ;;
 3: len 6; hex e5a595e8bf85; asc       ;;
 4: len 4; hex 80000019; asc     ;;
 5: len 6; hex e6b99be6b19f; asc       ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 899 page no 4 n bits 72 index PRIMARY of table `springcloud_test`.`test_lock` trx id 39297 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000009974; asc      t;;
 2: len 7; hex 81000000b00110; asc        ;;
 3: len 6; hex e69db0e4bca6; asc       ;;
 4: len 4; hex 80000014; asc     ;;
 5: len 6; hex e6b7b1e59cb3; asc       ;;

从上面日志可以看出,存在两个事务,分别在执行这两条sql时发生了死锁:

select * from test_lock where id = 1  for update
select * from test_lock where id = 5  for update

死锁的本质原因还是由加锁顺序不同所导致,如上面事务1先给id=1加锁,事务2给id=5加锁,然后事务1再给id=5加锁,事务2再给id=1加锁。

死锁记录只记录最近一个死锁信息,若要将每个死锁信息都保存到错误日志,启用以下参数

show variables like 'innodb_print_all_deadlocks';

查当前正在运行的InnoDB事务的信息,可以kill长期占用锁的事务对应的线程id

select * from information_schema.INNODB_TRX;

解决死锁

  1. 可以设置innodb_deadlock_detect=on 来开启死锁检测。死锁检测在发生死锁的时候,能够快速发现并进行处理,回滚并重新启动。但是死锁检测会比较耗资源。
show VARIABLES like  'innodb_deadlock_detect' -- 查看当前死锁检测是否开启
set global innodb_deadlock_detect = OFF; --设置死锁检测关闭
set global innodb_deadlock_detect = ON; --设置死锁检测开启
  1. 保证资源的加锁顺序,避免循环等待的产生。
  2. 减少对资源的占用时间和占用范围,避免长事务,锁粒度变大的情况,可以大大减少死锁产生的概率。
  3. 使用乐观锁mvcc机制,读取数据不上锁,在读情况下共享资源。
一安未来
致力于Java,大数据;心得交流,技术分享;
100篇原创内容

如果这篇文章对你有所帮助,或者有所启发的话,帮忙 分享、收藏、点赞、在看,你的支持就是我坚持下去的最大动力!

图片

基于Shrio的分布式微服务权限控制和会话管理的详细设计与实现


面试官:你工作中做过 JVM 调优吗?怎么做的?


OpenSSL生成根证书CA及签发子证书

图片

收录于合集 #干货分享集
 115
上一篇SpringBoot 这样做参数校验才足够优雅,彻底告别 if-else下一篇Java泛型中的T、E、K、V、?通配符,你确定都了解吗?
阅读 742
一安未来
100篇原创内容
 
posted @ 2023-06-25 10:29  往事已成昨天  阅读(1896)  评论(0编辑  收藏  举报