Configuring the Aria two-step deadlock detection_MariaDB Cookbook-QQ阅读女生青春网

上QQ阅读APP看书，第一时间看更新

Configuring the Aria two-step deadlock detection

A deadlock is when there are two competing actions and both are waiting for the other to finish and so neither of them ever finish. The Aria storage engine is able to automatically detect and deal with deadlocks. To make effective use of this feature, we should configure it with the settings that work well for our needs.

How to do it...

Run the following command to show the current settings for Aria's two-step deadlock detection:
```
SHOW VARIABLES LIKE 'deadlock_%'\G
```
If our settings are set to the default values, the output of the previous command will be as shown in the following screenshot:

Change the variables to our desired values, as follows:

SET GLOBAL deadlock_search_depth_short = 3;
SET GLOBAL deadlock_search_depth_long = 10;
SET GLOBAL deadlock_timeout_long = 10000000;
SET GLOBAL deadlock_timeout_short = 5000;

Make the changes permanent by adding the following lines of code to the bottom of our my.cnf or my.ini file (or to an existing [mysqld] section):
```
[mysqld]
deadlock_search_depth_short = 3
deadlock_search_depth_long = 10
deadlock_timeout_long = 10000000
deadlock_timeout_short = 5000
```

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.