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.

Finding MySQL deadlocks

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 log and log:

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.

Finding MySQL deadlocks

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 -nr

This 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}

Resolving deadlocks by overriding the MySQL transaction isolation level setting

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.

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.

Verifying the MySQL transaction settings

Based on your application setup, you can verify that the override settings are being applied through the following ways: 

Related Resources

• Fixes for MySQL Deadlocks in D7
• Overriding Drupal $databases settings
• Fixing database deadlocks