MySQL Bugs: #53341: No way to find out the current transaction's isolation level

Bug #53341	No way to find out the current transaction's isolation level
Submitted:	1 May 2010 10:52	Modified:	2 May 2010 9:07
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0+, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There is no way to retreive the current transaction isolation level.

@@session.tx_isolation returns the session setting. However, if there was SET TRANSACTION ISOLATION LEVEL statement, or COMMIT AND CHAIN, or ROLLBACK AND CHAIN, or @@session.completion_type is set to 1, this value can not be trusted.

How to repeat:
There is no way to repeat as this is something you can't do.

Suggested fix:
I don't see an easy solution.
Perhaps implement a new scope for our variables, @@transaction, and allow to retrieve the isolation level by select @@transaction.isolation_level.

Another alternative would be to remove SET TRANSACTION ISOLATION LEVEL altogether, but that wouldn't solve the case of COMMIT AND CHAIN.

Can you, please, provide any example that proves your point. Why these results can not be trusted:

77-52-4-109:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE           |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.01 sec)

mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.00 sec)

Valeriy, @@tx_isolation is a result of the session transaction isolation.
It does not always match the current transaction's isolation.
Isolation of a transaction is normally established at its start -- after that the session value may get changed.
Another case is when one used COMMIT AND CHAIN statement, when the current transaction's isolation level is inherited from the previous transaction, not from the session value.

Your last example with SET TRANSACTION ISOLATION LEVEL just highlights how bogus is currently the value of @@session.tx_isolation. In this example, we're in fact dealing 3 different isolation levels: current transaction, next transaction, session.
The current transaction is taken from the session value at transaction start. There is no way to see it. The session transaction is supposedly stored in @@session.tx_isolation, but for some reason, when one updates the *next* transaction isolation level with SET TRANSACTION ISOLATION LEVEL the session value is changed!

This is a mess, and Bug#20837 attempts to fix it. When it is fixed, however, SET TRANSACTION ISOLATION LEVEL will have no effect on @@session.tx_isolation (since these two values are unrelated).

As Konstantin says, this behavior was changed by the bug fix and there is still no way to find the current transaction isolation level.

The above sequence now produces correct (but not useful) results:

Server version: 5.5.15-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

I think this bug should be revisited.

In 8.0 tx_isolation is renamed to transaction_isolation.
This bug was reported against 5.1, 5.5. Since then 5.6 and 5.7 were released and 8.0 is now in release candidate stage.

Bug #108904 marked as duplicate of this one.