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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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)
READ LOCALtable lock. If you explicitly lock the table without the
LOCALmodifier, 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.