| Bug #20837 | Apparent change of isolation level during transaction | ||
|---|---|---|---|
| Submitted: | 4 Jul 2006 1:28 | Modified: | 1 Dec 10:21 |
| Reporter: | Peter Gulutzan | ||
| Status: | In progress | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.1 | OS: | Linux (SUSE 10.0 / 64-bit) |
| Assigned to: | Magne Mæhre | Target Version: | 5.1+ |
| Tags: | Contribution | ||
| Triage: | Triaged: D2 (Serious) | ||
[4 Jul 2006 1:28]
Peter Gulutzan
[4 Jul 2006 10: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 9:30]
Sergei Golubchik
patch: http://lists.mysql.com/internals/35146
[19 Oct 2008 9: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 9: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 17: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.
[7 Nov 2008 0: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 7: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 18:30]
Heikki Tuuri
Assigning this bug to Vasil. Vasil, please find out what is wrong and how to fix it. --Heikki
[27 Jun 13:07]
Petr Zelenka
Is there any progress for this bug? It is pretty ugly bug.
[27 Jun 13:08]
Petr Zelenka
This problem is also in 5.1 version.
[5 Sep 22: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 13:22]
Susanne Ebrecht
Bug #47436 is set as duplicate of this bug here
[18 Sep 13: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 20:26]
Kai Voigt
We just ran into this bug in class with 5.1.38 on Windows.
[26 Oct 12: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 10: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 23: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 12: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 10: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 9: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)
