--- title: "MySQL transactions" date: "2024-09-27T03:37:36+00:00" summary: "Resolve MySQL deadlocks in Drupal by updating the transaction isolation level. Learn how to identify, troubleshoot, and fix database conflicts for improved performance and data integrity." image: type: "page" url: "/acquia-cloud-platform/mysql-transactions" id: "6eb3f0ce-3910-4a00-ba88-b7ccbaef5a4c" --- In Drupal [10.1.0](https://www.drupal.org/node/3264101), the default [MySQL transaction isolation level](https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html) 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](/acquia-cloud-platform/drupal-watchdog-logs "/node/56394"), [PHP error log](/acquia-cloud-platform/php-error-logs "PHP error logs"), [Cloud Hooks log](/acquia-cloud-platform/cloud-hooks-logs "Cloud Hooks logs"), [Scheduled Cron Jobs log](/acquia-cloud-platform/scheduled-cron-jobs-logs "Scheduled Cron Jobs logs"), and any script that invokes database operations. The following are the error examples specific to [Drupal watchdog](/acquia-cloud-platform/drupal-watchdog-logs "Drupal watchdog logs") log and [PHP error](/acquia-cloud-platform/php-error-logs "PHP error logs") 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`](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_transaction_isolation), from the default value of `REPEATABLE-READ` to `READ-COMMITTED`. The [`transaction_isolation`](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_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`](https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html "13.3.6 SET TRANSACTION Statement"). Note If your applications use the [Multi-Region Failover](/acquia-cloud-platform/using-multi-region-failover "Using multi-region failover") feature, you cannot use `READ-COMMITTED` as this value is incompatible with the data replication method of [Multi-Region Failover](/acquia-cloud-platform/using-multi-region-failover "Using multi-region failover"). For more information on how to override any MySQL variable, visit [Overriding Drupal $databases settings](/acquia-cloud-platform/overriding-drupal-databases-settings "Overriding Drupal $databases settings"). Review this page and apply the changes for your use case. Caution For Acquia-hosted websites, you must exercise caution when updating transaction settings. ### Sites not using Site Factory For all Drupal versions: 1. Follow the steps outlined on the [Overriding Drupal $databases settings](/acquia-cloud-platform/overriding-drupal-databases-settings "Overriding Drupal $databases settings") page. 2. Update the following section in the code snippet provided on the [Overriding Drupal $databases settings](/acquia-cloud-platform/overriding-drupal-databases-settings "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', 3. 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"' ### Sites using Site Factory 1. Follow the steps outlined on the [Overriding Drupal $databases settings](/acquia-cloud-platform/overriding-drupal-databases-settings "Overriding Drupal $databases settings") page. 2. Delete the following code snippet in the [post-settings-php hook](https://docs.acquia.com/site-factory/extend/hooks#section-hooks-executed-on-every-page-load) 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', ); 3. Add the following code snippet instead: $databases['default']['default']['init_commands'] = [ 'transaction_isolation' => 'SET SESSION transaction_isolation="READ-COMMITTED"' ]; #### Verifying the MySQL transaction settings Based on your application setup, you can verify that the override settings are being applied through the following ways:  Note Ensure that you replace `SITE` with the site URL or the [mulitsite](/acquia-cloud-platform/about-drupal-multisite-installations "About Drupal multisite installations") name that you want to check.  ### Current Drupal version 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 _isolation # Shows this output: stdClass Object (    [Variable_name] => transaction_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 ... +----------------------------+----------+--------------------------------------------------------------+ * Access `/admin/reports/status` and check for any warning about the MySQL transaction isolation setting. ### Drupal 7 $ drush --uri=[EDITME] ev 'print_r(Database::getConnection("default", "default")->query("SHOW VARIABLES;")->fetchAll());' |grep -C2 _isolation Shows this output: stdClass Object ( [Variable_name] => transaction_isolation [Value] => READ-COMMITTED ) For information about table locking issues in Drupal 7, visit [Fixes for MySQL Deadlocks in D7](https://docs.acquia.com/site-factory/extend/hooks/settings-php#acsf-tx-isolation). Related Resources ----------------- * [Fixes for MySQL Deadlocks in D7](https://docs.acquia.com/site-factory/extend/hooks/settings-php#acsf-tx-isolation) * [Overriding Drupal $databases settings](/acquia-cloud-platform/overriding-drupal-databases-settings "Overriding Drupal $databases settings") * [Fixing database deadlocks](https://acquia.my.site.com/s/article/360005253954-Fixing-database-deadlocks)