rc 和rr 模式下的死锁问题

想网名太麻烦

已于 2022-02-24 13:56:40 修改

阅读量1.6k

点赞数

文章标签： sql 开发语言数据库锁

于 2021-12-27 23:07:15 首次发布

本文链接：https://blog.csdn.net/lhz121/article/details/122182000

版权

博客讨论了线上项目中出现的死锁问题，原因是更新无索引的表导致的全表锁。在Repeatable Read (RR) 隔离级别下，未加索引的更新操作会引发死锁。解决方案包括使用主键更新或降低隔离级别到Read Committed (RC)，并建议通过添加索引或调整隔离级别来避免死锁。实验证明，RC级别下仅锁一行记录，而RR级别下可能锁全表。

摘要由CSDN通过智能技术生成

问题描述:线上项目高峰期突然出现死锁问题!用户操作出现卡顿,后台直接抛出deadlock

查看日志发现是在update表的时候出现DeadlockLoserDataAccessException异常 (Deadlock found when trying to get lock; try restarting transaction...)。

问题分析:

查看日志是更新数据时抛出的异常(所以考虑的时锁竞争引发的锁等待超时回滚),因为是老版本维护的人比较多,查看sql语句发现没有加索引,事务隔离级别为rr,至此问题解决(rr 隔离级别下,锁机制未加索引,全表扫描,走的是netkey 机制退化成全表锁,锁住全表(排他锁),rc 隔离级别下,全表扫描匹配定位到匹配行,释放排他锁,只锁当前行)

解决方式:rr 模式我们可以先查出所要修改的那条记录, 使用主键id 更新虽然回表操作但是不会导致死锁问题,或者直接启用rc 隔离级别

解决方案 :给sql 语句加上索引,或者事务隔离级别设为rc(建议设为rc,虽然可能会产生幻读和不可重复读但是我们可以业务上避免或者手动加锁)

后文: 一直说更新语句如果不走索引锁的是全表,为什么是博主说是锁rc模式下锁的是一行记录,博主也很奇怪,偶然间看过这么一句话(RC和RU级别下不用去确保多次读取数据的一致性, 就不会有间隙锁(防止出现幻读),以及行锁升级表锁)

看实测把

创建一个学生表

#创建学生表
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `s_name` varchar(20) NOT NULL DEFAULT '',
  `s_birth` varchar(20) NOT NULL DEFAULT '',
  `s_sex` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#添加sql语句
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('01', '赵雷', '1990-01-01', '男');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('02', '钱电', '1990-12-21', '男');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('03', '孙风', '1990-05-20', '男');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('04', '李云', '1990-08-06', '男');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('05', '周梅', '1991-12-01', '女');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('06', '吴兰', '1992-03-01', '女');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('07', '郑竹', '1989-07-01', '女');
INSERT INTO `test`.`student`(`s_id`, `s_name`, `s_birth`, `s_sex`) VALUES ('08', '王菊', '1990-01-20', '女');

设置数据库隔离级别

set session transaction isolation level repeatable read;

执行开启事务更新表记录,查看锁住的表记录

set session transaction isolation level read committed;
update student set s_name = '李十三'  where s_name ="王菊"

select * from information_schema.innodb_trx

innodb_trx查看事务锁的状态和事务的隔离级别以及权重和所住的表记录下图可以看到只锁住了一行记录

查看rr隔离级别下未设置索引更新语句

#设置隔离级别
set session transaction isolation level repeatable  read;
开启事务
start TRANSACTION
select * from student 

update student set s_name = '李十三'  where s_name ="王菊"
#查看事务锁状态
 select * from information_schema.innodb_trx
ROLLBACK
COMMIT

由下图看全表都被锁住了