MySQL Bugs: #62641: A simple SELECT relock the table what was locked with LOCK TABLES WRITE.

Bug #62641	A simple SELECT relock the table what was locked with LOCK TABLES WRITE.
Submitted:	6 Oct 2011 14:05	Modified:	26 Dec 2011 19:00
Reporter:	Bela Nagy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Windows (7 64bit)
Assigned to:		CPU Architecture:	Any

Description:
The following situation is on InnoDB table with set autocommit=0.
I locked test1 table with LOCK TABLES table1 WRITE command and unlock it immediately (UNLOCK TABLES). Then I executed a SELECT on locked and unlocked table. 
After this I open a new session with same user on same database. 
I tried to lock the same table but it waiting until I execute the COMMIT command in the first session.
It is strange why lock the SELECT again the table after the UNLOCK TABLES?

How to repeat:
Open the first session:
SET AUTOCOMMIT = 0;
USE test;
LOCK TABLES test1 WRITE;
UNLOCK TABLES; 
SELECT *
  FROM test1
 WHERE test_col1 = 1;

Open the second session:
SET AUTOCOMMIT = 0;
USE test;
LOCK TABLES test1 WRITE;
-- the lock is waiting

In the first session:
COMMIT;

In the second session:
The lock executed immediately when the COMMIT was executed in the first session.

Backup of test database

Attachment: test.sql (application/octet-stream, text), 994 bytes.

lock and unlock the test1 table then execute a simple select.

Attachment: First_session_1.png (image/png, text), 64.70 KiB.

Lock waiting in the second session.

Attachment: First_and_second_session_lock.png (image/png, text), 105.25 KiB.

Execute COMMIT in the first session.

Attachment: First_session_commit.png (image/png, text), 108.44 KiB.

I cannot reproduce this with neither 5.1.59 nor 5.5.16 (using a table I have)

Peter
(not a MySQL person)

But I was able to get a similar problem reported here:
http://bugs.mysql.com/bug.php?id=62646

For me it is easily repeatable with InnoDB table, but I think that http://dev.mysql.com/doc/refman/5.5/en/lock-tables-and-transactions.html explains the problem. InnoDB internal table level lock is not released until COMMIT.

I placed one – one COMMIT before and after UNLOCK TABLES and I have same lock problem in the second session:

SET AUTOCOMMIT = 0;
USE test;
LOCK TABLES test1 WRITE;
COMMIT;
UNLOCK TABLES; 
COMMIT;
SELECT *
  FROM test1
 WHERE test_col1 = 1;

I don’t have this problem in 5.1 version.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have SET AUTOCOMMIT = 0; in the beginning of the first session. This means SELECT opens new transaction.