How to Use LOCK TABLES in MySQL: Syntax and Examples

Use LOCK TABLES in MySQL to explicitly lock tables for reading or writing to control concurrent access. After locking, perform your queries, then release locks with UNLOCK TABLES to allow others to access the tables.

Syntax

The LOCK TABLES statement locks one or more tables with specified lock types. Use UNLOCK TABLES to release all locks held by the current session.

• LOCK TABLES table_name [AS alias] lock_type: Locks the table with the given lock type.
• lock_type can be READ or WRITE.
• UNLOCK TABLES: Releases all table locks held by the session.

sql

LOCK TABLES table_name READ;
-- or
LOCK TABLES table_name WRITE;

-- After work is done
UNLOCK TABLES;

Example

This example locks the employees table for writing, inserts a new record, then unlocks the table so others can access it.

sql

LOCK TABLES employees WRITE;

INSERT INTO employees (name, position) VALUES ('Alice', 'Developer');

UNLOCK TABLES;

Output

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Common Pitfalls

Common mistakes when using LOCK TABLES include:

• Not unlocking tables after locking, which blocks other sessions.
• Trying to lock tables when using transactions with InnoDB, which uses row-level locking instead.
• Locking tables in one session and expecting locks to persist after disconnect.

Always use UNLOCK TABLES or close the session to release locks.

sql

/* Wrong: Forgetting to unlock tables */
LOCK TABLES employees WRITE;
INSERT INTO employees (name) VALUES ('Bob');
-- No UNLOCK TABLES here, other sessions will be blocked

/* Right: Always unlock */
LOCK TABLES employees WRITE;
INSERT INTO employees (name) VALUES ('Bob');
UNLOCK TABLES;

Quick Reference

Command	Description
LOCK TABLES table_name READ	Locks table for reading; others can read but not write.
LOCK TABLES table_name WRITE	Locks table for writing; others cannot read or write.
UNLOCK TABLES	Releases all locks held by the current session.
LOCK TABLES multiple_tables	Lock multiple tables with different lock types in one statement.

Key Takeaways

Use LOCK TABLES to control access to tables explicitly in MySQL.

Always release locks with UNLOCK TABLES to avoid blocking other sessions.

READ locks allow concurrent reads but block writes; WRITE locks block all other access.

LOCK TABLES is session-specific and locks are released on disconnect.

Avoid using LOCK TABLES with InnoDB transactions; prefer row-level locking.