Setting the MySQL transaction isolation level

The default transaction isolation level for MySQL, MariaDB, and equivalent databases is "REPEATABLE READ". This setting with Drupal can result in deadlocks on tables, resulting in the site becoming very slow or not responding at all.

The recommended transaction isolation level for Drupal sites is 'READ COMMITTED'. The 'REPEATABLE READ' option is supported but can result in deadlocks; the other options are 'READ UNCOMMITTED' and 'SERIALIZABLE'. They are available but not supported; use them at your own risk.

Drupal will generate a warning on the Status report page (admin/reports/status) when a MySQL, MariaDB, or equivalent database is used with the transaction isolation level set to "REPEATABLE READ".

If a site requires to explicitly set the transaction isolation level to 'READ COMMITTED' two methods can be used:

The preferred way to change the transaction isolation level

Global Value

The first one is to run a database query that sets the transaction isolation level for every session on the database. The query is:

SET GLOBAL transaction_isolation='READ-COMMITTED';

or

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

To make the setting persist after a reboot, you can use

SET PERSIST transaction_isolation = 'READ-COMMITTED';

and/or add the setting to the config file, as described below.

These queries can only be run by a database user with superuser privileges.  Also note, mysql will respond with "Query OK, 0 rows affected" and if you check the transaction isolation level with the command "SELECT @@transaction_isolation;" you will see the previous setting.  If this happens to you, logout and log back into mysql and run the above SELECT command again and now you should see the READ-COMMITTED setting.  Hope this will save you from a wild-goose chase.

You can check the value has been set by running the MySQL command below.

Config File

Alternatively, you can set this in your MySQL config file in the [mysqld] section. (may require root or administrator access)

transaction_isolation="READ-COMMITTED"

You can check the value has been set by running the MySQL command below.

Command Line

To set the server's default isolation level for all connections, use the --transaction-isolation option on the command line (e.g.: mysqld --transaction-isolation=READ-COMMITTED)

AWS RDS Parameters

If you are using Amazon Web Services (AWS) Relational Database Services (RDS) to set the server's default isolation level for all connections:

Create a new parameter group:

1. go to Amazon RDS Console
2. choose Parameter Groups in the left-nav
3. Click the Create Parameter Group button
4. Give your new group a Group Name (eg: 'custom.mysql8' and a Description)
5. Save your parameter group
6. On the Parameter Groups listing page, click on your new parameter group
7. In the Parameters search box, enter "tx_isolation" to find the proper parameter
8. Click the checkbox next to "tx_isolation"
9. Click the Edit Parameters button
10. In the Values dropdown, select READ-COMMITTED
11. Click Save button

Apply your parameter group to your database:

1. Go to the Amazon RDS->Databases page
2. Click on your database name to go to the database details page
3. Click the Modify button
4. Under the Additional Configurations section, edit the DB parameter group setting and select your newly created parameter group
5. At the bottom of the page click the Continue button
6. Select the Apply Immediately option (unless you need to wait until a maintenance window)
7. Click the Modify DB instance button
8. It will return you to the Database listing page and show a status of Modifying until the change has been saved.  Use the Refresh button to update the status until the status shows as Available.
9. To apply the new parameters to the running database, select your database's radio button and under Actions select Reboot.
10. After the reboot is complete, check your Drupal Reports->Status Page to confirm that the database is no longer showing you the warning. You can also check the value has been set by running the MySQL command below.

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

show variables WHERE Variable_name LIKE "%_isolation";

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)

Other methods to change the transaction isolation level

The second method is to update the settings.php file. The default location for the file is sites/default/settings.php. The file is created during the installation of your Drupal site.

At the end of the settings.php file is usually the database connection array found. Something like:

$databases['default']['default'] = array(
  'database' => 'databasename',
  'username' => 'sqlusername',
  'password' => 'sqlpassword',
  'host' => 'localhost',
  'driver' => 'mysql',
  'prefix' => '',
  'port' => '3306',
);

To change the database transaction isolation level to "READ COMMITTED" add the following to the database connection array:

  'isolation_level' => 'READ COMMITTED',

The database connection array with the added setting will be something like this:

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

Adding the setting of the transaction isolation level to the init commands in the settings.php file has the disadvantage that on every page request the transaction isolation level is set. That is an extra database call for every page request!

To confirm that the setting has been made correctly set with this method run the drush command:

drush eval "echo \Drupal::database()->query(\"show variables WHERE Variable_name LIKE '%isolation';\")->fetchAll()[0]->Value . PHP_EOL;"
READ-COMMITTED

or visit your Drupal's Status report page (admin/reports/status) where you should see this message :

For more information about isolation levels see:

• MariaDB: Isolation Levels and Locks and tx_isolation system variable.
• MySQL: Transaction Isolation Levels documentation and transaction_isolation system variable. 

Important notice regarding binary log format

Changing the transaction isolation level may also change the binary log format of your server because only row-based binary logging is supported with the READ COMMITTED isolation level. Even if you use READ COMMITTED with binlog_format=MIXED, the server automatically uses row-based logging. Source: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

For instance, in MariaDB version >10.2.3 the default binary log format is mixed logging. So changing the isolation level here will also change the binary log format in row logging. Source: https://mariadb.com/kb/en/binary-log-formats/

Please be aware that changing the binary log format changes the behaviour of your database server. For instance, row logging requires more storage space than mixed logging. Source: https://mariadb.com/kb/en/binary-log-formats/