How to show transaction isolation level (MySQL)

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.

Coaptation

Answer 1

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';

Coaptation

Note that this answer is outdated. See the other answers for MySQL 8. – Ion

Answer 2

check session transaction level (mysql8+)

SELECT @@transaction_ISOLATION;

check global transaction level (mysql8+)

SELECT @@global.transaction_ISOLATION;

Furlani

is @@global.transaction_ISOLATION the current session variable? – Florey

Wait a second, it's the other way round, right? @@global. is the global one. – Butta

Answer 3

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

Kodak

Answer 4

I use the following snippet:

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

Grivation

Answer 5

MySQL 8+

SELECT @@transaction_isolation; -- session

SELECT @@global.transaction_isolation; -- global

According to MySQL 8 Deprecation notes:

The deprecated tx_isolation and tx_read_only system variables have been removed. Use transaction_isolation and transaction_read_only instead.

Mexican

Answer 6

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

Caveator

Answer 7

Just one another query grammer:

MariaDB [email protected]:mysql> show variables like '%tx%'
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
| tx_read_only  | OFF             |
+---------------+-----------------+
2 rows in set
Time: 0.006s

Durra

Answer 8

You can show session isolation level. *My answer explains how to set transaction isolation level:

SELECT @@SESSION.transaction_isolation;

Or:

SELECT @@transaction_isolation;

Or:

SHOW VARIABLES WHERE VARIABLE_NAME = 'transaction_isolation';

And, you can show global isolation level:

SELECT @@GLOBAL.transaction_isolation;

In addition, you can show persisted isolation level if it exists

SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME = 'transaction_isolation';

Radiotherapy