[Solved] Drupal 1213 Deadlock found

Drupal 1213 Deadlock found

Drupal 1213 Deadlock found when trying to get lock suggests that there's a problem with transactions and locks in the underlying database.The default transaction isolation level for databases like MySQL, MariaDB, and their equivalents is set to "REPEATABLE READ."

However, using this setting in Drupal may lead to table deadlocks, causing the site to slow down or become unresponsive. You may notice the below error in your database logging page:

Serialization failure: 
1213 Deadlock found when trying to get lock; try restarting transaction: 
INSERT INTO "cache_config" ("cid", "expire", "created", "tags", "checksum",
"data", "serialized") VALUES (:db_insert_placeholder_0, 
:db_insert_placeholder_1, :db_insert_placeholder_2

For optimal performance and to avoid deadlocks, it's recommended to set the transaction isolation level for Drupal sites to 'READ COMMITTED.' Although 'REPEATABLE READ' is an option, it can potentially result in deadlocks. The other choices, namely 'READ UNCOMMITTED' and 'SERIALIZABLE,' are available but not officially supported, so using them comes with risks.

If your site is using MySQL, MariaDB, or a similar database with the "REPEATABLE READ" isolation level, Drupal will issue a warning on the Status report page (admin/reports/status) to bring attention to the potential deadlock issues.

1. Here is the preferred way to change the transaction isolation level in settings.php file:

$databases['default']['default'] = array(
  'database' => 'databasename',
  'username' => 'sqlusername',
  'password' => 'sqlpassword',
  'host' => 'localhost',
  'driver' => 'mysql',
  'prefix' => '',
  'port' => '3306',
  'init_commands' => [
    'isolation_level' => 'SET SESSION tx_isolation=\'READ-COMMITTED\'',
  ],
);

2. To confirm that the setting has been made correctly you can run the mysql command:

mysql> show variables WHERE Variable_name LIKE "%_isolation";

3. You should see the following output:

mysql> show variables WHERE Variable_name LIKE "%isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-COMMITTED  |
+-----------------------+-----------------+
1 row in set (0.00 sec)

This should fix Drupal 1213 Deadlock found when trying to get lock error. Thank you.