How to do it...

How it works...

If the Aria storage engine attempts to create a lock and is unable to do so, the possibility of having a deadlock exists. We only want to kill actual deadlocks and not a situation that will resolve itself in an amount of time for which we are comfortable waiting.

To detect deadlocks, whenever Aria cannot create a lock on a table, it first creates a wait-for graph of the possible deadlock with a search depth equal to deadlock_search_depth_short. If, after the search, the lock on the table still exists and Aria cannot determine if it is a deadlock, it will wait for the number of microseconds defined by the value of deadlock_timeout_short and then try again. If it is still unsuccessful, Aria will create a wait-for graph with a search depth equal to the value of deadlock_search_depth_long, and if a deadlock has still not been identified, Aria will wait for the number of microseconds defined by the value of deadlock_timeout_long and then time out with an error.

If a deadlock is detected at any point during the previous steps, Aria will determine the thread responsible for it and kill it, thereby releasing the deadlock and allowing a lock to be made and released as normal.

There's more...

It's important to remember that the deadlock_timeout_short and deadlock_timeout_long variables are defined in microseconds, not milliseconds or seconds. So a value of 10000000 is equal to ten seconds and a value of 5000 is equal to five-thousandths of a second.

For many users, the default timeout values of 50000000 (50 seconds) for the long timeout and 10000 (one-hundredth of a second) for the short timeout are perfectly adequate. The same is also true for the default values of the search depth variables. That said, it might be useful to experiment with different values if we're experiencing a lot of timeouts.

See also