浅谈MySQL 有哪些死锁场景

2024-09-29 20:40:17

1 环境准备

use martin;
drop table if exists dl;
CREATE TABLE `dl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_c` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `dl_insert` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`),
unique key `uniq_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into dl(a,b,c) values (1,1,1),(2,2,2);
drop table if exists dl_1;
create table dl_1  like dl;
insert into dl_1 select * from dl;

2 同一张表下的死锁

session1	session2
begin;	begin;
select * from dl where a=1 for update;…1 row in set (0.00 sec)	select * from dl where a=2 for update;…1 row in set (0.00 sec)
select * from dl where a=2 for update;/* SQL1 */（等待）
（session2 提示死锁回滚后，SQL1 成功返回结构）	select * from dl where a=1 for update;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
commit;	commit;

session1 在等待 session2 释放 a=2 的行锁，而 session2 在等待 session1 释放 a=1 的行锁。两个 session 互相等待对方释放资源，就进入了死锁状态。

3 不同表下的死锁

session1	session2
begin;	begin;
select * from dl where a=1 for update; … 1 row in set (0.00 sec)	select * from dl_1 where a=1 for update; … 1 row in set (0.00 sec)
select * from dl_1 where a=1 for update;/* SQL2 */ 等待
（session2 提示死锁回滚后，SQL1 成功返回结构）	select * from dl where a=1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
commit;	commit;

这个实验也是两个 session 互相等待对方释放资源，就进入了死锁状态。

4 间隙锁下的死锁

session1	session2
set session transaction_isolation='REPEATABLE-READ'; /* 设置会话隔离级别为 RR */	set session transaction_isolation='REPEATABLE-READ'; /* 设置会话隔离级别为 RR */
begin;	begin;
select * from dl where a=1 for update; … 1 row in set (0.00 sec)	select * from dl where a=2 for update; … 1 row in set (0.00 sec)
insert into dl(a,b,c) values (2,3,3);/* SQL1 */ 等待
（session2 提示死锁回滚后，SQL1 成功返回结果）	insert into dl(a,b,c) values (1,4,4);/* SQL2 */ ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
commit;	commit;

由于 RR 隔离级别下存在间隙锁，可以知道 SQL1 需要等待 a=2 获得的间隙锁，而 SQL2 需要等待 a=1 获得的间隙锁，两个 session 互相等待对方释放资源，就进入了死锁状态。

5 INSERT 语句的死锁

session1	session2	session3
begin;
insert into dl_insert(a,b,c) value (3,3,3);
	insert into dl_insert(a,b,c) value (3,3,3);/* 等待 */	insert into dl_insert(a,b,c) value (3,3,3);/* 等待 */
rollback;	执行成功	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这里需要注意的是，a 字段有唯一索引。当 session1 执行完 insert 语句，会在索引 a=3 上加记录锁，当 session2 执行同样的 insert 语句时，唯一键冲突，加上读锁；同样 session3 也会加上读锁。

当 session1 回滚，session2 和 session3 都试图继续执行插入操作，都要加上写锁。此时两个 session 都要等待对方的行锁，因此出现了死锁。

一些死锁场景就介绍到这里，当然，也欢迎各位补充其他的一些死锁场景。

到此这篇关于浅谈MySQL 有哪些死锁场景的文章就介绍到这了,更多相关MySQL 死锁场景内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

一个mysql死锁场景实例分析

