In 7.2.16+, 7.3.5+, and 7.4+ With Small Values of TransactionDeadlockDetectionTimeout. "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"
Last updated on MARCH 08, 2017
Applies to:MySQL Cluster - Version 7.2 and later
Information in this document applies to any platform.
In 7.2.16+, 7.3.5+, and 7.4+ with small values of TransactionDeadlockDetectionTimeout (typically less than 250 milliseconds), queries may encounter the error:
even if there is no lock waits or deadlocks.
The smaller the value of TransactionDeadlockDetectionTimeout the more likely to encounter the issue. For one test the following was found when execute a SELECT * FROM t1 WHERE val > ... (should return 0 rows) on a table with many (but no blob or text columns) and around 9 million rows
- TransactionDeadlockDetectionTimeout = 100 ms: All attempts failed with a lock wait time exceeded.
- TransactionDeadlockDetectionTimeout = 200 ms: Around 50% of the queries failed.
- TransactionDeadlockDetectionTimeout = 250 ms: All queries worked.
The issue may start after one of the following changes:
- Upgrading to 7.2.16+, 7.3.5+, or 7.4+.
- Decreasing the value of TransactionDeadlockDetectionTimeout.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms