Question 20688 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql : Cannot set Transaction Isolation level

Mysql version :5.7.41-0ubuntu0.18.04.1
Cannot modify the isolation level into READ COMMITTED needed for a Drupal10 database:

Running these commands as root

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0,00 sec)

mysql> show variables WHERE Variable_name LIKE "%isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0,00 sec)

or adding

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

to the database array in settings.php

Any idea how to solve?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks for your answer. I was surprised that running SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED worked perfectly from the mysql prompt but not from the Drupal settings php. The reason was the presence of 2 blocks of database array, a first one at the top of the file containing the init_commands isolation_level...`and a second block where the init_commands was missing. (and silly overwriting the previous setting)


  2. 0

    SET GLOBAL changes the global value but does not change your current session’s value. Your current session copied values from the global settings at the time the session started. Sessions don’t inherit subsequent global setting changes (with a few exceptions, e.g. read_only).

    You have two alternatives to fix this:

    • Use SET SESSION instead of SET GLOBAL.

    • Close and reopen your connection, which will start a new session and copy the current global values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search