2

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

2

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 ofthe 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 as it seems from the manual.

But nowadays you should mostly use the InooDB 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.

  • Good answer, even though it could have been a bit "simpler" -- thanks! – koljanep Sep 29 '15 at 21:24
  • 1
    Well, locking is not a simple subject and as you see all of your points can actually happen under some conditions :) – jkavalik Sep 30 '15 at 5:06

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.