Simple question but somehow impossible to find a clear answer:

What happens when I try to insert data into mysql while the table is locked?

1) INSERT query fails

2) INSERT query waits until lock is released

3) something else


The "normal" result is the INSERT query waiting until the lock is released. But there are some limits and special cases (you wrote about locking TABLES so MyISAM or explicit LOCK TABLES statement)

  • the waiting can timeout (lock_wait_timeout)
  • Concurrent Inserts are possible when you just append to the end of the table, in that case no waiting happens and INSERTs run in parallel. That needs the locking to originate implicitly from other INSERT/SELECT etc or explicitly from READ LOCAL table lock. If you explicitly lock the table without the LOCAL modifier, this feature cannot be used according to the manual.

But nowadays you should mostly use the InnoDB engine and transaction. In that case there are usually no explicit table locks used. There still can be metadata locks. But according to the manual InnoDB will not escalate row locks to table locks, instead using Multiple granularity locking according to https://www.percona.com/blog/2010/06/08/table-locks-in-show-innodb-status/ .

With row locks many operations can run in parallel and inserts should only wait if there is a gap lock on the gap into which the insert should put the inserted row.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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