In Drupal 10.1.0, the default MySQL transaction isolation level is updated from REPEATABLE-READ to READ-COMMITTED. Although Drupal core supports REPEATABLE-READ, it might cause deadlocks. However, Acquia continued to keep the default MySQL transaction isolation level to REPEATABLE-READ for compliance reasons. If you experience deadlocks when querying semaphore tables in your databases, you can override the MySQL transaction isolation level value from the Acquia default to READ-COMMITTED.
A MySQL deadlock occurs when two processes attempt to update two rows in a database in opposite orders.
For example, consider a scenario where one process attempts to update row 1 and then row 2, and another process attempts to update row 2 and then row 1. In this scenario, both processes wait for the other to finish. This conflict causes the database to halt, potentially leading to data loss.
Deadlock issues are displayed as errors in Drupal watchdog log, PHP error log, Cloud Hooks log, Scheduled Cron Jobs log, and any script that invokes database operations.
The following are the error examples specific to Drupal watchdog log and PHP error log:
PDOException: SQLSTATE[40001]: Serialization failure: 1213
Deadlock found when trying to get lock; try restarting transaction:
DELETE FROM {semaphore} WHERE (value = :db_condition_placeholder_0) ;
Array ( [:db_condition_placeholder_0] => 1206160266554d211f589b74.42438644 )
in lock_release_all() (line 269 of
/mnt/www/html/mysite/docroot/includes/lock.inc)..To view the tables having deadlock issues, run the following command from the location of your drupal-watchdog.log file:
$ zgrep 'try restarting transaction' drupal-watchdog.log | grep -o 'try
restarting transaction: [A-Z ]* {[a-z_0-9]*}' | sort | uniq -c | sort -nrThis command might display the following output:
169 try restarting transaction: UPDATE {variable}
12 try restarting transaction: INSERT INTO {field_data_commerce_line_items}
11 try restarting transaction: UPDATE {cache_update}
9 try restarting transaction: UPDATE {job_schedule}
9 try restarting transaction: UPDATE {field_config}
9 try restarting transaction: DELETE FROM {history}
9 try restarting transaction: DELETE FROM {feeds_log}
9 try restarting transaction: DELETE FROM {cache_update}
6 try restarting transaction: INSERT INTO {history}
1 try restarting transaction: UPDATE {queue}To avoid deadlocks, you can update the MySQL transaction isolation variable, transaction_isolation, from the default value of REPEATABLE-READ to READ-COMMITTED. The transaction_isolation variable is the preferred MySQL variable. Setting this variable is the equivalent of setting the database isolation level by using the MySQL command, SET SESSION TRANSACTION ISOLATION LEVEL.
If your applications use the Multi-Region Failover feature, you cannot use READ-COMMITTED as this value is incompatible with the data replication method of Multi-Region Failover.
For more information on how to override any MySQL variable, visit Overriding Drupal $databases settings. Review this page and apply the changes for your use case.
For Acquia-hosted websites, you must exercise caution when updating transaction settings.
For all Drupal versions:
Update the following section in the code snippet provided on the Overriding Drupal $databases settings page:
'VARIABLE_NAME_1' => 'SET SESSION VARIABLE_NAME_1=VALUE_1',
'VARIABLE_NAME_2' => 'SET SESSION VARIABLE_NAME_2=VALUE_2',Replace one of the above with the following, which sets the transaction_isolation variable to READ-COMMITTED:
'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"'As BLT builds some files automatically, ensure that you review all of your files as some files might be manually edited and look different than the following example. For example, the following steps are applicable for a clean BLT install. These steps might vary based on the edited version of your BLT file. Therefore, ensure that you make the changes based on your specific use case.
Update the following section in the code snippet provided on the Overriding Drupal $databases settings page:
'VARIABLE_NAME_1' => 'SET SESSION VARIABLE_NAME_1=VALUE_1',
'VARIABLE_NAME_2' => 'SET SESSION VARIABLE_NAME_2=VALUE_2',Replace one of the above with the following, which sets the transaction_isolation variable to READ-COMMITTED:
'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"'Delete the following code snippet in the post-settings-php hook section:
$databases['default']['default']['init_commands'] = array(
'VARIABLE_NAME_1' => 'SET SESSION VARIABLE_NAME_1=VALUE_1',
'VARIABLE_NAME_2' => 'SET SESSION VARIABLE_NAME_2=VALUE_2',
);Add the following code snippet instead:
$databases['default']['default']['init_commands'] = [
'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"'
];Based on your application setup, you can verify that the override settings are being applied through the following ways:
Ensure that you replace SITE with the site URL or the mulitsite name that you want to check.
You can verify the MySQL variable through any one of the following methods:
Run the following Drush command:
$ drush --uri=[EDITME] ev 'print_r(Drupal\Core\Database\Database::getConnection("default", "default")->query("SHOW VARIABLES;")->fetchAll());' |grep -C2 tx_isolation #
Shows this output
stdClass Object
(
[Variable_name] => tx_isolation
[Value] => READ-COMMITTED
)Run the following command and check if the output of this command contains a reference of transaction isolation:
drush --uri=[EDITME] core:requirements
# Shows this output
+----------------------------+----------+--------------------------------------------------------------+
| Title | Severity | Summary |
+----------------------------+----------+--------------------------------------------------------------+
... snip ...
| | | |
| Database Isolation Level | OK | READ-COMMITTED |
| | | |
... snip ...
+----------------------------+----------+--------------------------------------------------------------+/admin/reports/status and check for any warning about the MySQL transaction isolation setting.$ drush --uri=[EDITME] ev 'print_r(Database::getConnection("default", "default")->query("SHOW VARIABLES;")->fetchAll());' |grep -C2 tx_isolation
# Shows this output
stdClass Object
(
[Variable_name] => tx_isolation
[Value] => READ-COMMITTED
)For information about table locking issues in Drupal 7, visit Fixes for MySQL Deadlocks in D7.
If this content did not answer your questions, try searching or contacting our support team for further assistance.
Tue May 20 2025 11:53:47 GMT+0000 (Coordinated Universal Time)