top of page
CerebroSQL

MySQL: 

LOCK TABLES

Syntax:
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...

lock_type: {
READ [LOCAL]
| [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

MySQL enables client sessions to acquire table locks explicitly for the
purpose of cooperating with other sessions for access to tables, or to
prevent other sessions from modifying tables during periods when a
session requires exclusive access to them. A session can acquire or
release locks only for itself. One session cannot acquire locks for
another session or release locks held by another session.

Locks may be used to emulate transactions or to get more speed when
updating tables. This is explained in more detail in
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-re
strictions.

LOCK TABLES explicitly acquires table locks for the current client
session. Table locks can be acquired for base tables or views. You must
have the LOCK TABLES privilege, and the SELECT privilege for each
object to be locked.

For view locking, LOCK TABLES adds all base tables used in the view to
the set of tables to be locked and locks them automatically. For tables
underlying any view being locked, LOCK TABLES checks that the view
definer (for SQL SECURITY DEFINER views) or invoker (for all views) has
the proper privileges on the tables.

If you lock a table explicitly with LOCK TABLES, any tables used in
triggers are also locked implicitly, as described in
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-an
d-triggers.

If you lock a table explicitly with LOCK TABLES, any tables related by
a foreign key constraint are opened and locked implicitly. For foreign
key checks, a shared read-only lock (LOCK TABLES READ) is taken on
related tables. For cascading updates, a shared-nothing write lock
(LOCK TABLES WRITE) is taken on related tables that are involved in the
operation.

UNLOCK TABLES explicitly releases any table locks held by the current
session. LOCK TABLES implicitly releases any table locks held by the
current session before acquiring new locks.

Another use for UNLOCK TABLES is to release the global read lock
acquired with the FLUSH TABLES WITH READ LOCK statement, which enables
you to lock all tables in all databases. See [HELP FLUSH]. (This is a
very convenient way to get backups if you have a file system such as
Veritas that can take snapshots in time.)

URL: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

Example

bottom of page