超过了MySQL Lock等待超时 [英] MySQL Lock wait timeout exceeded

问题描述

我收到错误Lock wait timeout exceeded; try restarting transaction.这是什么原因以及如何解决该问题?仅供参考:在MySQL配置文件中为innodb_lock_wait_timeout = 100.

I have got the error Lock wait timeout exceeded; try restarting transaction. What are the reasons for this and how to solve the problem? FYI: innodb_lock_wait_timeout = 100 in MySQL config file.

推荐答案

这是锁争用的问题，最终导致其中一个锁超时.这里有一些建议:

This is problem of lock contention, which ultimately result in a time-out on one of the lock. Here are a few suggestions:

• 确保您具有正确的索引，这些索引会导致行级锁而不是表级锁.这将减少争用.
• 确保您在外键约束上具有索引.为了检查insert或update期间的关系约束，如果没有这样的索引，某些数据库会锁定整个引用表(不知道这是否是MySQL的情况)
• 如果问题仍然存在，请尝试使交易更快/更小.同样，这将减少对数据库的争用.
• 增加超时时间，但将值保持合理

• Make sure you have the correct indexes which result in row-level locks not table-level lock. This will reduce the contention.
• Make sure you have indexes on the foreign key constraints. To check the relational constraints during insert or update, some database lock the whole referenced table if there is no such index (don't know if this is the case of MySQL)
• If problem is still here, try to make the transaction faster/smaller. Again, this will reduce the contention on the database.
• Increase the timeout but keep the value reasonable

这篇关于超过了MySQL Lock等待超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！