[MDEV-6955] SET STATEMENT tx_isolation = FOR ... inside a transaction is meaningless and cannot have any effect, but is allowed Created: 2014-10-27  Updated: 2015-02-02  Resolved: 2014-10-29

Status: Closed
Project: MariaDB Server
Component/s: Admin statements
Affects Version/s: N/A
Fix Version/s: 10.1.2

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-5231 Per query variables from Percona Serv... Closed
relates to MDEV-6923 Testing for SET STATEMENT .. FOR (MDE... Closed

 Description   

Transaction isolation level cannot be changed inside a transaction. Unlike most session variables, session-level transaction properties, when they are modified, become effective from the next transaction. Thus, changing the value for a single statement inside a non-atomic transaction is meaningless and should be prohibited. Now it is not.

Percona server works OK, it returns the expected error:

1568: Transaction characteristics can't be changed while a transaction is in progress

Test case

 
--source include/have_innodb.inc
 
--enable_connect_log
 
 
 
create table t1 (i int) engine=InnoDB;
 
 
 
begin;
 
select * from t1;
 
 
 
--connect (con1,localhost,root,,)
 
insert into t1 values (1);
 
 
 
--connection default
 
--echo # With repeatable-read, we don't see the new value (OK)
 
select * from t1;
 
 
 
--echo # But what does it mean, set transaction isolation for one statement 
--echo # when we are inside a transaction?
 
set statement tx_isolation='read-committed' for select * from t1;
 
 
 
--echo # ... Apparently, nothing.

With MariaDB, the last set statement ... for select returns an empty set, which shows that the tx_isolation setting didn't work.

With Percona server, the same query causes an error. 

commit e64f5d8f758bcc1a8856ba9fba01780533f80747
 
Author: Oleksandr Byelkin <sanja@mariadb.com>
 
Date:   Sun Oct 26 16:27:54 2014 +0100
 
 
 
    Fixed test suite global variable saving



 Comments   
Comment by Oleksandr Byelkin [ 2014-10-29 ]

The variable is prohibited in SET STATEMENT for now.

Generated at Thu Feb 08 07:15:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.