In Drupal 10.1.0, the default MySQL transaction 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 level to REPEATABLE-READ for compliance reasons. If you experience deadlocks when querying semaphore tables in your databases, you can override the MySQL transaction level to READ-COMMITTED.

Finding MySQL deadlocks¶

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}

Overriding MySQL transaction setting¶

To avoid deadlocks, you can update the transaction isolation variable, tx_isolation, from the default value of REPEATABLE-READ to READ-COMMITTED.

You can override the MySQL transaction setting based on the following scenarios:

if (file_exists('/var/www/site-php')) {
  global $conf, $databases;
  $conf['acquia_hosting_settings_autoconnect'] = FALSE;
  // EDIT next line to proper path to include file.
  require('/var/www/site-php/MYSITE/MYDATABASE-settings.inc');
  $databases['default']['default']['init_commands'] = array(
    'isolation_level' => "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED",
  );
  if (function_exists("acquia_hosting_db_choose_active")) {
    acquia_hosting_db_choose_active();
  }
}

Verifying the MySQL transaction settings¶

To verify the updated MySQL transaction settings:

1. Connect to the database.

2. Verify the appropriate MySQL variable.

   The following are the examples specific to the current Drupal version and Drupal 7:

   Current Drupal Version

   Drupal 7

   You can verify the MySQL variable through any one of the following methods:

   • Run the following Drush command:

     copy

     $ 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:

     copy

     drush --uri=[EDITME] core:requirements
     # Shows this output
     +----------------------------+----------+--------------------------------------------------------------+
     | Title                      | Severity | Summary                                                      |
     +----------------------------+----------+--------------------------------------------------------------+
     ... snip ...
     |                            |          |                                                              |
     | Database Isolation Level   | OK       | READ-COMMITTED                                               |
     |                            |          |                                                              |
     ... snip ...
     +----------------------------+----------+--------------------------------------------------------------+

   • Access /admin/reports/status and check for any warning about the MySQL transaction isolation setting.