记一次死锁问题

最新推荐文章于 2024-09-14 18:46:05 发布

ByLir

最新推荐文章于 2024-09-14 18:46:05 发布

阅读量542

点赞数

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

本文链接：https://blog.csdn.net/ByLir/article/details/121742017

版权

本文探讨了数据库中因事务更新操作导致的死锁问题。通过一个具体的场景，展示了当事务1按ID更新而事务2按非索引字段更新时，如何引发死锁。分析指出，使用非索引字段更新可能导致全表锁定，进而造成阻塞。建议在更新时尽量依据主键或索引字段，以避免死锁。同时，提供了查询和诊断死锁的SQL语句以及优化措施。

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

场景：有两个事务，都是更新操作，其中一个在循环里面按照id更新，另外一个按照非索引字段更新。
场景再现：
首先有一张user表，表的字段以及内容如下：
user表

name，age，price为非索引字段

首先模拟事务1:在循环中按照id更新

关闭事务自动提交

     set autocommit = 0
  show variables like 'autocommit'
  begin 
  update user set name = 'aojie2' where id = 2
  select * from user

在这里插入图片描述

此时开启事务2

update user set name = 'aojie11' where id = 1
在这里插入图片描述

产生死锁并尝试重新开启事务

模拟事务2：按照非索引字段更新

关闭事务自动提交

 set autocommit = 0
 show variables like 'autocommit'
 begin 
 update user set name = 'aojie1' where price = 11

在这里插入图片描述

此时事务按照非索引字段更新会锁全表，锁住了price为11也就是第一行之后还要继续锁住后面行，但是现在事务1还持有第二行的X锁(排它锁)，所以事务2阻塞了，此时回去继续操作事务1。

分析：首先事务1在更新完第二行之后事务2开始，事务2根据非索引字段更新第一行，锁住第一行之后还想继续锁住后面行，但是事务1还持有第二行的X锁(排它锁)还未提交，所以事务2阻塞，此时事务1更新完第二行之后要更新第一行了，但是事务2持有第一行的X锁还在阻塞未提交。所以就出现一个问题，事务1等待事务2释放第一行的锁，事务2又等待事务1释放第二行的锁，互相等待对方释放锁，从而产生死锁。
建议：在更新时尽量根据主键或者索引字段来更新

#查询系统表中的锁信息
select * from information_schema.INNODB_LOCK_WAITS;
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_TRX;
#发生死锁时查询死锁发生的SQL和时间
show engine innodb status;
#显示当前查询
show full processlist;
#查询innodb的锁等待超时配置
show VARIABLES like ‘%innodb_lock_wait_timeout%’;