首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >并发插入和选择导致的MySQL死锁

并发插入和选择导致的MySQL死锁
EN

Stack Overflow用户
提问于 2015-12-23 22:49:39
回答 5查看 9.5K关注 0票数 16
  • MySQL版本: 5.6
  • 存储引擎: InnoDB

当两个任务尝试select,然后对同一个表进行insert时,就会发生死锁。该程序看起来如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
          Task_1       Task_2
          ------      ------
Phase 1 | SELECT      SELECT
Phase 2 | INSERT      INSERT

SELECT count(id) from mytbl where name = 'someValue' and timestampdiff(hour, ts, now()) < 1;
INSERT mytbl (id, name, ts) values ('newId', 'anotherValue', now());

死锁日志如下(一些细节被截断):

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
------------------------
LATEST DETECTED DEADLOCK
------------------------
151225  8:22:17
*** (1) TRANSACTION:
TRANSACTION 0 746402, ACTIVE 0 sec, process no 4690, OS thread id 140411390486272 inserting
mysql tables in use 1, locked 1
LOCK WAIT 1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3909, query id 31751474 10.20.36.38 mydb update
INSERT INTO mytbl -- truncated
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`mytbl` trx id 0 746402 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 0 746449, ACTIVE 0 sec, process no 4690, OS thread id 140411389953792 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1172 lock struct(s), heap size 112624, 32914 row lock(s)
MySQL thread id 3906, query id 31751477 10.20.36.38 mydb update
INSERT INTO mytbl  -- truncated
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5044 n bits 88 index `PRIMARY` of table `MYDB`.`MYTBL` trx id 0 746449 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

问题

  1. 根据MySQL手册,简单的SELECT语句使用不需要S锁的快照读取。INSERT语句要求插入单个行的X锁。那么,为什么Task_2持有S锁并导致死锁?

编辑

