由并发INSERT和SELECT引起的MySQL死锁

本文在这里给出了有关锁定和隔离级别一个详尽的解释.

感谢@newtover提供有关隔离级别的线索.我对该文章的总结和我自己的问题的答案如下:

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

在我的情况下,唯一的索引是PRIMARY,在我的SELECT查询中无用(可以通过验证explain select...).结果,索引中的所有条目PRIMARY都被锁定.当TXN_2等待某个条目上的X锁定时,该条目被保留的S锁定锁定TXN_1.类似地,TXN_1等待另一个条目上的X锁定,但该条目也被自己保留的S锁定锁定.发生了"一个S两X"的死锁.

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

列上的索引name不仅加快了查询速度,更重要的是阻止了锁定索引的所有条目.

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

这是相当低效的.让我们清理它以加快速度,减少死锁的可能性.

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

ts < NOW() - INTERVAL 1 HOUR

你的意外截断; 我的说法是"比1小时前更早",我怀疑你想要的.

现在我们可以索引ts到良好的效果.但是,让我们通过使用"复合"索引进一步实现它:

INDEX(name, ts)

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

你说COUNT(id)- 这意味着你需要避免NULLs进入id.也许这不是一个问题,你可以简单地说COUNT(*).

那些应该SELECT更快.现在让我们弄清楚为什么SELECT和INSERT彼此有任何关系.他们在同一笔交易中吗？或者你有自动提交关闭,但忘了说COMMIT？请向我们展示整个交易,以及SHOW CREATE TABLE.

本文在这里给出了有关锁定和隔离级别一个详尽的解释.

感谢@newtover提供有关隔离级别的线索.我对该文章的总结和我自己的问题的答案如下:

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

在我的情况下,唯一的索引是PRIMARY,在我的SELECT查询中无用(可以通过验证explain select...).结果,索引中的所有条目PRIMARY都被锁定.当TXN_2等待某个条目上的X锁定时,该条目被保留的S锁定锁定TXN_1.类似地,TXN_1等待另一个条目上的X锁定,但该条目也被自己保留的S锁定锁定.发生了"一个S两X"的死锁.

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

列上的索引name不仅加快了查询速度,更重要的是阻止了锁定索引的所有条目.

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