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 later in
this section.

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.

A table lock only protects against inappropriate reads or writes
by other sessions. A session holding a WRITE
lock can perform table-level operations such as
DROP TABLE or
TRUNCATE TABLE. For sessions
holding a READ lock, DROP
TABLE and TRUNCATE TABLE
operations are not permitted.

The following discussion applies only to
non-TEMPORARY tables. LOCK
TABLES is permitted (but ignored) for a
TEMPORARY table. The table can be accessed
freely by the session within which it was created, regardless of
what other locking may be in effect. No lock is necessary because
no other session can see the table.

To acquire table locks within the current session, use the
LOCK TABLES statement. The
following lock types are available:

READ [LOCAL] lock:

The session that holds the lock can read the table (but not
write it).

Multiple sessions can acquire a READ lock
for the table at the same time.

Other sessions can read the table without explicitly acquiring
a READ lock.

The LOCAL modifier enables nonconflicting
INSERT statements (concurrent
inserts) by other sessions to execute while the lock is held.
(See Section 8.11.3, “Concurrent Inserts”.) However,
READ LOCAL cannot be used if you are going
to manipulate the database using processes external to the
server while you hold the lock. For InnoDB
tables, READ LOCAL is the same as
READ.

[LOW_PRIORITY] WRITE lock:

The session that holds the lock can read and write the table.

Only the session that holds the lock can access the table. No
other session can access it until the lock is released.

Lock requests for the table by other sessions block while the
WRITE lock is held.

The LOW_PRIORITY modifier has no effect. In
previous versions of MySQL, it affected locking behavior, but
this is no longer true. As of MySQL 5.6.5, it is deprecated
and its use produces a warning. Use WRITE
without LOW_PRIORITY instead.

If the LOCK TABLES statement must
wait due to locks held by other sessions on any of the tables, it
blocks until all locks can be acquired.

A session that requires locks must acquire all the locks that it
needs in a single LOCK TABLES
statement. While the locks thus obtained are held, the session can
access only the locked tables. For example, in the following
sequence of statements, an error occurs for the attempt to access
t2 because it was not locked in the
LOCK TABLES statement:

WRITE locks normally have higher priority than
READ locks to ensure that updates are processed
as soon as possible. This means that if one session obtains a
READ lock and then another session requests a
WRITE lock, subsequent READ
lock requests wait until the session that requested the
WRITE lock has obtained the lock and released
it.

Sort all tables to be locked in an internally defined order.
From the user standpoint, this order is undefined.

If a table is to be locked with a read and a write lock, put
the write lock request before the read lock request.

Lock one table at a time until the session gets all locks.

This policy ensures that table locking is deadlock free.

Note

LOCK TABLES or UNLOCK
TABLES, when applied to a partitioned table, always
locks or unlocks the entire table; these statements do not
support partition lock pruning. See
Section 19.6.4, “Partitioning and Locking”.

Rules for Lock Release

When the table locks held by a session are released, they are all
released at the same time. A session can release its locks
explicitly, or locks may be released implicitly under certain
conditions.

If the connection for a client session terminates, whether
normally or abnormally, the server implicitly releases all table
locks held by the session (transactional and nontransactional). If
the client reconnects, the locks will no longer be in effect. In
addition, if the client had an active transaction, the server
rolls back the transaction upon disconnect, and if reconnect
occurs, the new session begins with autocommit enabled. For this
reason, clients may wish to disable auto-reconnect. With
auto-reconnect in effect, the client is not notified if reconnect
occurs but any table locks or current transaction will have been
lost. With auto-reconnect disabled, if the connection drops, an
error occurs for the next statement issued. The client can detect
the error and take appropriate action such as reacquiring the
locks or redoing the transaction. See
Section 23.8.20, “C API Automatic Reconnection Control”.

User comments in this section are, as the name implies, provided by MySQL users.
The MySQL documentation team is not responsible for, nor do they endorse, any of
the information provided here.

Posted by
Frederic Marand
on
November 30, 2010

Note that, while a WRITE lock will lock new SELECT queries until it is converted to a READ lock or released via UNLOCK, it will not prevent SELECT queries already in the query cache from returning the cached results.

This marginal effect will be limited, though: in most cases the session LOCKing a table will be writing to the same table before UNLOCKing, which will cause the query cache to be flushed for that table.

Posted by
Larry Clapp
on
September 13, 2011

If you need to do things with tables not normally supported by read or write locks (like dropping or truncating a table), and you're able to cooperate, you can try this: Use a semaphore table, and create two sessions per process. In the first session, get a read or write lock on the semaphore table, as appropriate. In the second session, do all the stuff you need to do with all the other tables.

This is just using the database to create and manage a semaphore instead of using the OS directly (and of course it's not "real" locking but only advisory locking, so it won't work if you can't cooperate with other users), but on the other hand using the database allows the semaphore to be accessed by multiple other servers accessing the DB over a network.