MySQL Bugs: #46527: COMMIT AND CHAIN RELEASE does not make sense

Bug #46527	COMMIT AND CHAIN RELEASE does not make sense
Submitted:	3 Aug 2009 11:10	Modified:	29 Jul 2010 18:31
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any
Tags:	5.0, 5.1

Description:
COMMIT AND CHAIN will commit the current transaction and immediately open a new transaction.

COMMIT RELEASE will commit the current transaction and then terminate the session.

COMMIT AND CHAIN RELEASE commits the current transaction, opens a new transaction and then terminates the session, presumably with an open transaction.

The behaviour of the latter command makes no sense and should cause an error.

The same applies to ROLLBACK.

How to repeat:
SET AUTOCOMMIT=0;
SELECT 1;
COMMIT AND CHAIN RELEASE;
SELECT 1; -- opens a new session.

Suggested fix:
Issue an error message if the user specifies both AND CHAIN and RELEASE.

Thank you for the problem report.

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.

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

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

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)

Noted in 5.5.5 changelog.

For the COMMIT and ROLLBACK statements, the AND CHAIN and RELEASE
modifiers should be mutually exclusive, but the parser allowed both
to be specified.