2

I would like to disable InnoDB dead-lock detection and automatic rollback, as per the requirement we like to rely on innodb_lock_wait_timeout (because of heavy concurrency).

The lock wait timeout value does not apply to deadlocks when innodb_deadlock_detect is enabled (the default) because InnoDB detects deadlocks immediately and rolls back one of the deadlocked transactions. When innodb_deadlock_detect is disabled, InnoDB relies on innodb_lock_wait_timeout for transaction rollback [...]

How can we achieve the above in MySQL 5.7.13? I couldn't find any variable innodb_deadlock_detect.

2 Answers 2

2

Yes, you can do it. Be sure to keep innodb_lock_wait_timeout reasonably low, for example, 2 seconds. Deadlocks that last for 2 seconds are completely harmless, if they are rare (and usually they are very rare). Remind developers to avoid long transactions, and avoid foreign keys ON ... CASCADE (because they propagate locks, making deadlocks more likely).

You can even set innodb_lock_wait_timeout to 0. In that case, deadlocks are impossible, because a transaction will never wait to acquire a lock. But transactions will probably fail often with a timeout error, so your application needs to be able to handle the error and replay the transaction. Even so, such solution could be harmful because it might lead to a crazy number of transaction failures and retries.

So, while I wrote some generic hints, they might not be good for your specific case. Please test carefully changes like this, with a realistic multi-threaded workload.

0

Even if it is possible, it is probably not wise.

Every time a would-be deadlock occurs, you are turning it into a connection that sits around taking up some resources for 50 seconds. With your "heavy concurrency", this might lead to "running out of connections". At that point, no one is getting anything done.

Instead, let's discuss one of your common transactions that sometimes deadlocks. (You can probably catch one via SHOW ENGINE=INNODB STATUS;.) The may be simple ways to cut down on the frequency of deadlocking and/or speed up the transaction. Or maybe combine multiple transactions into one, thereby diminishing the overhead.

Meanwhile, I assume this is what you are referring to?

----- 2016-09-12 8.0.0 Development Milestone & 2016-09-06 5.7.15 General Availability -- Functionality Added or Changed -- InnoDB -----

A new dynamic configuration option, innodb_deadlock_detect, can be used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. (Bug #23477773)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.