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

    XMLWordPrintable

    Details

      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

        Attachments

          Issue Links

            Activity

              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: