Bug #46163 KILL QUERY causes implicit COMMIT
Submitted: 14 Jul 2009 9:10 Modified: 24 Jul 2009 14:20
Reporter: Philip Stoev Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0, 5.1,5.4 OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: regression

[14 Jul 2009 9:10] Philip Stoev 
Description:
When KILL QUERY is used against a query in a transaction, the transaction will be committed. This is definitely not desireable. Either the transaction should remain as it is and wait for an explicit COMMIT or ROLLBACK from the user, or (less ideally) there should be an explicit ROLLBACK.

Since LOCK TABLE does not have a timeout, KILL QUERY is the only way to exit from a LOCK TABLE deadlock. Therefore, it is important that KILL QUERY works as expected and allows one to recover from the situation.

How to repeat:
Connection1:
 LOCK TABLE mysql.user WRITE;

Connection2:
 CREATE TABLE mysql.t1 (f1 integer) ENGINE=INNODB;
 SET AUTOCOMMIT=OFF;
 START TRANSACTION;
 INSERT INTO t1 values (1);
 LOCK TABLE mysql.user WRITE;
 (connection 2 hangs here)

Connection 3:
 SHOW PROCESSLIST; 
 KILL QUERY 2;
 SELECT * FROM t1; 
 Connection 3 sees the insert from Connection 2
[14 Jul 2009 9:11] Philip Stoev 
Also see bug #45309, the fix for which does not appear to solve this bug.
[14 Jul 2009 10:15] Sveta Smirnova 
Thank you for the report.

Verified as described. Version 4.1 is not affected.
[24 Jul 2009 14:20] Davi Arnaut 
LOCK TABLES issues a implicit commit at the start of the statement as documented in:

http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html