MySQL Bugs: #44277: Abort (Ctrl+C) via mysql client can't free some locks in transaction

Bug #44277	Abort (Ctrl+C) via mysql client can't free some locks in transaction
Submitted:	15 Apr 2009 2:56	Modified:	15 Apr 2009 10:27
Reporter:	rancpine cui	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.32	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	FREE, lock

Description:
Abort (Ctrl+C) via mysql client can't free some locks in transaction.
It seems that mysql will keep the thread running for a while( with table locks ) even I close the connection.

How to repeat:
S1. create 3 connections(Conn1, Conn2, Conn3) to mysql 5.1.32
S2. at Conn1, run:
    use test;
    create table t1 ( c int primary key ) engine=innodb;
    start transaction;
    insert into t1 values(1);
S3. at Conn2, run:
    use test;
    insert into t1 values(2),(1);  -- hang
S4. at Conn3, run:
    use test;
    drop table t1;              -- hang
S5. at Conn1, run:
    insert into t1 values(2);   -- hang
S6. at ALL connections, run:
    Ctrl + C
    -- at Conn1 & Conn3, it outputs:
    -- Query aborted by Ctrl+C
    -- ERROR 1317 (70100): Query execution was interrupted
    -- at Conn2 however, continue hang
S7. at Conn2, run:
    Ctrl + C  -- mysql client quit now
S8. at Conn3, run:
    show processlist;
    mysql> show processlist;
/*  resulst:  ( why there are 3 threads? )
+----+------+-----------+------+---------+------+--------+------------------------------+
| Id | User | Host      | db   | Command | Time | State  | Info                         |
+----+------+-----------+------+---------+------+--------+------------------------------+
| 33 | root | localhost | test | Query   |    0 | NULL   | show processlist             | 
| 34 | root | localhost | test | Sleep   |   15 |        | NULL                         | 
| 35 | root | localhost | test | Query   |   29 | update | insert into t1 values(2),(1) | 
+----+------+-----------+------+---------+------+--------+------------------------------+
*/
S9. create a new connection Conn4
S10. at Conn3, run:
    show processlist:
/*  resulst:  ( a new guy comes in, which is correct )
+----+------+-----------+------+---------+------+--------+------------------------------+
| Id | User | Host      | db   | Command | Time | State  | Info                         |
+----+------+-----------+------+---------+------+--------+------------------------------+
| 33 | root | localhost | test | Query   |    0 | NULL   | show processlist             | 
| 34 | root | localhost | test | Sleep   |   65 |        | NULL                         | 
| 35 | root | localhost | test | Query   |  79 | update | insert into t1 values(2),(1) | 
| 39 | root | localhost | NULL | Sleep   |    4 |        | NULL                         | 
+----+------+-----------+------+---------+------+--------+------------------------------+
*/
S11. at Conn4, run:
    use test;   -- hang
S12. at Conn3, run:
    show processlist;
/*  resulst:  ( the new guy is hanged now )
+----+------+-----------+------+------------+------+-------------------+------------------------------+
| Id | User | Host      | db   | Command    | Time | State             | Info                         |
+----+------+-----------+------+------------+------+-------------------+------------------------------+
| 33 | root | localhost | test | Query      |    0 | NULL              | show processlist             | 
| 34 | root | localhost | test | Sleep      |   85 |                   | NULL                         | 
| 35 | root | localhost | test | Query      |   99 | update            | insert into t1 values(2),(1) | 
| 39 | root | localhost | test | Field List |    7 | Waiting for table |                              | 
+----+------+-----------+------+------------+------+-------------------+------------------------------+
*/

Suggested fix:
don't know how to fix

May be duplicate with Bug #22876.
I just have the following questions:
1. why Ctrl+C & reconnect can't kill the thread
2. It's a little amazing that a thread locks the WHOLE database

repeatable at mysql 5.1.22

Thank you for the report.

Nobody promised what just abort of query in mysql command line client would rollback or commit transaction. So I'd say this is not a bug.

But in version 6.0 server detects potential deadlock when conn1 issues second insert, so this probably is more what you expect.

Thank you for your reply.
I wonder whether mysql will do an implicit rollback if client lose the connection with mysql server during a transaction.

In addition, I still don't think it is reasonable that new connection(Conn 4) is hanged beause of Conn2 when execute just "use test;".

Thank you for the feedback.

Quitting mysql command line client should rollback transaction. But if I understood correctly you don't quit conn1 and conn3.

Yes. I've only quit Conn2.
After that, I create a new connection Conn4 and run:
         use test;
It hangs.

In my.cnf, I have:
innodb_lock_wait_timeout = 120
It seems that after the query at Conn2 run util 120 seconds,the thread is quit and mysql return to normal state.

Thank you for your attention :)

Thank you for the answer.

I can not repeat problem with conn4.

my.cnf

Attachment: my.txt (text/plain), 20.27 KiB.

I build mysql 5.1.32 and 5.1.22-debug from source with:
./configure --prefix=/home/work/mysql --with-charset=utf8
--with-extra-charset=utf8 --with-collation=utf8_unicode_ci --enable-local-infile --with-plugins=innobase,myisam,ndbcluster,heap

I will try to give a more clear description about the problem.
Thank you for your help.