MySQL Bugs: #40959: Doc request - docs on LOCK TABLE is 'insiderish'

Bug #40959	Doc request - docs on LOCK TABLE is 'insiderish'
Submitted:	23 Nov 2008 20:24	Modified:	9 Apr 2009 19:05
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
I do not find docs here 
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
.. clear enough!

Once you understand it is easy to understand. But documentation should *promote understand* and not only *repeat understanding*!

The problem is this short paasage "LOCK TABLES acquires table locks for the current thread". BOOM!  Are users supposed to read the thoughts of the person who wrote this?

This does not explicitly make it clear that 

1) tables not listed as parameters to LOCK TABLES will be locked for current session but not for other sessions! 

2) LOCK WRITE allows *only* current session (any kind of) access - LOCK READ allows read for *all* sessions.

How to repeat:
-- server is 5.0.67
-- 1st connection

create database lt;
use lt;
create table tb1 (id int);
create table tb2 (id int);
create table tb3 (id int);
lock table tb1 write, tb2 read;

select * from tb1; -- success

select * from tb2; -- success

select * from tb3;
/* first thing I do not find documented properly here
tb3 was not listed in LOCK tables statement and is nevertheless affected
.. see 1) above!
...
Error Code : 1100
Table 'tb3' was not locked with LOCK TABLES */

insert into tb1 (id) values (1); -- success .. no problem here

insert into tb2 (id) values (1);
/* this is OK as client is same that isssue the LOCK
Error Code : 1099
Table 'tb2' was locked with a READ lock and can't be updated */

insert into tb3 (id) values (1);
/* also here see 1) above 
Error Code : 1100
Table 'tb3' was not locked with LOCK TABLES */

-- now start 2nd connection
use lt;
select * from tb1; -- client is waiting for lock to be released .. this is OK
select * from tb2; -- success .. see 2) above 
select * from tb3; -- success .. see 1) above
insert into tb1 (id) values (1); -- waiting for lock to be released .. this is OK
insert into tb2 (id) values (1); -- waiting for lock to be released .. this is OK
insert into tb3 (id) values (1); -- success .. but not clear from documentation .. see 1) above

Suggested fix:
Include a case similar to above in documentation!

Even is this is fully according to SQL standards and totally similar to other SQL datbases, this is non-trivial! 

Why write docs for those who don't need it?

ok, my fault .. a 'd' was positioned wrong.  What I meant was of course

"doc request - docs on LOCK TABLE is 'insiderish'"

updated synopsis!

"
The problem is this short paasage "LOCK TABLES acquires table locks for the current
thread". BOOM! Are users supposed to read the thoughts of the person who wrote this?
"

I'm sorry, I don't understand what mysteries you think that this is hiding. You seem to be suggesting that a single sentence quoted from the section should convey all the information in the section. It's only the single-sentence summary of the purpose of LOCK TABLES.

"
This does not explicitly make it clear that

1) tables not listed as parameters to LOCK TABLES will be locked for current session but
not for other sessions!

2) LOCK WRITE allows *only* current session (any kind of) access - LOCK READ allows read
for *all* sessions.
"

The first para of the section says this, which covers 1):

A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Later, the section says this, which covers 2):

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 (that thread can also read from the table); other threads are blocked from reading or writing the table until the lock has been released.

"
/* first thing I do not find documented properly here
tb3 was not listed in LOCK tables statement and is nevertheless affected
"

This is covered by:

When you use LOCK TABLES, you must lock all tables that you are going to use in your statements. While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any tables that were not locked by the statement.

@Paul ..

I have now experienced 5+ MySQL users who were not able to understand from this text how LCOK TABLE works!  I also had to produce a test case for myself to find all details.  This should not be necessary! I find that what is important for users is hidden between description of threading implementation details.  I suggest moving the passages quoted by you up the page. HOW IT WORKS is most important.  HOW IT IS IMPLEMENTED is less important!  

As said: it is easy to understand this page if you (already) understand it!

I do not mean to be negative.  But if you have the chance try a survey on around 20 MySQL users with medium (2-3 years) experience! Do they know/understand in detail how LOCK TABLE works? I would be surprised if you find 2 that do!

I think the only change needed is some example to illustrate the following:

"When you use LOCK TABLES, you must lock all tables that you are going to use in your statements. While the locks obtained with a LOCK TABLES statement are in effect, you cannot access any tables that were not locked by the statement."

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 products.

Revised text (5.0, 5.1, and 6.0 non-transactional lock discussion):

A session that requires locks must acquire all the locks that it
needs in a single LOCK TABLES statement. While the locks thus
obtained are held, the session can access only the locked tables. For
example, in the following sequence of statements, an error occurs for
the attempt to access t2 because it was not locked in the LOCK TABLES
statement: 

mysql> LOCK TABLES t1 READ; 
mysql> SELECT COUNT(*) FROM t1; 
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2; 
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

With reference to the coment of 25 Nov 2008 18:08:

I cannot move the first section quoted by me up the page because it is already the first paragraph, which provides a general description of LOCK TABLES.

I've added the example (based on your own) to the second section quoted by me. I did not move it up the page because it is a more specific detail of locking behavior (and is in any case no longer true in general -- 6.0 has transactional locks, for which the behavior differs).