InnoDB 死锁，锁定模式 S 和 X
在我的应用程序中，我有两个不时发生的查询（来自不同的进程），这会导致死锁。
查询 #1
UPDATE tblA, tblB SET tblA.varcharfield=tblB.varcharfield WHERE tblA.varcharfield IS NULL AND [a few other conditions];
查询 #2
INSERT INTO tmp_tbl SELECT * FROM tblA WHERE [various conditions];
这两个查询都需要花费大量时间，因为这些表有数百万行。当查询 #2 运行时，tblA 似乎被锁定在模式 S 中。查询 #1 似乎需要 X 锁。由于这与 S 锁不兼容，查询 #1 等待长达 30 秒，此时我遇到了死锁：
序列化失败：1213 尝试获取锁时发现死锁；尝试重新启动事务
- 在 tblA.varcharfield 上设置索引。不幸的是，我认为这需要一个非常大的索引来存储 varchar(512) 字段。 （请参阅下面的编辑...这不起作用。）
- 使用
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 禁用锁定；。我不明白这意味着什么，并且担心数据损坏。我目前不在我的应用程序中使用显式事务，但我可能会在将来的某个时候使用。 - 将我的耗时查询分成小块，以便它们可以在 MySQL 中排队和运行，而不会达到 30 秒超时。这并不能真正解决问题的核心，而且我担心当我的数据库服务器繁忙时问题会再次出现。
- 只是一遍又一遍地重试查询......这不是我所希望的选择。
我应该如何进行？我应该考虑其他方法吗？
编辑： 我尝试在 varcharfield 上设置索引，但表仍然锁定。我怀疑锁定是在 UPDATE 部分实际执行时发生的。还有其他建议可以解决这个问题吗？
In my application, I have two queries that occur from time to time (from different processes), that cause a deadlock.
Query #1
UPDATE tblA, tblB SET tblA.varcharfield=tblB.varcharfield WHERE tblA.varcharfield IS NULL AND [a few other conditions];
Query #2
INSERT INTO tmp_tbl SELECT * FROM tblA WHERE [various conditions];
Both of these queries take a significant time, as these tables have millions of rows. When query #2 is running, it seems that tblA is locked in mode S. It seems that query #1 requires an X lock. Since this is incompatible with an S lock, query #1 waits for up to 30 seconds, at which point I get a deadlock:
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
Based on what I've read in the documentation, I think I have a couple options:
- Set an index on tblA.varcharfield. Unfortunately, I think that this would require a very large index to store the field of varchar(512). (See edit below... this didn't work.)
- Disable locking with
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. I don't understand the implications of this, and am worried about corrupt data. I don't use explicit transactions in my application currently, but I might at some point in the future. - Split my time-consuming queries into small pieces so that they can queue and run in MySQL without reaching the 30-second timeout. This wouldn't really fix the heart of the issue, and I am concerned that when my database servers get busy that the problem will occur again.
- Simply retrying queries over and over again... not an option I am hoping for.
How should I proceed? Are there alternate methods I should consider?
EDIT: I have tried setting an index on varcharfield, but the table is still locking. I suspect that the locking happens when the UPDATE portion is actually executing. Are there other suggestions to get around this problem?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以只索引 varchar 列的一部分，它仍然可以工作，并且需要更少的空间。只需指定索引大小：
You can index only part of the varchar column, it will still work, and will require less space. Just specify index size:
我能够通过在两个查询周围添加显式的 LOCK TABLE 语句来解决该问题。事实证明这是一个更好的解决方案，因为每个查询都会影响如此多的记录，并且这两个都是后台进程。他们现在互相等待。
http://dev.mysql.com/doc/refman/5.0 /en/lock-tables.html
虽然这对我来说是一个不错的解决方案，但它显然不是每个人的答案。使用
WRITE锁定意味着您无法READ。只有READ锁才允许其他人READ。I was able to solve the issue by adding explicit
LOCK TABLEstatements around both queries. This turned out to be a better solution, since each query affects so many records, and that both of these are background processes. They now wait on each other.http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
While this is an okay solution for me, it obviously isn't the answer for everyone. Locking with
WRITEmeans that you cannotREAD. Only aREADlock will allow others toREAD.答：如果我们假设索引
varcharField占用大量磁盘空间，并且添加新列不会对您造成太大影响，我可以建议采用以下方法：varcharField为 null，则该字段将存储 0，否则存储 1。希望有帮助。
A. If we assume that indexing
varcharFieldtakes a lot of disk space and adding new column will not hit you hard I can suggest the following approach:varcharFieldis null and 1 - otherwise.Hope it helps.