Bug #29154 LOCK TABLES is not atomic when >1 InnoDB tables are locked

Description:
LOCK TABLE does NOT release table level lock on InnoDB level when it can not set locks for all the tables listed. As a result, MySQL table level locks and InnoDB table level locks are not in sync. See "How to repeat" for the details. 

Additional notes:

Moment #1. There are no table level locks reportered by mysqladmin debug.

Moment #2. There are no table level locks reportered by mysqladmin debug. SHOW INNODB STATUS has:

------------
TRANSACTIONS
------------
Trx id counter 0 10501
Purge done for trx's n:o < 0 9986 undo n:o < 0 0
History list length 7
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 6383, OS thread id 1116924848
MySQL thread id 5, query id 198 localhost root
show innodb status
---TRANSACTION 0 10500, ACTIVE 8 sec, process no 6383, OS thread id 1116523440 i
nserting
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1, query id 135 localhost root update
insert into foo values(1)
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/foo` trx id 0 10500 lock mode IX waiting
------------------
---TRANSACTION 0 10499, ACTIVE 140 sec, process no 6383, OS thread id 1116724144

1 lock struct(s), heap size 320
MySQL thread id 2, query id 129 localhost root

So, there is a table level lock in InnoDB, although LOCK TABLES returned error.

Moment #3. SHOW INNODB STATUS has:

------------
TRANSACTIONS
------------
Trx id counter 0 10501
Purge done for trx's n:o < 0 9986 undo n:o < 0 0
History list length 7
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 6383, OS thread id 1116924848
MySQL thread id 5, query id 201 localhost root
show innodb status
---TRANSACTION 0 10500, ACTIVE 199 sec, process no 6383, OS thread id 1116523440
 inserting
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 320
MySQL thread id 1, query id 200 localhost root update
insert into foo values(1)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/foo` trx id 0 10500 lock mode IX waiting
------------------
---TRANSACTION 0 10499, ACTIVE 331 sec, process no 6383, OS thread id 1116724144

1 lock struct(s), heap size 320
MySQL thread id 2, query id 199 localhost root

So, even after explict UNLOCK TABLES we still have table level lock in InnoDB.

Moment #4. SHOW INNODB STATUS has:

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 6383, OS thread id 1116924848
MySQL thread id 5, query id 204 localhost root
show innodb status
---TRANSACTION 0 10501, ACTIVE 24 sec, process no 6383, OS thread id 1116724144
mysql tables in use 2, locked 2
2 lock struct(s), heap size 320
MySQL thread id 2, query id 202 localhost root
---TRANSACTION 0 10500, ACTIVE 377 sec, process no 6383, OS thread id 1116523440

mysql tables in use 1, locked 1
MySQL thread id 1, query id 203 localhost root Table lock
insert into foo values(1)

How to repeat:
connection 1)

create table foo (foo int) engine=innodb;
create table foo2 (foo int) engine=innodb;
set autocommit=0;
insert into foo2 values (1);

conneciton 2) 
set autocommit=0;
lock tables foo read, foo2 read;

This is Moment #1.

You will get (in 50 seconds):

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

connection 1) 
commit;
insert into foo values (1);

This is Moment #2.

You will get (in 50 seconds):
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This is a bug, as LOCK TABLES should either lock all tables or none.

connection 2)
unlock tables;

connection 1)
insert into foo values (1);

This is Moment #3.

You will get (in 50 seconds):
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This is a bug, as unlock tables should have released all locks set, if any, on InnoDB level also!

conneciton 2)
lock tables foo read, foo2 read; 
# success this time since the transaction is committed

connection 1) 
insert into foo values (1); 
# this time hangs as the table is locked but different behavior as it doesn't get a "lock wait" error and just waits for the table to be unlocked

This is Moment #4.

connection 2) 
unlock tables;
#allows above statement to proceed.

Suggested fix:
1. LOCK TABLES should be atomic, either work for all tables or does not set locks at all.

2. LOCK TABLES/UNLOCK TABLES should commit InnoDB transactions and release all locks set by session.

3. Anything that will make MySQL and InnoDB in sync WRT table locks in all cases.

Verified with latest 5.0.44-BK on Linux. This is a regression bug of a kind, as 4.1.19, for example, had no issues like this.

See also Bug#28870

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/29426

ChangeSet@1.2500, 2007-06-22 19:20:55+03:00, gkodinov@magare.gmz +4 -0
  Bug #29154: LOCK TABLES is not atomic when >1 InnoDB tables are locked
    LOCK TABLES takes a list of tables to lock. It may lock several 
    tables successfully and then encounter a tables that it can't lock, 
    e.g. because it's locked. In such case it needs to undo the locks on
    the already locked tables. And it does that. But it has also notified
    the relevant table storage engine handlers that they should lock.
    The only reliable way to ensure that the table handlers will give up
    their locks is to end the transaction. This is what UNLOCK TABLE 
    does : it ends the transaction if there were locked tables by LOCK 
    tables.
    It is possible to end the transaction when the lock fails in 
    LOCK TABLES because LOCK TABLES ends the transaction at its start 
    already. 
    Fixed by ending (again) the transaction when LOCK TABLES fails to
    lock a table.

The reason 4.1 does not have this regression is that in 4.1 InnoDB would always trigger a server-wide transaction rollback on lock timeout or deadlock.

In 5.0 it has become optional.

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/29480

ChangeSet@1.2500, 2007-06-25 10:44:52+03:00, gkodinov@magare.gmz +3 -0
  Bug #29154: LOCK TABLES is not atomic when >1 InnoDB tables are locked
    LOCK TABLES takes a list of tables to lock. It may lock several 
    tables successfully and then encounter a tables that it can't lock, 
    e.g. because it's locked. In such case it needs to undo the locks on
    the already locked tables. And it does that. But it has also notified
    the relevant table storage engine handlers that they should lock.
    The only reliable way to ensure that the table handlers will give up
    their locks is to end the transaction. This is what UNLOCK TABLE 
    does : it ends the transaction if there were locked tables by LOCK 
    tables.
    It is possible to end the transaction when the lock fails in 
    LOCK TABLES because LOCK TABLES ends the transaction at its start 
    already. 
    Fixed by ending (again) the transaction when LOCK TABLES fails to
    lock a table.

Pushed into 5.1.21-beta

Pushed into 5.0.46

Noted in 5.0.46, 5.1.21 changelogs.

LOCK TABLES was not atomic when more than one InnoDB tables were
locked.