1

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?

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
2
  • I already know about transactions. This question was specifically about locking tables. This is a special case.
    – Programster
    Jul 6, 2015 at 22:00
  • OK. You have to LOCK all tables at once. The LOCK TABLE code depends on that for deadlock prevention, rather than having some complex code.
    – Rick James
    Jul 6, 2015 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.