Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6955

SET STATEMENT tx_isolation = FOR ... inside a transaction is meaningless and cannot have any effect, but is allowed

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Minor
        • Resolution: Fixed
        • Affects Version/s: N/A
        • Fix Version/s: 10.1.2
        • Component/s: Admin statements
        • Labels:
          None

          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

            Gliffy Diagrams

              Attachments

                Issue Links

                relates to

                Task - A task that needs to be done. MDEV-5231 Per query variables from Percona Server [patch]

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                Technical task - A technical task. MDEV-6923 Testing for SET STATEMENT .. FOR (MDEV-5231, per-query variables)

                • Major - Major loss of function.
                • Closed

                  Activity

                  Hide
                  Permalink
                  sanja Oleksandr Byelkin added a comment -

                  The variable is prohibited in SET STATEMENT for now.

                  Show
                  sanja Oleksandr Byelkin added a comment - The variable is prohibited in SET STATEMENT for now.

                    People

                    • Assignee:
                      sanja Oleksandr Byelkin
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: