1

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.

1

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

Of course I am assuming that you can keep a very low value for those timeouts. But can you? I don't know your workload but, for example, a statement may lock some rows for 10 seconds every hour. In such cases, can you afford a low timeout? If not, then everything I wrote above does not apply to you.

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, privacy policy and cookie policy

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