java.sql.SQLException：超出锁定等待超时;尝试在MYSQL中重启事务异常 [英] java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction exception occur in MYSQL

问题描述

我在 MYSQL 中使用一个表名test，它在事务T1期间锁定，将在20分钟内完成。当我在这20分钟内通过另一个事务T2更新此表时。我得到一个例外： -

I am Using a table name "test" in MYSQL that is lock during a transaction T1 that will be complete with in 20 minutes. When I am updating this table by another transaction T2 within these 20 minutes . I am getting an exception:-

11:58:38,584 ERROR [STDERR] java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
11:58:38,584 ERROR [STDERR]     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
11:58:38,584 ERROR [STDERR]     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
11:58:38,584 ERROR [STDERR]     at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1124)
11:58:38,584 ERROR [STDERR]     at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:676)

<请给我一个解决方案，如何在不得到此异常的情况下执行此事务T2？

Please provide me the solution that how can I do this transaction T2 without getting this exception?

在mysql数据库中更新 innodb_lock_wait_timeout 的值是否正确，以解决此异常。我期待着为这个问题找到任何有用的解决方案。

Is it right to update the value of innodb_lock_wait_timeout in mysql database for solve this exception. I look forward for getting any useful solution for this problem .

推荐答案

更新innodb_lock_wait_timeout的值是不是解决此问题的正确方法。对于初学者来说，你听起来需要将它更新到20分钟，这将是荒谬的。

Updating the value of innodb_lock_wait_timeout is not the right way to solve this problem. For starters, you it sounds like you would need to update it to 20 minutes, which would be ridiculous.

innodb_lock_wait_timeout的默认值为50秒 - 这是在放弃之前，时间T2将等待访问由T1锁定的表（并导致您看到的异常）。

innodb_lock_wait_timeout has a default of 50 seconds - this is the length of time T2 will wait for access to a table locked by T1 before giving up (and resulting in the exception you are seeing).

您的T2交易在做什么？如果它只执行读取（即不写入表test），那么可以将数据库的隔离级别更改为read uncommitted，以便T2可以读取未提交的数据。但是，IMO这是你应该避免的黑客攻击。

What is your T2 transaction doing? If it is performing reads only (i.e. not writing to your table "test") then you could change the database's isolation level to "read uncommitted" so that T2 can read the uncommitted data. However, IMO this is a hack you should avoid.

相反，你应该考虑你的设计/实现。要让一个打开并持有行锁20分钟的事务在多线程环境（例如webapp）中遇到麻烦。

Instead, you should consider your design/implementation. To have a transaction that is open and holding a row lock for 20 minutes is asking for trouble in a multi-threaded environment (such as a webapp).

您的归档是否存档活动（需要20分钟）必须在一次交易中？解决这个问题的一个显而易见的方法是在每个语句之后提交或将其分解为更合理大小的事务。

Does your archiving activity (which takes 20 minutes) have to be in one transaction? An obvious way to solve this problem would be to commit after every statement or to break it into more reasonably sized transactions.

这篇关于java.sql.SQLException：超出锁定等待超时;尝试在MYSQL中重启事务异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！