Bug #88070 Too limited compatibility options for transaction isolation
Submitted: 12 Oct 2017 7:17 Modified: 16 Oct 2017 11:58
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:8.0.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: compatibility, transaction_isolation, tx_isolation

[12 Oct 2017 7:17] Daniël van Eeden
Description:
In the (not yet released) MySQL 5.7.20 the tx_isolation was deprecated. And this variable is removed in 8.0.3.

The problem is that this makes upgrades needlessly difficult.

1. On 5.7.19 'transaction_isolation' variable doesn't exist, so we can't switch to the new variable before upgrading.
2. On 8.0.3 the 'tx_isolation' variable doesn't exist, so we can't switch after upgrading.
3. Using "SELECT @@tx_isolation /*!800000 ,@@transaction_isolation */;" works to only use transaction_isolation on 8.0 and above, but there is no equivalent to only use @@tx_isolation on versions lower than 8.0.

For code which needs to set the transaction isolation we can use a SET TRANSACTION ISOLATION LEVEL. But to read this in code which needs to work with 5.7 and 8.0 we need to: First we need to get the VERSION() and then based on the result request the right variable (e.g. SELECT @@session.tx_isolation). To get rid of the extra roundtrip we could use "SHOW SESSION VARIABLES LIKE 't%\_isolation'"

Probably the best way to do this on 5.7 and 8.0 is 
SELECT VARIABLE_VALUE FROM performance_schema.session_variables WHERE VARIABLE_NAME IN ('tx_isolation','transaction_isolation') LIMIT 1;

But that is way more complicated than just doing "SELECT @@tx_isolation"

As some third-party applications might use this this might make it difficult to upgrade to 8.0.

How to repeat:
Try to read the session variable for transaction isolation from an application which needs to be compatible with MySQL 5.7 and MySQL 8.0. 

Suggested fix:
Add a tx_isolation variable to 8.0 which has the same value as transaction_isolation. This variable should be deprecated in 8.0.
[16 Oct 2017 5:36] Simon Mudd
Bottom line: deprecation of old functionality should be done with more care and giving people and code "time to migrate". I was one of the people to request that variable names are consistent but this change is too quick and clearly not thought out and there seems to be little consideration of people who run multiple major MySQL versions at once.
[16 Oct 2017 11:58] MySQL Verification Team
Hello Daniël, Simon,

Thank you for the report and feedback!

Regards,
Umesh