This article discusses a warning message that Drupal displays about performance and MySQL transaction isolation levels, and how to resolve it.
After you install Drupal, you may see the following warning message on the Status report page of the administration interface:
Transaction isolation level REPEATABLE-READ The recommended level for Drupal is "READ COMMITTED". See the setting MySQL transaction isolation level page for more information.
By default, MySQL uses the REPEATABLE READ isolation level. According to the Drupal documentation, however, this level can cause deadlocks on database tables, leading to poor site performance.
To determine the current transaction isolation level enabled on your account, follow these steps:
mysql -u username -p
At the prompt, type the following command:
SHOW variables WHERE variable_name LIKE "%_isolation";
Examine the command output:
+---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.001 sec)
If the isolation level is set to READ COMMITTED, you receive the following output:
+---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+ 1 row in set (0.001 sec)
The procedure to change the transaction isolation level depends on the type of hosting account you have.
To change the transaction isolation level on these types of hosting accounts, follow these steps:
$databases['default']['default'] = array ( 'database' => 'example_drup123', 'username' => 'example_drup123', 'password' => 'example-password', 'prefix' => 'drom_', 'host' => 'localhost', 'port' => '3306', 'isolation_level' => '', 'driver' => 'mysql', );
Change the isolation_level line as follows:
'isolation_level' => 'READ COMMITTED',
For example, the database connection array should now look like:
$databases['default']['default'] = array ( 'database' => 'example_drup123', 'username' => 'example_drup123', 'password' => 'example-password', 'prefix' => 'drom_', 'host' => 'localhost', 'port' => '3306', 'isolation_level' => 'READ COMMITTED', 'driver' => 'mysql', );
How you change the transaction isolation level on a VPS or Dedicated server depends on whether or not you have root access:
transaction_isolation="READ-COMMITTED"
Type the following command to restart the MySQL service:
systemctl restart mysqld
Subscribe to receive weekly cutting edge tips, strategies, and news you need to grow your web business.
No charge. Unsubscribe anytime.
Did you find this article helpful? Then you'll love our support. Experience the A2 Hosting difference today and get a pre-secured, pre-optimized website. Check out our web hosting plans today.
We use cookies to personalize the website for you and to analyze the use of our website. You consent to this by clicking on "I consent" or by continuing your use of this website. Further information about cookies can be found in our Privacy Policy.