[转]一次Delete&Insert引发的Mysql死锁
近日遇到一个比较奇怪的deadlock错误, 错误详情:
Deadlock found when trying to get lock; try restarting transaction; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException...
跟踪代码后最终定位到一段业务逻辑:
delete from A where no = $no;
insert into A(no, value) values($no, "value");
印象中mysql一直是使用行级锁, 为什么此处在并发时会发生死锁呢? 唯一的解释是mysql在这边锁住的不只一行数据.
简单搜索之后, 发现mysql的锁分为三种(按照锁定的行数划分):
1.record lock:记录锁,也就是仅仅锁着单独的一行
2.gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就 是不包括边界值,至于为什么这么定义?innodb官方定义的)
3.next-key lock:record lock+gap lock,所以next-key lock也就半开半闭区间,且是下界开,上界闭。(为什么这么定义?innodb官方定义的)
由于此处是在明确指定了no=XX的情况下抛出了死锁异常, 并且no建立的是普通索引, 所以此处mysql使用的应该是next-key lock(查看何种情况下使用何种锁 https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html).
下面来举个手册上的例子看看next-key lock是如何上锁的。假如一个索引的行有10,11,13,20
那么可能的next-key lock的包括:
(无穷小, 10]
(10,11]
(11,13]
(13,20]
(20, 无穷大)
下面分析何种情况下会发生死锁.
结合业务逻辑, 执行新增操作时也会执行一样的逻辑, 先进行delete.
例如,现在表student中有四条数据:
现在要新增一条数据, no = 21, 这时候会先进行delete, 线程会锁住(20, 无穷大)这块区间, 加入这个时候另一个线程正在新增另一条数据 no = 22, 线程也会锁住(20, 无穷大)这块区间就会发生死锁.
下面看具体实验:
创建一张表student.
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_no` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1
插入数据:
INSERT INTO student (no,name) VALUES(10, "Jim");
INSERT INTO student (no,name) VALUES(11, "Kimi");
INSERT INTO student (no,name) VALUES(13, "Tom");
INSERT INTO student (no,name) VALUES(20, "Mike");
执行两个事务:
session 1:
begin;
delete from student where no = 21;
session 2:
begin;
delete from student where no = 22;
此处解释一下, 此时,session 1和session 2都会对区间(20, 无穷大)加锁, 而区间锁只是用来防止其他事务在区间中插入数据,区间x锁 与区间S锁效果是一样的(只要不是插入操作), 因此两个session都会持有锁.
参考:https://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html
Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. Thus, a gap X-lock has the same effect as a gap S-lock.
继续执行:
session 1:
INSERT INTO student (no,name) VALUES(21, "Zhoubing");
此时session 1阻塞(因为session 2持有区间锁), 如图:
session 2:
INSERT INTO student (no,name) VALUES(22, "Zhoubing");
此时session 2死锁(因为session 1持有区间锁), 如图:
.
总结, delete之后进行insert有可能发生死锁, 因为delete可能会持有区间锁, 而区间锁是可重入的(只要不是插入数据).
解决方案:
将事务隔离级别将为read commit.
相关文章
- MYSQL 函数 字符串到整数
- MySQL使用explain时各字段解释
- 【Mysql 学习】mysql 字符集
- 【Python3网络爬虫开发实战】1.4.1-MySQL的安装
- php分享二十七:批量插入mysql
- mysql_use_result & mysql_store_result & MYSQLI_ASYNC
- php对比辨析之 mysql_escape_string & mysql_real_escape_string & addsalshes
- 一天一个mysql函数(一) cast && convert
- 【MySQL】悲观锁&乐观锁
- thinkphp6:访问多个mysql数据源(thinkphp6.0.5 / php 7.4.9)
- Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- mysql全局权限账户%登录不上ERROR 1045 (28000): Access denied for user 'mhz'@'localhost' (using password: YES)
- Mysql 之 添加innodb支持
- linux 卸载mysql
- 【MySQL 提高】1. MySQL优化的总览, 数据库的基本原理和3NF的概念
- MySQL 视图(详解)& navicat如何创建视图
- MySQL数据库的数据类型以及取值范围详解
- MyBatis JdbcType 与Oracle、MySql数据类型对应关系详解
- sql中datetime日期类型字段比较(mysql&oracle)
- 这项评测,华为云GaussDB(for MySQL)顺利通过
- Python编程:pandas读写mysql数据
- MySQL 聚簇索引和非聚簇索引 & mysql 索引为啥用b 树
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket
- MySQL——修改root密码的4种方法(以windows为例)
- mysql 启动 && 停止
- Mysql中的auto_increment关键字详解
- CentOS 6.8 每天自动备份 MySQL