Description:
Contrary to the common sense expectation, UNLOCK TABLES does not release
transactional locks.
I.e.:
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t1 (a int) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t1 in share mode;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
-- in another connection:
mysql> drop table t1;
-- (hangs)
If you issue COMMIT in the first connection, DROP TABLE goes through.
Strictly speaking, this is not a bug, because WL#3561 specification
says in one place:
--quote
UNLOCK TABLE works exactly as before, no changes.
--end quote
However, the specification contradicts itself, because it says
in another place:
--quote
Current behaviour:
UNLOCK TABLE automatically commits a transaction
--end quote
The assumption made in the spec, that UNLOCK TABLES automatically commits a transaction is incorrect. In reality, this is only done if there were LOCK TABLE READ|WRITE locks (i.e. non-transactional locks). UNLOCK TABLES is also used to release locks taken for FLUSH TABLES WITH READ LOCK, and for this statement, it does not commit a transaction (this is documented).
To sum up, the only reliable way to release transactional locks in WL#3561
is COMMIT/ROLLBACK.
How to repeat:
drop table t1;
create table t1 (a int) engine=innodb;
begin;
lock table t1 in share mode;
unlock tables;
-- in another connection:
drop table t1;
-- (hangs)
Suggested fix:
At least fix the test case to not issue UNLOCK TABLES all over the place, they are useless.
Then there are two choices:
- make sure UNLOCK TABLES commits a transaction if there are transactional locks. This is dangerous, I would not recommend it, since you can take transactional locks after FLUSH TABLES WITH READ LOCK, and UNLOCK TABLES is already documented to *not* commit a transaction if done after FLUSH TABLES WITH READ LCOK.
- document that UNLOCK TABLES does not release transactional locks, and COMMIT|ROLLBACK should be used instead.
A side note on the second approach: the whole idea with implicit lock conversion sounds questionable, since to release transactional and non-transactional locks a user has to use different syntax. People will be forced to write COMMIT; UNLOCK TABLES; to make sure all locks are released, even those that were implicitly converted, and this looks ugly.
