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
.
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 end up waiting 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}
For Acquia-hosted websites, you must exercise caution when updating transaction settings.
The steps in the procedure are applicable for a clean BLT install. These steps might slightly vary based on the edited version of your BLT file. Therefore, ensure that you make the changes based on your specific use case.
settings.php
file, locate the line that requires blt.settings.php
.Add the following before the blt.settings.php
require line:
// On Acquia Cloud, do not immediately connect to the database.
global $conf, $databases;
$conf['acquia_hosting_settings_autoconnect'] = FALSE;
Add the following after the blt.settings.php
require line:
// Set the Transaction isolation.
$databases['default']['default']['init_commands'] = [
'tx_isolation' => "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED",
];
// On Acquia Cloud, connect to the Database.
if (function_exists("acquia_hosting_db_choose_active")) {
acquia_hosting_db_choose_active();
}
The following is the code that includes the preceding changes:
// On Acquia Cloud, do not immediately connect to the database.
global $conf, $databases;
$conf['acquia_hosting_settings_autoconnect'] = FALSE;
// Original code put in by BLT.
require DRUPAL_ROOT . "/../vendor/acquia/blt/settings/blt.settings.php";
// Set the Transaction isolation.
$databases['default']['default']['init_commands'] = [
'tx_isolation' => "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED",
];
// On Acquia Cloud, connect to the Database.
if (function_exists("acquia_hosting_db_choose_active")) {
acquia_hosting_db_choose_active();
}
After you use the preceding code, you might get the following error when you run Drush:
TypeError: count(): Argument #1 ($value) must be of type Countable|array, null
given in count() (line 155 of /usr/ah/lib-pub/D9-settings.functions.inc).
To resolve this error, replace acquia_hosting_db_choose_active();
with acquia_hosting_db_choose_active($conf['acquia_hosting_site_info']['db'], 'default', $databases, $conf);
.
To verify the updated MySQL transaction settings:
Connect to the database.
Verify the appropriate MySQL variable.
The following are the examples specific to the current Drupal version and Drupal 7:
You can verify 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
/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
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.