Cloud Platform
MySQL transactions
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:
Cloud PlatformPDOException: 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.
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 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"'Based on your application setup, you can verify that the override settings are being applied through the following ways:
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:requirementsShows 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.If this content did not answer your questions, try searching or contacting our support team for further assistance.
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:requirementsShows 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.If this content did not answer your questions, try searching or contacting our support team for further assistance.