SHOW CREATE TABLE的结果如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
| task_content | CREATE TABLE `mytbl` (
`id` bigint(20) NOT NULL,
`ts` timestamp NULL DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
EN

回答 5

Stack Overflow用户

回答已采纳

发布于 2015-12-26 00:08:51

如果您当前的隔离级别是repeatable read或更强,要想在事务中对select count(id) ...重复相同的结果,MySQL必须锁定整个主键(或WHERE条件使用的另一个键的一部分)。然后,通过插入一个新值来修改密钥。但是并发事务修改了密钥的状态,这已经被看到了。两种方法都可以从相同的键状态开始,然后等待另一种状态在没有更改的情况下完成,这样它就可以应用自己的更改。

票数 7
EN

Stack Overflow用户

发布于 2015-12-28 01:00:22

这里一文详尽地解释了锁和隔离级别。

感谢@newtover给出了关于隔离级别的线索。我对这篇文章的总结和对我自己问题的答复如下:

InnoDB中的默认隔离级别是可重复读取,这将锁定索引(而不是锁定数据表),直到事务结束为止。

在我的情况下,唯一的索引是PRIMARY,它在我的SELECT查询中毫无用处(可以由explain select...验证)。因此,PRIMARY索引中的所有条目都被锁定。当TXN_2在某个条目上等待X锁时,该条目被TXN_1保留的S锁锁定。类似地,TXN_1在另一个条目上等待一个X锁,但是该条目也被自己保留的S锁锁定。出现了“一S二X”死锁。

相反,在我在列name上创建索引name之后,索引name将在SELECT语句中使用(可以由explain select ...验证),因此将在索引name上而不是PRIMARY上发出锁。更重要的是,SELECT语句只对等于someValue的条目发出S锁,而不是索引name的所有条目。此外,INSERT所需的IX锁和X锁将在索引PRIMARY上发出。S锁和IX锁之间的冲突,X锁将得到解决。

name上的索引不仅加快了查询速度,而且更重要的是防止锁定索引的所有条目。

票数 11
EN

Stack Overflow用户

发布于 2015-12-26 11:20:45

其中name = 'someValue‘和timestampdiff(小时,ts,now()) < 1;

这是相当低效的。让我们清理一下,以加快速度,减少出现僵局的可能性。

timestampdiff(hour, ts, now()) < 1ts隐藏任何索引;让我们重写它

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
ts < NOW() - INTERVAL 1 HOUR

你的密码以意想不到的方式被截断;我的邮件里写着“1小时前”,我怀疑你想要这样做。

现在我们可以对ts进行索引,取得了良好的效果。但是,让我们进一步使用“复合”索引:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
INDEX(name, ts)

这将有效地使用WHERE子句的两个部分来定位行。

您说的是COUNT(id) --这意味着您需要避免在id中使用NULLs。也许这不是一个问题,您可以简单地说是COUNT(*)

这应该会使SELECT更快。现在,让我们弄清楚为什么SELECTINSERT之间有任何关系。他们在同一笔交易中吗?或者您是否关闭了自动提交,但忘记说COMMIT?请向我们展示整个交易,加上SHOW CREATE TABLE

票数 8
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/34448539

复制
相关文章
并发replace操作导致的死锁问题
今天上班的时候,遇到了一个问题,有业务同学反应使用并发replace操作的时候,遇到了死锁的问题。针对这个问题,我看了看表的结构,发现表中有一个主键,一个唯一索引,然后用replace的操作去对表中的记录进行插入,如果存在相同的唯一索引,那么就更新这条记录。
AsiaYe
2020/01/15
5.2K0
并发replace操作导致的死锁问题
并发replace操作导致的死锁问题
批量对一张表进行replace into操作,每个SQL操作1000条数据,最近有同事反馈使用并发replace操作的时候,遇到了死锁的问题。针对这个问题,我看了看表的结构,发现表中有一个主键,一个唯一索引,然后用replace的操作去对表中的记录进行插入,如果存在相同的唯一索引,那么就更新这条记录。
翎野君
2023/05/12
5480
并发replace操作导致的死锁问题
【MySQL】加了什么锁,导致死锁的?
最近在看 小林coding 的文章,看到一篇《字节面试:加了什么锁,导致死锁的?》,自己也跟着做了做,题目如下图:
sidiot
2023/08/31
3100
【MySQL】加了什么锁,导致死锁的?
Mysql 并发引起的死锁问题
本文讲述了一个基于Discuz的MySQL云数据库搬迁实例,分析了在搬迁过程中出现的死锁问题和性能瓶颈,并给出了相应的优化方案。通过优化表结构和采用分块传输,可以有效提高数据库的搬迁效率,降低死锁风险。
邵梦超
2017/04/26
7.7K1
Mysql 并发引起的死锁问题
一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识
最近遇到一个由于唯一性索引,导致并发插入产生死锁的场景,在分析死锁产生的原因时,发现这一块还挺有意思的,涉及到MySql中不少的知识点,特此总结记录一下。
Java程序猿阿谷
2021/03/02
5.8K0
一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识
大招落地:MySQL 插入更新死锁源码分析
读者反馈了一个死锁案例,比较有意思,上一篇文章讲了怎么通过调试源码来分析锁,今天再来分析一个死锁场景。
挖坑的张师傅
2022/05/13
7390
大招落地:MySQL 插入更新死锁源码分析
fork导致的死锁问题
先看一个示例程序,该程序有个全局对象sGlobalInstance,父进程先通过该对象执行了lock操作,然后执行fork,在子进程中,也去执行lock操作。可以先思考一下这个程序有没有问题。
coderhuo
2023/10/21
6220
Java并发-死锁
 所谓死锁是指两个或两个以上的线程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无其余方法作用,它们都将无法推进下去。  网友们有一个生动形象的比方:两个人面对面过独木桥,甲和乙都已经在桥上走了一段距离,即占用了桥的资源,甲如果想通过独木桥的话,乙必须退出桥面让出桥的资源,让甲通过,但是乙不服,为什么让我先退出去,我还想先过去呢,于是就僵持不下,导致谁也过不了桥,这就是死锁。其实死锁形成的关键就是:谁也不让谁,谁都不会主动地让步。
Fisherman渔夫
2020/02/18
7790
Java并发-死锁
MySQL 加锁和死锁解析
本文大多数都整理自《死锁-何登成 - 管中窥豹——MySQL(InnoDB)死锁分析之道》
星哥玩云
2022/08/18
9970
MySQL 加锁和死锁解析
MySQL相关 – 死锁的发生和避免
在我们使用锁的时候,有一个问题是需要注意和避免的,我们知道,排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候,会阻止其他的线程获取锁,这个时候会造成阻塞等待,如果循环等待,会有可能造成死锁。
全栈程序员站长
2022/07/19
8910
MySQL相关 – 死锁的发生和避免
【MySQL】mysql死锁以及死锁日志分析
死锁:死锁一般是事务相互等待对方资源,最后形成环路造成的。 对于死锁,数据库处理方法:牺牲一个连接,保证另外一个连接成功执行。 发生死锁会返回ERROR:1213 错误提示,大部分的死锁InnoDB存储引擎本身可以侦测到,不需要人为进行干预。 注意: InnoDB存储引擎并不会回滚大部分的错误异常,像阻塞章节里面的例子,但是死锁例外,发现死锁后,InnoDB存储引擎会马上回滚一个事务,会返回1213错误。
用户5522200
2019/06/02
3.7K0
(十五)Java并发性和多线程-死锁
死锁是两个或更多线程阻塞着等待其它处于死锁状态的线程所持有的锁。死锁通常发生在多个线程同时但以不同的顺序请求同一组锁的时候。
用户1212940
2022/04/13
2490
MySQL如何加锁避免并发事务导致的脏写?
多个事务同时并发更新一行数据时, 就有脏写问题。脏写绝对不允许,可依靠锁机制让多个事务更新一行数据的时候串行化,避免同时更新一行数据。
JavaEdge
2022/02/17
1.4K0
MySQL如何加锁避免并发事务导致的脏写?
由于不当的执行顺序导致的死锁
为了保证线程的安全,我们引入了加锁机制,但是如果不加限制的使用加锁,就有可能会导致顺序死锁(Lock-Ordering Deadlock)。
程序那些事
2020/07/08
4290
使用mysql事务不同场景导致的死锁问题以及解决方法
MySQL各存储引擎使用了三种级别的锁定机制:table-level(表级锁定),row-level(行级锁定)和page-level(页级锁定)此处只介绍使用InnoDB存储引擎行过程中经常常遇到的问题以及解决方法。
云上冲浪者
2018/08/09
2K0
MySql 死锁
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务视图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源,也会产生死锁。
赵哥窟
2019/03/06
1.3K0
DllMain中不当操作导致死锁问题的分析--死锁介绍
        最近在网上看到一些关于在DllMain中不当操作导致死锁的问题,也没找到比较确切的解答,这极大吸引了我研究这个问题的兴趣。我花了一点时间研究了下,正好也趁机研究了下进程对DllMain的调用规律。因为整个研究篇幅比较长,我觉得还是分开写比较能突出重点。本文先说说死锁。(转载请指明出于breaksoftware的csdn博客)
方亮
2019/01/16
8840
DllMain中不当操作导致死锁问题的分析--导致DllMain中死锁的关键隐藏因子2
        本文介绍使用Windbg去验证《DllMain中不当操作导致死锁问题的分析--导致DllMain中死锁的关键隐藏因子》中的结论,调试对象是文中刚开始那个例子。(转载请指明出于breaksoftware的csdn博客)
方亮
2019/01/16
7380
DllMain中不当操作导致死锁问题的分析--导致DllMain中死锁的关键隐藏因子
        有了前面两节的基础,我们现在切入正题:研究下DllMain为什么会因为不当操作导致死锁的问题。首先我们看一段比较经典的“DllMain中死锁”代码。(转载请指明出于breaksoftware的csdn博客)
方亮
2019/01/16
1.5K0
#算法基础#选择和插入排序
算法是基础,小蓝同学准备些总结一系列算法分享给大家,这是第二篇《选择和插入排序》,非常赞!希望对大家有帮助,大家会喜欢! 系列文章: 由快速排序到分治思想 一、选择排序 这是一种最简单的排序算法 第一步他先找到数组中最小的元素,然后将它和本数组中第一个元素交换位置。然后把剩下的n-1个数算为一个数组。继续找到其中最小的 放到第二个位置。以此往复。于是恭喜你得到了大奖,一个有序的数组。哈哈 话不多说 看代码 : public static void sort(Comparable[] a)
大数据和云计算技术
2018/03/08
7270

相似问题

MySQL并发插入导致(显式)事务外的死锁

22

Mysql并发选择/插入

20

导致死锁的行上的Mysql并发更新

13

mysql insert...select语句导致并发请求中的死锁

10

插入和更新的Mysql死锁

23
添加站长 进交流群

领取专属 10元无门槛券

AI混元助手 在线答疑

扫码加入开发者社群
关注 腾讯云开发者公众号

洞察 腾讯核心技术

剖析业界实践案例

扫码关注腾讯云开发者公众号
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
查看详情【社区公告】 技术创作特训营有奖征文