Bug #20837 Apparent change of isolation level during transaction
Submitted: 3 Jul 2006 23:28 Modified: 30 Jul 2010 2:34
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1 OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Magne Mæhre CPU Architecture:Any
Tags: Contribution, regression

[3 Jul 2006 23:28] Peter Gulutzan
Description:
When I start a transaction as READ UNCOMMITTED, and I update an
InnoDB table, I see an apparent change to REPEATABLE READ. This
is understandable (READ UNCOMMITTED transaction should be READ
ONLY), but the isolation level should not automatically change
after the transaction has started.

In fact InnoDB won't read uncommitted data even if the isolation
level was initially set to READ UNCOMMITTED, so this is not a
serious matter. But people who select @@tx_isolation will get
the impression that isolation level really changes, wrongly.

How to repeat:
mysql> create table t1 (s1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| s1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values (-1);
Query OK, 1 row affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
[4 Jul 2006 8:52] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.1.12-BK (ChangeSet@1.2237, 2006-07-03 23:17:53-04:00).
[2 Nov 2007 8:30] Sergei Golubchik
patch: http://lists.mysql.com/internals/35146
[19 Oct 2008 7:46] Sveta Smirnova
This happens with SELECT as well:

--source include/have_innodb.inc

drop table if exists test1;
CREATE TABLE test1
(
  id smallint not null,
  primary key (id)
) engine=innodb;

insert into test1 values (1),(2),(3);

set transaction isolation level read uncommitted;
SELECT @@tx_isolation;
begin;
SELECT @@tx_isolation;
select * from test1;
SELECT @@tx_isolation;

Result:

drop table if exists test1;
Warnings:
Note    1051    Unknown table 'test1'
CREATE TABLE test1
(
id smallint not null,
primary key (id)
) engine=innodb;
insert into test1 values (1),(2),(3);
set transaction isolation level read uncommitted;
SELECT @@tx_isolation;
@@tx_isolation
READ-UNCOMMITTED
begin;
SELECT @@tx_isolation;
@@tx_isolation
READ-UNCOMMITTED
select * from test1;
id
1
2
3
SELECT @@tx_isolation;
@@tx_isolation
REPEATABLE-READ
[19 Oct 2008 7:48] Sveta Smirnova
Same for read committed:

drop table if exists test1;
Warnings:
Note    1051    Unknown table 'test1'
CREATE TABLE test1
(
id smallint not null,
primary key (id)
) engine=innodb;
insert into test1 values (1),(2),(3);
set transaction isolation level read committed;
SELECT @@tx_isolation;
@@tx_isolation
READ-COMMITTED
begin;
SELECT @@tx_isolation;
@@tx_isolation
READ-COMMITTED
select * from test1;
id
1
2
3
SELECT @@tx_isolation;
@@tx_isolation
REPEATABLE-READ
[5 Nov 2008 16:38] Elena Stepanova
It does not just switch to REPEATABLE-READ, it switches to the global transaction isolation level, whatever it is, in any direction. The example below shows a switch from REPEATABLE-READ to READ-UNCOMMITTED.

Moreover, the switch indeed changes the behavior -- see the comment at the end of the example.

# connect for the first time

select @@tx_isolation;
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

drop table if exists t;
create table t ( i int ) engine = innodb;

set global transaction isolation level read uncommitted;

# Need to reconnect to get the new global level work

exit;
# Connect again

select @@tx_isolation;
# +------------------+
# | @@tx_isolation   |
# +------------------+
# | READ-UNCOMMITTED |
# +------------------+

set transaction isolation level repeatable read;

select @@tx_isolation;
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

begin;

select @@tx_isolation;
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

select * from t;
Empty set (0.00 sec)

select @@tx_isolation;
# +------------------+
# | @@tx_isolation   |
# +------------------+
# | READ-UNCOMMITTED |
# +------------------+

# It indeed behaves as uncommitted read. If you insert something into t 
# from another connection with autocommit = 0, you'll see the result right away

select * from t;
# +------+
# | i    |
# +------+
# |    1 |
# +------+

# etc.
[6 Nov 2008 23:10] Elena Stepanova
Even more precisely, it switches to SESSION isolation level -- e.g. if global transaction isolation level is READ-COMMITTED, session level is READ-UNCOMMITTED, and transaction level is set REPEATABLE-READ, in the examples above the level will switch to READ-UNCOMMITTED.
[6 Feb 2009 6:38] Christopher Chan-Nui
This bug still exists in 5.1.31 for solaris sparc 32 and 64 bit.  The severity of this bug should be increased.  A transaction relying on serializable semantics could cause data corruption if the isolation level is unintentionally decreased.
[6 Feb 2009 17:30] Heikki Tuuri
Assigning this bug to Vasil.

Vasil, please find out what is wrong and how to fix it.

--Heikki
[27 Jun 2009 11:07] Petr Zelenka
Is there any progress for this bug? It is pretty ugly bug.
[27 Jun 2009 11:08] Petr Zelenka
This problem is also in 5.1 version.
[5 Sep 2009 20:20] Petr Zelenka
Is there any progress in this bug. It is critical bug for many applications. Our application cannot be migrate to 5.1 from 5.0 because of this bug.
[18 Sep 2009 11:22] Susanne Ebrecht
Bug #47436 is set as duplicate of this bug here
[18 Sep 2009 11:25] Pavel Pushkarev
Wow, the bug's old.

It is still there in 5.1.38 and it switches to the default isolation level from all other levels (in my case it was serializable).

If possible, please set the severity to serous at least: the bug is serous as it makes all isolation level using environments impossible.
[23 Sep 2009 18:26] Kai Voigt
We just ran into this bug in class with 5.1.38 on Windows.
[26 Oct 2009 11:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88132

3197 Magne Mahre	2009-10-26
      Bug #20837 Apparent change of isolation level during transaction
      
      SET TRANSACTION ISOLATION LEVEL is used to temporarily set
      the trans.iso.level for the next transaction.  After the
      transaction, the iso.level is (re-)set to value of the session
      variable 'tx_isolation'.
      
      This bug is caused by resetting the transaction isolation
      even on a statement commit.   The fix is to check if this
      is a full transaction commit/rollback, and only reset the
      value if so.
[26 Nov 2009 9:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91748

3221 Magne Mahre	2009-11-26
      Bug #20837 Apparent change of isolation level during transaction
            
      SET TRANSACTION ISOLATION LEVEL is used to temporarily set
      the trans.iso.level for the next transaction.  After the
      transaction, the iso.level is (re-)set to value of the 
      session variable 'tx_isolation'.
      
      The bug is caused by setting the thd->variables.tx_isolation 
      field to the value of the session variable upon each
      statement commit.  It should only be set at the end of the
      full transaction.
      
      The fix has been to remove the setting of the variable in
      ha_autocommit_or_rollback if we're in a transaction, as it 
      will be correctly set in  either ha_rollback or 
      ha_commit_one_phase.  
      
      If, on the other hand, we're in  autocommit mode, tx_isolation 
      will be explicitly set here.
     @ mysql-test/t/innodb_mysql.test
        "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" sets
        the trans.isolation for the next transaction.  We test
        that @@tx_isolation keeps this value during the transaction,
        and is only reset back to the session value at the end
        of the transaction.
[26 Nov 2009 22:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91850

3226 Magne Mahre	2009-11-26
      Bug #20837 Apparent change of isolation level during transaction
            
      SET TRANSACTION ISOLATION LEVEL is used to temporarily set
      the trans.iso.level for the next transaction.  After the
      transaction, the iso.level is (re-)set to value of the 
      session variable 'tx_isolation'.
      
      The bug is caused by setting the thd->variables.tx_isolation 
      field to the value of the session variable upon each
      statement commit.  It should only be set at the end of the
      full transaction.
      
      The fix has been to remove the setting of the variable in
      ha_autocommit_or_rollback if we're in a transaction, as it 
      will be correctly set in  either ha_rollback or 
      ha_commit_one_phase.  
      
      If, on the other hand, we're in  autocommit mode, tx_isolation 
      will be explicitly set here.
     @ mysql-test/t/innodb_mysql.test
        "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" sets
        the trans.isolation for the next transaction.  We test
        that @@tx_isolation keeps this value during the transaction,
        and is only reset back to the session value at the end
        of the transaction.
[30 Nov 2009 11:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92056

3221 Magne Mahre	2009-11-30
      Bug #20837 Apparent change of isolation level during transaction
            
      SET TRANSACTION ISOLATION LEVEL is used to temporarily set
      the trans.iso.level for the next transaction.  After the
      transaction, the iso.level is (re-)set to value of the 
      session variable 'tx_isolation'.
      
      The bug is caused by setting the thd->variables.tx_isolation 
      field to the value of the session variable upon each
      statement commit.  It should only be set at the end of the
      full transaction.
      
      The fix has been to remove the setting of the variable in
      ha_autocommit_or_rollback if we're in a transaction, as it 
      will be correctly set in  either ha_rollback or 
      ha_commit_one_phase.  
      
      If, on the other hand, we're in  autocommit mode, tx_isolation 
      will be explicitly set here.
     @ mysql-test/t/innodb_mysql.test
        "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" sets
        the trans.isolation for the next transaction.  We test
        that @@tx_isolation keeps this value during the transaction,
        and is only reset back to the session value at the end
        of the transaction.
[1 Dec 2009 9:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92227

3232 Georgi Kodinov	2009-12-01
      Revert of the push of bug #20837 due to failing regression tests.
[2 Dec 2009 8:05] Bugs System
Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:joro@sun.com-20091201091951-dnizjsxz6ec4kvji) (merge vers: 5.1.42) (pib:13)
[16 Dec 2009 8:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:46] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091203204606-yot52u2niq1et8ys) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:53] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[12 Mar 2010 14:14] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:30] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:46] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[17 Mar 2010 19:15] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103632

3406 Magne Mahre	2010-03-17
      Bug#20837 Apparent change of isolation level during transaction
      
      SET TRANSACTION ISOLATION LEVEL <level> is used to
      temporarily change the transaction isolation level.  The
      life-time of the change is the duration of the _next_
      transaction.
      
      The bug is caused by setting the thd->variables.tx_isolation 
      field to the value of the session variable upon each
      statement commit.  It should only be set at the end of the
      full transaction.
      
      Since the SET TRANS ISO LEVEL statement itself causes
      an implicit commit after execution, 
      thd->set_isolation_statement was introduced to indicate 
      that the currently executing statement is a 
      "SET TRANSACTION ISOLATION LEVEL xxx". This enables
      us to determine that we should skip resetting back the 
      thd->tx_isolation immidiately.
[1 May 2010 10:30] Konstantin Osipov
See also Bug#46932 "SET TRANSACTION ISOLATION LEVEL documentation wrong", which confirms that SET TRANSACTION ISOLATION LEVEL statement is intended to change isolation level of the next (not started) transaction.
[5 May 2010 22:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107583

3010 Konstantin Osipov	2010-05-06
      Bug#20837: commit for work in progress to ease a merge.
[7 May 2010 16:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/107774

3015 Konstantin Osipov	2010-05-07
      Draft patch that fixes and a sketches test cases for:
      Bug#20837 Apparent change of isolation level during transaction,
      Bug#46527 COMMIT AND CHAIN RELEASE does not make sense,
      Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
      preserve the isolation level
      Bug#53346 completion_type has strange effect in a stored 
      procedure/prepared statement
      
      Make thd->tx_isolation mean strictly "current transaction 
      isolation level"
      Make thd->variables.tx_isolation mean "current session isolation
      level".
      The current transaction isolation level is now established
      at transaction start. If there was a SET TRANSACTION
      ISOLATION LEVEL statement, the value is taken from it.
      Otherwise, the session value is used.
      A change in a session value, made while a transaction is active,
      whereas still allowed, no longer has any effect on the
      current transaction isolation level. This is an incompatible
      change.
      A change in a session isolation level, made while there is
      no active transaction, overrides SET TRANSACTION statement,
      if there was any.
      Changed the impelmentation to not look at @@session.completion_type
      in the parser, and thus fixed Bug#53346.
      Changed the parser to not allow AND NO CHAIN RELEASE,
      and thus fixed Bug#46527.
      Changed the transaction API to take the current transaction
      isolation level into account:
      - BEGIN/COMMIT now do preserve the current transaction
      isolation level if chaining is on.
      - implicit commit, XA COMMIT or XA ROLLBACK or autocommit don't.
[7 May 2010 16:37] Konstantin Osipov
Sent a review over email to commits@
[7 May 2010 16:39] Konstantin Osipov
This is too big to go into 5.1, and countains some incompatible changes. (The nature of the request is to make an incompatible change). Please re-triage and drop sr51mru tag.
[7 Jun 2010 21:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/110406

3046 Magne Mahre	2010-06-07 [merge]
      Bug#20837 Apparent change of isolation level during transaction
      Bug#46527 COMMIT AND CHAIN RELEASE does not make sense
      Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
                preserve the isolation level
      Bug#53346 completion_type has strange effect in a stored 
                procedure/prepared statement
      
      Added test cases to verify the expected behaviour of :
       SET SESSION TRANSACTION ISOLATION LEVEL, 
       SET TRANSACTION ISOLATION LEVEL,
       @@completion_type,
       COMMIT AND CHAIN,
       ROLLBACK AND CHAIN
       ..and some combinations of the above
[8 Jun 2010 12:04] Jon Olav Hauglid
Additional test coverage approved with minor comments.
[8 Jun 2010 17:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/110534

3047 Magne Mahre	2010-06-08 [merge]
      Bug#20837 Apparent change of isolation level during transaction
      Bug#46527 COMMIT AND CHAIN RELEASE does not make sense
      Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
                preserve the isolation level
      Bug#53346 completion_type has strange effect in a stored 
                procedure/prepared statement
      
      Added test cases to verify the expected behaviour of :
       SET SESSION TRANSACTION ISOLATION LEVEL, 
       SET TRANSACTION ISOLATION LEVEL,
       @@completion_type,
       COMMIT AND CHAIN,
       ROLLBACK AND CHAIN
       ..and some combinations of the above
[8 Jun 2010 17:49] Magne Mæhre
Pushed to mysql-trunk-runtime (r.3047)
[9 Jun 2010 8:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/110582

3049 Magne Mahre	2010-06-09
      Post-commit fixes after the push for Bug#20837 et. al.
     @ mysql-test/suite/innodb/r/innodb_information_schema.result
        The transaction is in REPEATABLE READ, since the
        iso level was changed to SERIALIZABLE only for the 
        previous trx.
     @ mysql-test/suite/innodb/t/innodb-semi-consistent.test
        'commit' was missing from the original patch
     @ mysql-test/t/parser.test
        Moved the test for Bug#46527 to parser_not_embedded,
        since "disconnect default" isn't allowed with
        the embedded server.
     @ mysql-test/t/parser_not_embedded.test
        Moved the test for Bug#46527 to parser_not_embedded,
        since "disconnect default" isn't allowed with
        the embedded server.
[22 Jun 2010 13:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)
[22 Jun 2010 13:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)
[30 Jul 2010 2:34] Paul DuBois
Noted in 5.5.5 changelog.

After SET TRANSACTION ISOLATION LEVEL to set the isolation level for
the next transaction, the session value of the tx_isolation system
variable could appear to change after completion of statements within
the transaction to the transaction isolation level. Now the current
transaction isolation level is now established at transaction start.
If there was a SET TRANSACTION ISOLATION LEVEL statement, the value
is taken from it. Otherwise, the session tx_isolation value is used.
A change in the session value while a transaction is active is still
allowed, but no longer affects the current transaction isolation
level. This is an incompatible change. A change in the session
isolation level made while there is no active transaction overrides a
SET TRANSACTION ISOLATION LEVEL statement, if there was any.