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

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

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

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

代码语言:javascript
代码运行次数:0
运行
复制
          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
运行
复制
------------------------
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
运行
复制
| 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 08:08:51

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

票数 7
EN

Stack Overflow用户

发布于 2015-12-28 09: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 19:20:45

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

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

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

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

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

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

代码语言:javascript
代码运行次数:0
运行
复制
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

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档