MySQL Bugs: #5665: problem with lock table sql command

Bug #5665	problem with lock table sql command
Submitted:	20 Sep 2004 13:52	Modified:	30 Sep 2008 22:23
Reporter:	Stefano Misto'	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1, 5.0	OS:	HP/UX (HP-UX B.11.11 U 9000/785)
Assigned to:		CPU Architecture:	Any

Description:
I've encountered the following problem using "lock table" sql command in a transactional environment (InnoDB engine)

I performed a test with the following commands (consider that all the involved table are InnoDB ones):

==============================
mysql> lock table NE read;
mysql> select * from NTW;
ERROR 1100 (HY000): Table 'NTW' was not locked with LOCK TABLES
====================================

I don't want to lock all the table on which I want to perform read (select) and/or write (update/insert) operations, because I need really to lock only the first one (table named NE), while on the other ones I don't want to set any lock or a row level lock is enough.

I think this is a bug to be solved for MySQL 4.1, this is a blocking problem for us.

Thanks,
Stefano

How to repeat:
mysql> lock table NE read;
mysql> select * from NTW;
ERROR 1100 (HY000): Table 'NTW' was not locked with LOCK TABLES

Stefano,

you have bumped into a MySQL feature. Since there is no deadlock detection in MySQL's table locking, you must in LOCK TABLES specify ALL the tables that you are going to use before the next UNLOCK TABLES. Otherwise you could end up in a deadlock that MySQL cannot resolve.

A workaround: do not use LOCK TABLES at all. Program the logic using rows locks:

SELECT .... FOR UPDATE;

and

SELECT ... LOCK IN SHARE MODE;

Then InnoDB's automatic deadlock detection takes care of deadlocks.

Feature request:

1) MySQL should implement deadlock detection in the table locking system,

or

2) we should introduce a new command LOCK TABLES TRANSACTIONAL ... that would only set InnoDB table locks, no MySQL table locks. Then the deadlock detection of InnoDB would work.

Hmm... I would prefer 2, because then the deadlock detection for row locks and table locks is combined.

Regards,

Heikki

Heikki,

the workaround you suggest doesn't give exactly the same bahaviour of a "lock table", infact after a "select for update" from session A, new "insert" commands from another session are not blocked.

About the solutions, surely the second one is the only that fit our needs, infact we are interested in having deadlocks detection for row locks and table locks combined (we use InnoDB). 
What I don't agree is that there will be a different sintax: I think it is not good to have two very similar "lock" command with a so different bahaviour.

Stefano

Stefano,
we agree that it would be nice to have only one LOCK command that would play nicely with transactions. However, due to historical reasons, the LOCK TABLES/UNLOCK TABLES statements in MySQL were implemented in the way they are, sort of "poor man's BEGIN/COMMIT". We cannot change the behaviour of LOCK TABLES, because it would break existing applications.

You also wrote that the workaround Heikki suggested doesn't work: 'after a "select for update" from session A, new "insert" commands from another session are not blocked.' A further workaround for this would be to issue the "select for update" in the beginning of the session that does the "insert".

Marko

Hi!

The current plan is to use the Oracle/DB2 syntax in 'transactional' LOCK TABLES:

LOCK TABLE t IN SHARE MODE;

or

LOCK TABLE t IN EXCLUSIVE MODE;

Then the syntax would be easily portable.

It looks like this feature may be delayed to MySQL-5.1, though the patch is actually ready and written already. Is version 5.1 ok for you?

Regards,

Heikki

Sorry, but MySQL 5.1 is really too late for us, at now we have planned to use MySQL 5.0 (we have changed our original plan to use 4.1) knowing that it will solve the find-out problems and that it will be released to us in May 2005. 
So, for us (Alcatel), it's better to have the solution in MySQL 5.0.
Let me know your decision.

Bye,
Stefano

Starting from 6.0 you can use LOCK TABLE TRANSACTIONAL:
http://dev.mysql.com/doc/refman/6.0/en/lock-tables.html