MySql Lock Tables Blocking Logic

Question

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?

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

• Programster: I already know about transactions. This question was specifically about locking tables. This is a special case.
• Rick James: 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.