| 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: | |
| 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
[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.
