MySql Lock Tables Blocking Logic

lockingMySQL

The MySQL documentation (5.5) for Lock tables states:
enter image description here

Let's say that I'm trying to lock table1 and table2 in a single query, but table2 is already locked. Will MySQL hold onto a lock on table1 and block until it can lock table2 or will it only hold onto a lock on table1 when it can get a lock on both tables?

In case it matters, assume the InnoDB storage engine.

Update
I just found the following statement on the same page:

When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released.

Is this confirming that MySQL will block whilst holding the lock on table1? If that is the case, is there a way to query MySQL such that it will immediately return with a failure rather than block if it fails to grab all of the table locks?

Best Answer

Do not use LOCK TABLES with InnoDB unless you have a very special need.

Instead, learn about "transactions" and use BEGIN...COMMIT to effect most "locking" needed. See also SELECT ... FOR UPDATE....

Related Solutions

Mysql – Lock wait timeout exceeded on insert despite low lock_time in the slow query log

The query times are wall time from start to finish. The lock times are times spent actually locking affected tables. If you run

select sleep(60);

You'll see a slow query entry with a query time of 60 seconds and no lock time.

50 seconds is a standard lock wait time out setting. Run

show variables like 'innodb_lock_wait_timeout';

I'll bet it's 50. The queries in your OP are likely showing a query time of this b/c of the time out setting. They're probably sitting there not really doing anything waiting for some other query to finish until they're effectively killed. If they're hitting that limit it should be easy to just run show processlist; to see what's going on. You'll likely see many threads in a Locked state (i.e. doing nothing but waiting) where there's one thread "doing something".

Are your truncated "insert" statement ultimately doing some kind of expensive subselect from other tables?

Is your application explicitly locking tables and not letting go?

Edit: Queries hitting a lock wait timeout does not mean they are holding on to a lock for that limit but rather they got tired of waiting for someone else to give it up and let them have a go.

show engine innodb status\G

Can also give you insight to what is locked at the moment and who is holding them. Although the output can be a bit cryptic at first glance it can give some pointers.

Mysql – Does MySQL guarantee atomic upgrade from read lock to write lock

Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
BEGIN;
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back

Best Answer

Related Solutions

Mysql – Lock wait timeout exceeded on insert despite low lock_time in the slow query log

Mysql – Does MySQL guarantee atomic upgrade from read lock to write lock

Related Question