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 3:29	Modified:	15 Oct 2004 12:14
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.22 4.1.6	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

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.