前言最近遇到一个mysql在RR级别下的死锁问题,感觉有点意思,研究了一下,做个记录. 涉及知识点:共享锁.排他锁.意向锁.间隙锁.插入意向锁.锁等待队列场景隔离级别:Repeatable-Read 表结构如下 create table t ( id int not null primary key AUTO_INCREMENT, a int not null default 0, b varchar(10) not null default '', c varchar(10) not n
浅谈MySQL 有哪些死锁场景

目录 1 环境准备 2 同一张表下的死锁 3 不同表下的死锁 4 间隙锁下的死锁 5 INSERT 语句的死锁首先一起来复习一下死锁的概念:死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象. 下面我们通过几个实验,来验证几种死锁场景. 1 环境准备 use martin; drop table if exists dl; CREATE TABLE `dl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` in
浅谈Mysql insert on duplicate key 死锁问题定位与解决

目录前言死锁定位 insert on duplicate key的锁问题解决前言最近在监测线上日志时发现我们一个Mysql业务db时常出现 dead lock,频次不高但却一直出现,定位后发现是在并发场景下的 insert on duplicate key update sql 出现的死锁.经过分析发现这种sql确实比较容易造成死锁,不太适用于我们目前的业务场景,于是更换后解决问题. 这篇文章就从分析死锁展开,到最终如何解决这样的问题分享相应的思路. 死锁定位我们目前生产环境使用M
浅谈mysql 针对单张表的备份与还原

A.MySQL 备份工具xtrabackup 的安装 1. percona 官方xtrabackup 的二进制版本:二进制版本解压就能用了. 2. 解压xtrabackup & 创建连接 tar -xzvf percona-xtrabackup-2.3.4-Linux-x86_64.tar.gz -C /usr/local/ ln -s /usr/local/percona-xtrabackup-2.3.4 /usr/local/xtrabackup 3. 设置PATH环境变量 export P
浅谈MySQL和Lucene索引的对比分析

MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M
浅谈mysql导出表数据到excel关于datetime的格式问题

最近用mysql导出表数据到excel文件,mysql中的datetime类型导出到excel(excel2016)中被excel识别成它自己默认的日期格式了,在mysql中的格式形如 yyyy-mm-dd hh:mm:ss,到了excel变成了 yyyy/m/d h:mm,看起来不太习惯,当然可以通过设置excel单元格格式改成自定义格式 yyyy-mm-dd hh:mm:ss,但是这样多了一个步骤,能不能直接从mysql导出到excel的就是mysql显示的样式呢?当然可以. 开始猜想是由于
浅谈MySQL大表优化方案

背景阿里云RDS FOR MySQL(MySQL5.7版本)数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务方案概述一.数据库设计及索引优化 MySQL数据库本身高度灵活,造成性能不足,严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率建议字段定义not null,null值很难查询优化且占用额外的索引空间使用TINYINT类
浅谈MySQL中的自增主键用完了怎么办

在面试中,大家应该经历过如下场景面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你:"用的是自增主键" 面试官:"为什么是自增主键?" 你:"因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla-" 面试官:"那自增主键达到最大值了,用完了怎么办?" 你:"what,没复习啊!!" (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得
浅谈MySQL中float、double、decimal三个浮点类型的区别与总结

下表中规划了每个浮点类型的存储大小和范围: 类型大小范围(有符号) 范围(无符号) 用途 ==float== 4 bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度浮点数值 ==double== 8 bytes (-1.797 693 134 862 315 7 E
浅谈MySQL与redis缓存的同步方案

本文介绍MySQL与Redis缓存的同步的两种方案方案1:通过MySQL自动同步刷新Redis,MySQL触发器+UDF函数实现方案2:解析MySQL的binlog实现,将数据库中的数据同步到Redis 一.方案1(UDF) 场景分析:当我们对MySQL数据库进行数据操作时,同时将相应的数据同步到Redis中,同步到Redis之后,查询的操作就从Redis中查找过程大致如下: 在MySQL中对要操作的数据设置触发器Trigger,监听操作客户端(NodeServer)向MySQL中写入数
浅谈MySQL如何优雅的做大表删除

随着时间的推移或者业务量的增长,数据库空间使用率也不断的呈稳定上升状态,当数据库空间将要达到瓶颈的时候,可能我们才会发现数据库有那么一两张的超级大表!他们堆积了从业务开始到现在的全部数据,但是90%的数据都是没有业务价值的,这时候该如何处理这些大表? 既然是没有价值的数据,我们通常一般会选择直接删除或者归档后删除两种,对于数据删除的操作方式来说又可分为两大类: 通过truncate直接删除表中全部数据通过delete删除表中满足条件记录一.Truncate操作从逻辑意义上来讲,trunca