| Bug #53343 | completion_type=1, COMMIT/ROLLBACK AND CHAIN don't preserve the isolation level | ||
|---|---|---|---|
| Submitted: | 1 May 2010 11:39 | Modified: | 22 Jul 2010 15:53 |
| Reporter: | Konstantin Osipov (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0+ | OS: | Any |
| Assigned to: | Magne Mæhre | CPU Architecture: | Any |
[1 May 2010 17:11]
Valeriy Kravchuk
Verified just as described: 77-52-4-109:mysql-test openxs$ ./mtr bug53343 Logging: ./mtr bug53343 MySQL Version 5.0.91 Using ndbcluster when necessary, mysqld supports it Setting mysqld to support SSL connections Binaries are debug compiled mysql-test-run: WARNING: Could not find all required ndb binaries, all ndb tests will fail, use --skip-ndbcluster to skip testing it. Using MTR_BUILD_THREAD = 0 Using MASTER_MYPORT = 9306 Using MASTER_MYPORT1 = 9307 Using SLAVE_MYPORT = 9308 Using SLAVE_MYPORT1 = 9309 Using SLAVE_MYPORT2 = 9310 Using NDBCLUSTER_PORT = 9311 Using IM_PORT = 9313 Using IM_MYSQLD1_PORT = 9314 Using IM_MYSQLD2_PORT = 9315 Killing Possible Leftover Processes Removing Stale Files Creating Directories Installing Master Database ======================================================= Starting Tests in the 'main' suite TEST RESULT TIME (ms) ------------------------------------------------------- bug53343 [ fail ] --- /Users/openxs/dbs/5.0/mysql-test/r/bug53343.result 2010-05-01 20:08:47.000000000 +0300 +++ /Users/openxs/dbs/5.0/mysql-test/r/bug53343.reject 2010-05-01 20:09:14.000000000 +0300 @@ -0,0 +1,65 @@ +drop table if exists t1; +create table t1 (a varchar(255)) engine=innodb; +set @@autocommit=0; +insert into t1 (a) values ("original"); +set @@session.tx_isolation="read-committed"; +select @@session.tx_isolation; +@@session.tx_isolation +READ-COMMITTED +begin; +select * from t1; +a +original +# --> connection con1 +set @@autocommit=1; +update t1 set a="old"; +# --> connection default +# +# We expect to read the "new" value +# of the column, since the isolation level +# is set to read-committed. +# +select * from t1; +a +old +# Now update the session tx_isolation. This should only +# have effect on the next transaction, and only if neither +# CHAIN is specified, nor completion_type is set to 1. +set @@session.tx_isolation="repeatable-read"; +# Expect the previous statement to have no effect because of +# the CHAIN. +commit and chain; +select * from t1; +a +old +# --> connection con1 +update t1 set a="new"; +# --> connection default +# Expect to see the new value, because the isolation +# level of the prevoius transaction is read committed. +# Ooops, a bug, seeing the old value! +select * from t1; +a +old +# Now test the completion_type variable. +set @@session.completion_type=1; +commit; +select * from t1; +a +new +# --> connection con1 +update t1 set a="newest"; +# connection default +# Expect to see the newest value, because the isolation +# level of the prevoius transaction is read committed. +# Ooops, a bug, seeing the old value! +select * from t1; +a +new +commit; +# @todo: write tests for rollback and chain and rollback. +# Clean up. +# --> connection con1 +# --> connection default +drop table t1; +set @@session.tx_isolation=default;
[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 Jun 2010 21:38]
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 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
[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)
[22 Jul 2010 15:53]
Paul DuBois
Noted in 5.5.5 changelog. The AND CHAIN option for COMMIT and ROLLBACK failed to preserve the current transaction isolation level. Setting completion_type to 1 also failed to do so.
Description: COMMIT AND CHAIN, ROLLBACK AND CHAIN, and their equivalent COMMIT/ROLLBACK statements when completion_type=1, are documented to preserve the current transaction's isolation level. Quoting the manual: "The AND CHAIN clause causes a new transaction to begin as soon as the current one ends, and the new transaction has the same isolation level as the just-terminated transaction." This is, however, not the case, the isolation level is reset to the session isolation level. Since it's impossible to reliably find out the current transaction's isolation level, I shall use a test case that exercises different InnoDB visibility guarantees at different isolation levels (please see the mysql-test-run language test case below). How to repeat: Test case: -- source include/have_innodb.inc --disable_warnings drop table if exists t1; --enable_warnings create table t1 (a varchar(255)) engine=innodb; set @@autocommit=0; insert into t1 (a) values ("original"); set @@session.tx_isolation="read-committed"; select @@session.tx_isolation; begin; select * from t1; connect (con1, localhost, root,,); --echo # --> connection con1 connection con1; set @@autocommit=1; update t1 set a="old"; --echo # --> connection default connection default; --echo # --echo # We expect to read the "new" value --echo # of the column, since the isolation level --echo # is set to read-committed. --echo # select * from t1; --echo # Now update the session tx_isolation. This should only --echo # have effect on the next transaction, and only if neither --echo # CHAIN is specified, nor completion_type is set to 1. set @@session.tx_isolation="repeatable-read"; --echo # Expect the previous statement to have no effect because of --echo # the CHAIN. commit and chain; select * from t1; --echo # --> connection con1 connection con1; update t1 set a="new"; --echo # --> connection default connection default; --echo # Expect to see the new value, because the isolation --echo # level of the prevoius transaction is read committed. --echo # Ooops, a bug, seeing the old value! select * from t1; --echo # Now test the completion_type variable. set @@session.completion_type=1; commit; select * from t1; --echo # --> connection con1 connection con1; update t1 set a="newest"; --echo # connection default connection default; --echo # Expect to see the newest value, because the isolation --echo # level of the prevoius transaction is read committed. --echo # Ooops, a bug, seeing the old value! select * from t1; commit; --echo # @todo: write tests for rollback and chain and rollback. --echo # Clean up. --echo # --> connection con1 connection con1; disconnect con1; --source include/wait_until_disconnected.inc --echo # --> connection default connection default; drop table t1; set @@session.tx_isolation=default; Test output (5.5, but should be repeatable in all versions): +drop table if exists t1; +create table t1 (a varchar(255)) engine=innodb; +set @@autocommit=0; +insert into t1 (a) values ("original"); +set @@session.tx_isolation="read-committed"; +select @@session.tx_isolation; +@@session.tx_isolation +READ-COMMITTED +begin; +select * from t1; +a +original +# --> connection con1 +set @@autocommit=1; +update t1 set a="old"; +# --> connection default +# +# We expect to read the "new" value +# of the column, since the isolation level +# is set to read-committed. +# +select * from t1; +a +old +# Now update the session tx_isolation. This should only +# have effect on the next transaction, and only if neither +# CHAIN is specified, nor completion_type is set to 1. +set @@session.tx_isolation="repeatable-read"; +# Expect the previous statement to have no effect because of +# the CHAIN. +commit and chain; +select * from t1; +a +old +# --> connection con1 +update t1 set a="new"; +# --> connection default +# Expect to see the new value, because the isolation +# level of the prevoius transaction is read committed. +# Ooops, a bug, seeing the old value! +select * from t1; +a +old +# Now test the completion_type variable. +set @@session.completion_type=1; +commit; +select * from t1; +a +new +# --> connection con1 +update t1 set a="newest"; +# connection default +# Expect to see the newest value, because the isolation +# level of the prevoius transaction is read committed. +# Ooops, a bug, seeing the old value! +select * from t1; +a +new +commit; +# @todo: write tests for rollback and chain and rollback. +# Clean up. +# --> connection con1 +# --> connection default +drop table t1; +set @@session.tx_isolation=default; Suggested fix: Make it work.