[MDEV-31751] SET @@tx_isolation=X behaves like SET TRANSACATION ISOLATION LEVEL X instead of SET SESSSION tx_isolation=X Created: 2023-07-20  Updated: 2023-10-10

Status: In Review
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.4.30
Fix Version/s: 11.1

Type: Bug Priority: Minor
Reporter: Daniel Black Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-31746 Problems with tx_isolation after MDEV... Closed

 Description   

Both SET TRANSACTION ISOLATION and SET @@tx_isolation=X are OPT_DEFAULT, which gets processed like the former.

set TRANSACTION ISOLATION LEVEL READ COMMITTED;

 
Thread 31 "mariadbd" hit Breakpoint 3, Sys_var_tx_isolation::session_update (this=0x17afc18 <Sys_tx_isolation>, thd=0x7f6a28000c58, var=0x7f6a280128c0)
 
    at /home/dan/repos/mariadb-server-10.4/sql/sys_vars.inl:2231
 
2231	    if (var->type == OPT_SESSION && Sys_var_enum::session_update(thd, var))
 
(gdb) p *var
 
$5 = (set_var) {<set_var_base> = {<Sql_alloc> = {<No data fields>}, _vptr$set_var_base = 0xf46d50 <vtable for set_var+16>}, var = 0x17afc18 <Sys_tx_isolation>, value = 0x7f6a28012830, type = SHOW_OPT_DEFAULT, 
  save_result = {ulonglong_value = 1, longlong_value = 1, double_value = 4.9406564584124654e-324, plugin = 0x1, plugins = 0x1, time_zone = 0x1, string_value = {
 
      str = 0x1 <error: Cannot access memory at address 0x1>, length = 140093914425568}, timestamp = {unix_time = 1, second_part = 140093914425568}, ptr = 0x1}, base = {str = 0x0, length = 0}}

set session tx_isolation='REPEATABLE-READ';

 
Thread 31 "mariadbd" hit Breakpoint 3, Sys_var_tx_isolation::session_update (this=0x17afc18 <Sys_tx_isolation>, thd=0x7f6a28000c58, var=0x7f6a280128f8)
 
    at /home/dan/repos/mariadb-server-10.4/sql/sys_vars.inl:2231
 
2231	    if (var->type == OPT_SESSION && Sys_var_enum::session_update(thd, var))
 
(gdb) p *var
 
$4 = (set_var) {<set_var_base> = {<Sql_alloc> = {<No data fields>}, _vptr$set_var_base = 0xf46d50 <vtable for set_var+16>}, var = 0x17afc18 <Sys_tx_isolation>, value = 0x7f6a28012840, type = SHOW_OPT_SESSION, 
  save_result = {ulonglong_value = 2, longlong_value = 2, double_value = 9.8813129168249309e-324, plugin = 0x2, plugins = 0x2, time_zone = 0x2, string_value = {
 
      str = 0x2 <error: Cannot access memory at address 0x2>, length = 140093914425624}, timestamp = {unix_time = 2, second_part = 140093914425624}, ptr = 0x2}, base = {str = 0x0, length = 0}}

set @@tx_isolation='READ-UNCOMMITTED';

 
Thread 31 "mariadbd" hit Breakpoint 3, Sys_var_tx_isolation::session_update (this=0x17afc18 <Sys_tx_isolation>, thd=0x7f6a28000c58, var=0x7f6a280128f8)
 
    at /home/dan/repos/mariadb-server-10.4/sql/sys_vars.inl:2231
 
2231	    if (var->type == OPT_SESSION && Sys_var_enum::session_update(thd, var))
 
(gdb) p *var
 
$6 = (set_var) {<set_var_base> = {<Sql_alloc> = {<No data fields>}, _vptr$set_var_base = 0xf46d50 <vtable for set_var+16>}, var = 0x17afc18 <Sys_tx_isolation>, value = 0x7f6a28012838, type = SHOW_OPT_DEFAULT, 
  save_result = {ulonglong_value = 0, longlong_value = 0, double_value = 0, plugin = 0x0, plugins = 0x0, time_zone = 0x0, string_value = {str = 0x0, length = 140093914425624}, timestamp = {unix_time = 0, 
      second_part = 140093914425624}, ptr = 0x0}, base = {str = 0x0, length = 0}}

set @@tx_isolation= has a OPT_DEFAULT rather than an OPT_SESSION needed to follow the right path.

 Comments   
Comment by Elena Stepanova [ 2023-07-20 ]

If it has been so all this time, I don't think you can suddenly change behavior of a variable in the old GA.
serg ^

Comment by Daniel Black [ 2023-07-20 ]

might be a way to fix it for @@transaction_isolation only in 11.2, better?

Comment by Daniel Black [ 2023-07-21 ]

Making this a blocker as with a new replacement variable @@transaction_isolation not in GA, we can make that behave correctly and let the @@tx_isolation behave exactly like it did before (and leave a documentation task to update it).

Comment by Daniel Black [ 2023-07-21 ]

https://github.com/MariaDB/server/pull/2709 - second commit only.

Comment by VAROQUI Stephane [ 2023-10-10 ]

Since minor update of release < 11 also tracking of session variable tx-isolation stop working and is default in most drivers

Comment by Daniel Black [ 2023-10-10 ]

MDEV-29629 / MDEV-31746 was meant to track variables and their alias to avoid this problem.

The implementation https://github.com/MariaDB/server/pull/2707 probably would have avoided this, except it wasn't used. Please raise a regression blocker bug.

Generated at Thu Feb 08 10:26:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.