type
status
date
slug
summary
tags
category
icon
password
example-row
example-row
记录一次insert on duplicate语句引起的死锁问题。

环境

mysql8.0.13

场景复现

建立一张表user_info,这张表中存在字段id、no、name,其中id为自增主键,no字段为唯一索引。
  1. 创建一个数据库连接(session1),执行语句begin;开启事务。
 
  1. 在session1中执行语句
  1. 再创建一个数据库连接(session2),执行语句
此时语句会卡主等待。
  1. 再创建一个数据库连接(session3),执行语句
此时语句同样会卡主等待。
  1. session1执行语句commit;将事务进行提交。此时session3或session2会造成死锁报错。

死锁原因

  1. 在session1执行语句
后,数据库会加上两把gap锁。
  1. 当session2执行语句
后,数据库会加上两把gap锁,但其中一把gap锁会有一个INSERT_INTENTION 标识,并且状态为WAITING
  1. session3中执行语句的效果同session2。
  1. 当session1中执行commit;语句提交事务时,此时session1中的锁会得到释放。session2的gap锁变成了GRANTED 状态,session2中要去插入时,会去检测事务,然后发现session3中存在一个gap锁,所以又会给自己加一个WAITING状态gap锁进行等待。然后session3中的WAITING状态的gap锁是等待session2的事务的,这样就造成了session2和session3中的gap锁循环了,变成了死锁。

解决方案

在java代码上使用try catch语句,try一下insert语句,catch到duplicate key 报错时,再执行update语句。
不过在8.4的版本上,在insert语义下把gap锁去掉了,所以在8.4的版本上这个问题没有得到复现。
 
【mysql优化002】深分页下慢sql处理【blog001】Windows下hexo+github
  • Twikoo