MySQL Bugs: #5998: LOCK TABLE write locks for same table conflict

Bug #5998	LOCK TABLE write locks for same table conflict
Submitted:	9 Oct 2004 5:29	Modified:	15 Oct 2004 14:14
Reporter:	Dean Ellis
Status:	Closed
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	4.0.22 4.1.6	OS:	Any
Assigned to:	Marko Mäkelä	Target Version:

Description:
Write locks for the same table interfere with each other; if ConnectionA acquires LOCK
TABLE write lock on table t1, then ConnectionB requests the same LOCK TABLE write lock on
table t1, ConnectionA is blocked from modifying table t1.

How to repeat:
Connection A:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int ) TYPE=InnoDB;
LOCK TABLE t1 WRITE;

Connection B:
LOCK TABLE t1 WRITE;

Connection A:
INSERT INTO t1 VALUES (1);

SHOW INNODB STATUS reveals that the ConnectionA INSERT is waiting for the table lock held
by ConnectionB.

ConnectionB blocks until ConnectionA unlocks the table.
ConnectionA blocks for innodb_lock_wait_timeout seconds before aborting.

Suggested fix:
n/a

I was able to repeat this problem. Table locks are safer if you run with
SET AUTOCOMMIT=0; I could not repeat this problem if I used 
SET AUTOCOMMIT=0; in connection A. 

But the real fix is to remove unnecessary table locking. Otherwise threads
will pile up, and cause serious trouble.

I should soon implement LOCK TABLES TRANSACTIONAL, where the
table lock is only taken internally inside InnoDB. Then InnoDB's deadlock
detection algorithm will work, and people will have less trouble with
table locks.

In InnoDB, LOCK TABLES takes two locks: the MySQL table lock, and a table lock inside
InnoDB. (Before my fix last spring, it would only take the MySQL table lock, thus failing
to block when there were ongoing transactions on the table.) In InnoDB, all locks are
released at transaction commit.

In AUTOCOMMIT=1 mode, LOCK TABLES will acquire and keep the MySQL table lock until UNLOCK
TABLES. It will also acquire the InnoDB table lock, but release it immediately. Probably
the simplest fix to this problem would be to make LOCK TABLES issue an implicit BEGIN.
However, that could break existing applications. (Applications might just terminate the
connection without COMMIT or UNLOCK tables, which would cause a rollback if LOCK TABLES
started a transaction.)

Hi!

The behavior is, unfortunately, as expected: since you are running with

AUTOCOMMIT=1

the InnoDB table lock of the user 1 is released immediately AFTER his LOCK TABLES is
processed! That is why user 2 gets an InnoDB table lock on the table, and the table lock
of user 2 blocks the insert of user 1!

The fix would be to disable AUTOCOMMIT=1 inside LOCK TABLES, but Monty and I decided not
to do that.

Correct use of LOCK TABLES with InnoDB requires that AUTOCOMMIT = 0. Then the InnoDB
table lock is held as long as the MySQL table lock, and the behavior makes sense.

The problem stems from the fact that a transactional database releases all locks at a
transaction commit. But MySQL table locks are NOT released unless there is a explicit
UNLOCK TABLES.

When we get

LOCK TABLES TRANSACTIONAL ...

implemented, then users should stop using LOCK TABLES for transactional tables. The
interplay between non-transactional locks and transactional locks is simply too complex.

One can ask if we should remove InnoDB table locks from versions 4.0.20- and 4.1.4- in
LOCK TABLES, and return to the old behavior.

Marko is right now adding a my.cnf  option

innodb_mysql_locks_old_behavior

that will help users who have used LOCK TABLES with AUTOCOMMIT=1.

Regards,

Heikki

Starting with MySQL 4.0.22 and 4.1.7, there will be a startup option and settable session
variable innodb_table_locks_old_behavior, which makes LOCK TABLES behave in the old way:
no InnoDB lock will be taken. (The new behavior has caused some problems for those who
were using LOCK TABLES with InnoDB, especially in AUTOCOMMIT=1 mode.)

In a code review, the option was renamed to innodb_table_locks.
By default, InnoDB 4.0.22 and 4.1.7 will behave like before 4.0.20 and 4.1.2:
LOCK TABLES acquires no InnoDB lock. In order to make LOCK TABLES acquire InnoDB
locks (as in 4.0.20, 4.0.21, 4.1.2..4.1.6), you will have to set
innodb_table_locks=1.

Hi,

I know its very late that I'm commenting on this bug. But I recently landed up in the
same problem. I assume LCOK TABLE TRANSACTIONAL is not yet implemented as I couldn't spot
it in the documentation.

My problem has a little more twist to it. You have mentioned that we need to use
AUTOCOMMIT=0 mode to avoid such locking issue and also the documentation
http://dev.mysql.com/doc/refman/4.1/en/commit.html says that 

"To disable autocommit mode for a single series of statements, use the START TRANSACTION
statement:"
and
"BEGIN and BEGIN WORK are supported as aliases of START TRANSACTION for initiating a
transaction."

I'm running into this problem while using LOCK TABLE on InnoDB after BEGIN. Can you
please explain how can I get rid of this.

Parvesh,

as far as I remember, Innobase Oy prepared a patch for implementing transactional table
locks in early 2005.  The feature was not accepted to MySQL 5.0.

The syntax for acquiring transactional table locks was implemented by a MySQL employee in
early 2007, but I am not sure if it was included in MySQL 5.1 or only in a special build
for a premium support customer.

I will now try to explain how LOCK TABLES works in MySQL.

Traditionally, MySQL avoids deadlocks by locking the all needed tables for the duration
of the statement, always in the same order, so that no cyclic waits can occur between
client connections.  When this bug was fixed, LOCK TABLES would also acquire an InnoDB
table lock, so that all transactions in InnoDB (including FOREIGN KEY CASCADE operations
and such) would obey the table lock.

Normally, transactional databases release all locks of a transaction at commit or
rollback. However, MySQL table locks are not transactional, but they last until an UNLOCK
TABLES command is executed.  At transaction commit, the InnoDB locks will be released, but
the MySQL locks will be held until UNLOCK TABLES.

I suppose that the MySQL transactional table locks would refuse UNLOCK TABLES, and that
they would allow multiple LOCK TABLE statements during a transaction. That is how locks
work in many other transactional databases.

Marko,

It seems like the table lock that InnoDB is having is a shared lock. Otherwise the second
transaction should not even get the InnoDB lock given that it was exclusive. Can you
please elaborate on this? If so, can't we have InnoDB respecting LOCK TABLE ... WRITE to
get an exclusive lock on the table and thus be in sync with the intentions.

Regards,
Parvesh

Parvesh,

Yes, InnoDB should map LOCK TABLES to a shared (S) lock and LOCK TABLES ... WRITE to an
exclusive (X) lock. I'm sorry that I forgot to mention this.

Please tell us if LOCK TABLES ... WRITE solved your problem.

(For casual readers:  deadlocks can often be avoided with coarse or aggressive locking:
get all the locks upfront, and you won't have to wait for locks during the actual
operation. Sometimes, SELECT ... FOR UPDATE early in the transaction does the trick.)

Marko,

I mentioned this because LOCK TABLE ... WRITE is not solving my purpose. I hope this is a
bug with InnoDB. As of now, we have changed our locking strategy and successfully using
get_lock to avoid such things. Moreover, as you mentioned about aggressive locking, I
think get_lock is one of the best as it is light weight also and doesn't depend on the
underlying engine.