mysql超时 写入失败,java.sql.SQLException：超出锁定等待超时;尝试在MYSQL中重启事务异常...

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)

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

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 .

解决方案

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 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).

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.

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).

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.