Drupal core will warn if a mysql database is not using READ COMMITTED 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, which will result 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 2 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 when a MySQL, MariaDB or equivalent database is used with the transaction isolation level set to "REPEATABLE READ".

There are two methods to change the transaction isolation level to 'READ COMMITTED'.

The preferred way to change the transaction isolation level

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 LEVEL READ COMMITTED

This query can only be run by a database user that has superuser privileges.

The alternative way 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:

  'init_commands' => [
    'isolation_level' => 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
  ],

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

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

For more information about isolation levels see the MySQL Transaction Isolation Levels documentation.

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 being set. That is an extra database call for every page request!