MySQL Table Locking

Last updated: October 19, 2026Oct 19, 2026 6 min read

When many users work with the same data at once, the database must stop them from getting in each other's way. MySQL does this with locks. You can lock whole tables explicitly with LOCK TABLES, and InnoDB also locks individual rows automatically inside transactions.

The examples on this page use two real sessions: Session A and Session B. Session B's lock wait timeout is set to 2 seconds, so instead of waiting forever it reports a timeout error, which shows you exactly when it was blocked.

1. Why Locking Is Needed

Imagine Meera's balance is 5,000. Two cashiers read it at the same time, one adds 1,000 and the other subtracts 500, and each writes back its own result. One update is lost. This is the lost update problem. Locks force the second session to wait until the first has finished.

2. LOCK TABLES and UNLOCK TABLES

LOCK TABLES table1 {READ | WRITE} [, table2 {READ | WRITE} ...];
-- ... work with the locked tables ...
UNLOCK TABLES;

• READ (shared): you and others can read; nobody can write.
• WRITE (exclusive): only you can read and write; others wait.
• LOCK TABLES first releases any locks you already hold and commits an open transaction.
• Locks are released by UNLOCK TABLES, by starting a transaction, or when the session ends.
• You need the LOCK TABLES privilege and SELECT on the tables.

3. READ Lock in Action

Session A takes a READ lock and can read:

LOCK TABLES accounts READ;
SELECT * FROM accounts;

Result: 3 rows

But Session A can't write to the table it locked for reading:

UPDATE accounts SET balance = balance + 100 WHERE acc_no = 101;

ERROR 1099 (HY000): Table 'accounts' was locked with a READ lock and can't be updated

Session A also can't touch any table it didn't lock:

SELECT * FROM branches;

ERROR 1100 (HY000): Table 'branches' was not locked with LOCK TABLES

Session B can still read the locked table:

SELECT holder, balance FROM accounts WHERE acc_no = 102;

Result: 1 row

But its write has to wait for the lock, and after 2 seconds it times out:

UPDATE accounts SET balance = 0 WHERE acc_no = 102;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

UNLOCK TABLES;

Query OK, 0 rows affected

4. WRITE Lock in Action

LOCK TABLES accounts WRITE;
UPDATE accounts SET balance = balance - 500 WHERE acc_no = 103;
SELECT acc_no, balance FROM accounts WHERE acc_no = 103;

Result: 1 row

Now even reading blocks for Session B:

SELECT * FROM accounts;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

UNLOCK TABLES;

Query OK, 0 rows affected

After the unlock, Session B reads normally and sees Session A's change:

SELECT acc_no, balance FROM accounts WHERE acc_no = 103;

Result: 1 row

5. Locking Several Tables and Aliases

Lock every table the work needs in one statement. If a query uses an alias, lock the table under that alias too:

LOCK TABLES accounts AS a READ, branches WRITE;
SELECT a.holder FROM accounts AS a WHERE a.balance > 4000;
UPDATE branches SET city = 'Kochi Central' WHERE id = 2;
UNLOCK TABLES;

Result: 2 rows

6. InnoDB Row Locks: the Better Tool

LOCK TABLES blocks the whole table. InnoDB transactions instead lock only the rows they change, so different users can update different rows at the same time. Use SELECT … FOR UPDATE to lock rows you are about to change.

Session A locks Meera's row:

START TRANSACTION;
SELECT balance FROM accounts WHERE acc_no = 101 FOR UPDATE;

Result: 1 row

Session B can update a different row immediately:

UPDATE accounts SET balance = balance + 50 WHERE acc_no = 102;

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

But changing the same row has to wait, and times out:

UPDATE accounts SET balance = balance + 50 WHERE acc_no = 101;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Session A finishes its work and commits, releasing the row lock:

UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 101;
COMMIT;
SELECT acc_no, balance FROM accounts ORDER BY acc_no;

Result: 3 rows

7. Metadata Locks

Any statement that uses a table also takes a metadata lock on it, so its structure can't change underneath. An ALTER TABLE must wait until all open transactions that touched the table have finished. A forgotten open transaction can therefore make a quick ALTER TABLE hang. Find blockers with:

SELECT object_name, lock_type, lock_status, owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_schema = 'bank';

8. Deadlocks

A deadlock happens when two transactions each wait for a row the other has locked. InnoDB detects it immediately and rolls back one of them with ERROR 1213 (40001): Deadlock found when trying to get lock. Your application should simply retry that transaction.

• Lock rows in the same order everywhere (for example, by ascending acc_no).
• Keep transactions short.
• Use indexes so updates lock fewer rows.
• SHOW ENGINE INNODB STATUS shows the latest deadlock.

9. When to Use LOCK TABLES

• Rarely, with InnoDB. Transactions and row locks are almost always better.
• Bulk maintenance on MyISAM tables (which have only table locks).
• Making a consistent copy of a few non-transactional tables.
• Never hold table locks while waiting for user input.

10. Summary

• LOCK TABLES t READ lets everyone read and no one write; WRITE gives one session exclusive access.
• A session holding table locks can only use the tables it locked; release them with UNLOCK TABLES.
• InnoDB locks rows inside transactions; use SELECT … FOR UPDATE to lock rows you will change.
• Metadata locks protect table structure; long transactions can block ALTER TABLE.
• Deadlocks are detected automatically; retry the failed transaction.