Mysql - How to find out isolation level for the tr

Question

I want to know what isolation level is set for current Mysql database. How can find it out? I tried searching it on the google but did not find it.

Answer 1

There are potentially six different transaction isolation level values in MySQL/MariaDB - and they could have different values. They are:

1. Value defined in the configuration file(s).
2. Value used in the command line option used to start mysqld.
3. The global transaction isolation level.
4. The session transaction isolation level.
5. The level that will be used by the very next transaction that is created.
6. The level being used by the current transaction.

The reason you want to know its value, will determine which one (or multiple ones) you need.

Also, be aware of when the level that was obtained can change - sometimes by things outside your control.

1. Configured level

Look for a transaction-isolation entry in the configuration files. This may be found under sections such as [mysqld] or [server].

Start with the default /etc/my.cnf, but you may have to look in other configuration files depending on the include statements used. Be aware that mysqld might be started with command line options telling it to ignore the configuration files or to use a different set of configuration files.

2. Command line option level

Examine how the mysqld process was started. The level used here will override any specified in the configuration files.

It may change if mysqld is somehow started differently in the future.

3. Global level

This can be retrieved by running SELECT @@global.tx_isolation;.

This is initially set when the database starts up, to the level provided by the command line option or from the configuration file.

It can be changed by running set GLOBAL transaction isolation level .... But be aware that any value set that way will be lost when the database restarts. It could change if some other program runs the set global command.

4. Session level

This can be retrieved by running SELECT @@tx_isolation;.

When a new session/connection is created, it is set to the current global level.

It can be changed by running set SESSION transaction isolation level ... in that session.

If you are using connection pools, be aware that its value could change on you (reverting back to the global level), since connections can be silently terminated and re-established if it is put back into the pool.

5. Next transaction level

There is no way to query this.

This level is set by running set transaction isolation level ... and that level will override the session level and global level for the very next transaction created in that session. The next transaction after that will revert back to using the session level (unless another set transaction isolation level command is issued again).

To know this value, you'll have to keep track of how you've used set transaction isolation level (if you've used it at all).

6. Current transaction level

There is no way to query this. (It is the subject of MySQL bug #53341.)

To know this value, you'll have to infer what it is from the session level (at the time the transaction was created) and if you had set the "next transaction level" immediately before you created the transaction.

References

• Set transaction in MariaDB: https://mariadb.com/kb/en/library/set-transaction/

• Transaction isolation levels in MySQL: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

Answer 2

I did a bit of more searching on the google and found out that if have MySQL 5.1+ then you can find out the isolation level by firing below query

SELECT * FROM information_schema.session_variables
WHERE variable_name = 'tx_isolation';

Answer 3

check global transaction level (mysql8+)

SELECT @@transaction_ISOLATION;

check session transaction level (mysql8+)

SELECT @@global.transaction_ISOLATION;

Answer 4

You could

SELECT DATABASE();      to verify you are using the database, then
SELECT @TX_ISOLATION;    for the DB TX isolation value
SELECT @@TX_ISOLATION;   for global TX isolation value

if your db verification is not what you think it should be,

USE [db-you-want-to-check];
SELECT DATABASE();    to verify current database, then
SELECT @TX_ISOLATION;   for current database TX isolation value
SELECT @@TX_ISOLATION;   for GLOBAL TX isolation value

Answer 5

I use the following snippet:

mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)