MySQL MySQL LOCK TABLE

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Syntax

  • LOCK TABLES table_name [READ | WRITE]; // Lock Table

  • UNLOCK TABLES; // Unlock Tables

Remarks

Locking is used to solve concurrency problems.Locking is required only when running a transaction, that first read a value from a database and later write that value in to the database. Locks are never required for self-contained insert, update, or delete operations.

There are two kinds of locks available

READ LOCK - when a user is only reading from a table.

WRITE LOCK - when a user is doing both reading and writing to a table.

When a user holds a WRITE LOCK on a table, no other users can read or write to that table. When a user holds a READ LOCK on a table, other users can also read or hold a READ LOCK, but no user can write or hold a WRITE LOCK on that table.

If default storage engine is InnoDB, MySQL automatically uses row level locking so that multiple transactions can use same table simultaneously for read and write, without making each other wait.

For all storage engines other than InnoDB, MySQL uses table locking.

For more details about table lock See here



Got any MySQL Question?