How to lock tables and why do we need to lock a table?

A client in a session can lock a certain table they are working on, in order to prevent other clients from using the same table. This process will avoid any data losses that might occur when multiple users work on the same table simultaneously. A client can lock a table and unlock it whenever needed. However, if a table is already locked by a client session, it cannot be accessed by other client sessions until it is released.

There are two kinds of MYSQL table locks −

1
READ LOCK − If you apply this lock on a table the write operations on it are restricted. i.e., only the sessions that holds the lock can write into this table.
2
WRITE LOCK − This lock allows restricts the sessions (that does not possess the lock) from performing the read and write operations on a table.

Syntax:

Difficulty: 6/10

Topics: table locking, concurrency control, transaction isolation

Scenario Questions

0-2 years experience

1
You need to run a bulk UPDATE that touches 50k rows in a MyISAM table while preventing any reads or writes during the operation. Walk me through the exact statements you'd run and what happens if the connection drops halfway through.
2
A teammate says 'just add LOCK TABLES before every query to be safe.' What's wrong with that approach, and what would you see in SHOW PROCESSLIST if they did it on a busy InnoDB table?

2-5 years experience

1
Our nightly analytics job does LOCK TABLES events WRITE, then runs a 10-minute INSERT ... SELECT. The checkout service starts timing out with 'Lock wait timeout exceeded.' You can't change the analytics query. What are two ways to mitigate this without rewriting the job?
2
A developer used LOCK TABLES orders WRITE, order_items WRITE in a stored procedure but forgot UNLOCK TABLES. The app pool exhausted connections. How do you diagnose this from the MySQL side, and what configuration would limit the blast radius next time?

5-8 years experience

1
We're migrating a 2TB MyISAM table to InnoDB with zero downtime. The table has heavy read/write traffic. Outline a strategy that avoids long table locks during the final cutover, including how you'd handle the brief moment when both engines must stay in sync.
2
A high-throughput service uses SELECT ... FOR UPDATE SKIP LOCKED for a work queue. Under load, you see 'Lock wait timeout' spikes despite low CPU. Explain the likely contention pattern and how you'd redesign the locking granularity or isolation level to fix it.

8+ years experience

1
Two teams own services that share a MySQL cluster. Team A wants to run a 4-hour ALTER TABLE on a core table; Team B can't tolerate more than 30s of write unavailability. Design a cross-team coordination plan and technical approach (tools, replication topology, rollback) that satisfies both, and explain how you'd get organizational buy-in.
2
You're inheriting a legacy system where 40% of queries use explicit LOCK TABLES on InnoDB tables. The new architecture requires horizontal sharding. How do you phase out table locks without a big-bang rewrite, and what observability would you add to prove safety at each step?

Follow-up Questions

❓What happens to other sessions when you hold a WRITE lock on a MyISAM table?
❓How does InnoDB's row locking interact with explicit LOCK TABLES?
❓When would you choose LOCK TABLES over a transaction with SELECT FOR UPDATE?