MySQL Bugs: #19206: Engine hangs trying to read from table that was locked for writing

Bug #19206	Engine hangs trying to read from table that was locked for writing
Submitted:	19 Apr 2006 22:02	Modified:	2 May 2006 21:59
Reporter:	Nikolay Grishakin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
In a test case below I’m using two different connections con1 and con2. Con1 locks the table for writing and con2 tries to select from the same table. 
Test case hangs and never finishes. I suspect a deadlock. Happense with DD tables as well. Test hangs on:  
     connection con2;
     SELECT * FROM test.t1; # Hangs here

If you commnet out SELECT statment and leave only  INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3"); we would expect error message to be returned but test hangs as well.

-- source include/have_ndb.inc

 connect (con1,localhost,root,,);
 connect (con2,localhost,root,,);

 CREATE LOGFILE GROUP lg
 ADD UNDOFILE './lg_group/undofile.dat'
 INITIAL_SIZE 16M
 UNDO_BUFFER_SIZE = 1M
 ENGINE=NDB;

 CREATE TABLESPACE ts1
 ADD DATAFILE './table_space1/datafile.dat'
 USE LOGFILE GROUP lg
 INITIAL_SIZE 12M
 ENGINE NDB;

--disable_warnings
 drop table if exists test.t1;
--enable_warnings

 CREATE TABLE test.t1 (a1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a2 VARCHAR(256), a3 BLOB) TABLESPACE ts1 STORAGE DISK  ENGINE=NDB;

 connection con1;
 LOCK TABLES test.t1 write;
 INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1");
 INSERT INTO test.t1 VALUES(NULL, "aaaaa2", "bbbbb2");
 SELECT * FROM test.t1 ORDER BY a1;
 
 connection con2;
 SELECT * FROM test.t1; # Hangs here
 INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3"); # and here  
 
 connection con1;
 UNLOCK TABLES;

 connection con2;
 INSERT INTO test.t1 VALUES(NULL, "aaaaa3", "bbbbb3"); 
 SELECT * FROM test.t1;
 DELETE FROM test.t1;
 DROP TABLE test.t1;

# connection defualt;

 ALTER TABLESPACE ts1
 DROP DATAFILE './table_space1/datafile.dat'
 ENGINE=NDB;
 DROP TABLESPACE ts1 ENGINE=NDB;

 DROP LOGFILE GROUP lg
 ENGINE=NDB;

How to repeat:
see above

1) Why category Partitioning
2) Have you tested on myisam/innodb ?
3) I would actually expect the behaviour that you describe,
    but I havent read manual on "lock table" very closely...have you?

This is from manual: http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

"If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, only the thread holding the lock can write to the table. Other threads are blocked from doing so until the lock has been released."

The manual says "If a thread obtains a WRITE lock on a table, only
the thread holding the lock can write to the table. Other threads are blocked
from doing so until the lock has been released." By "doing so" I assume doing writes and reads are permited. 

I changed the table to InnoDB and it hangs too. Moving bug category to "Server"

I interpret it as the behaviour is correct.
I.e while having a write lock on table, no other thread can read/write from it.
  This is sometimes also called exclusive lock.
They "block" until unlocked, I interpret block as hang in this context.

But that's my interpretation :-)

The manual should say that other threads cannot write or read from the table when the table is WRITE-locked.

Why no error code is returned when table is locked by another thread? There is no option for wait or no-wait lock available. So why second thread should retry forever?

If I use read lock on table I'm getting appropriate error message back when someone else trys to insert into the same table:
"query 'INSERT INTO test.t1 VALUES(NULL, "aaaaa1", "bbbbb1")' failed: 1099: Table 't1' was locked with a READ lock and can't be updated"

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated the manual as indicated by the final comment.