How to lock tables in MySQL

Use LOCK TABLES to lock tables manually. The READ lock allows multiple sessions to read but cannot be written. The WRITE lock provides exclusive read and write permissions for the current session and other sessions cannot read and write. 2. Locking is only for the current connection. Execution of START TRANSACTION and other commands will implicitly release the lock. After locking, it can only access the locked table; 3. Only use in specific scenarios such as MyISAM table maintenance and data backup. InnoDB should prioritize transaction and row-level locks such as SELECT... FOR UPDATE to avoid performance problems; 4. After the operation is completed, UNLOCK TABLES must be explicitly released, otherwise resource blockage may occur.

Locking tables in MySQL is a way to control access to table data during certain operations, especially in MyISAM and other storage engines that don't support row-level locking. While InnoDB uses row-level locks by default and handles concurrency more efficiently, table locking is still relevant in specific scenarios.

Here's how to lock tables in MySQL:

1. Using LOCK TABLES and UNLOCK TABLES

MySQL provides the LOCK TABLES statement to manually lock one or more tables for the current session. These locks prevent other sessions from modifying or reading the tables, depending on the lock type.

Syntax:

 LOCK TABLES
    table_name [AS alias] READ | WRITE
    [, table_name READ | WRITE]...;

After locking, you must release the locks with:

 UNLOCK TABLES;

Types of Locks:

• READ lock ( LOCK TABLES table_name READ ):

  
  • Allows the session to read the table but not write to it.
  • Other sessions can still read the table, but cannot write to it until the lock is released.
  • Multiple sessions can hold a READ lock on the same table simultaneously.

• WRITE lock ( LOCK TABLES table_name WRITE ):

  • Grants exclusive access: only the locking session can read or write the table.
  • No other session can read or write the table until the lock is released.
  • Has higher priority than read locks.

Example:

 LOCK TABLES users WRITE;
UPDATE users SET last_login = NOW() WHERE id = 1;
UNLOCK TABLES;

This ensures no other session interferes with the users table during the update.

2. Important Notes and Best Practices

• Locks are connection-specific :
  Table locks apply only to the current database connection. Closing the connection or running UNLOCK TABLES releases them.

• InnoDB and auto-commit :
  If you're using InnoDB, avoid LOCK TABLES unless absolutely necessary. InnoDB handles concurrency with internal row-level locks and MVCC (Multi-Version Concurrency Control). Using LOCK TABLES in InnoDB can interfere with transactional integrity.

• You cannot access unlocked tables after LOCK TABLES :
  Once you lock any table, your session can only access the locked tables. Any attempt to access an unlocked table will result in an error.

• Implicit unlocking :
  Certain commands (like START TRANSACTION , COMMIT , ROLLBACK , or ALTER TABLE ) will automatically release table locks.

3. When to Use Table Locks

Table locking is mostly used in these cases:

• Performance maintenance on MyISAM tables.
• Migrating or backing up data with consistent reads.
• Implementing application-level locking logic (rare).

For most modern applications using InnoDB, you should rely on:

• Transactions ( BEGIN , COMMIT , ROLLBACK )
• Row-level locks via SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE

Example of InnoDB-style locking (preferred):

 START TRANSACTION;
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;
-- safely modify data
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;

This locks only the affected rows, not the entire table.

Summary

• Use LOCK TABLES table READ/WRITE to manually lock tables.
• Always call UNLOCK TABLES to release locks.
• Avoid table locks in InnoDB; prefer row-level locking via transactions.
• Table locks are coarse-grained and can hurt performance in high-concurrency environments.

Basically, only use LOCK TABLES when you're certain you need it—like with non-transactional engines or very specific consistency requirements.

The above is the detailed content of How to lock tables in MySQL. For more information, please follow other related articles on the PHP Chinese website!