Locks

MySQL enables client sessions to acquire table locks explicitly, either to cooperate with other sessions over access to tables, or to prevent other sessions from modifying tables while 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.

Sometimes it is much faster to lock MyISAM tables, because MySQL does not flush the key cache while a lock is in effect. At other times, LOCK TABLES is required to ensure that no other session modifies the tables between a SELECT and an UPDATE. However, this can sometimes be avoided by using relative updates (e.g. UPDATE tbl SET v=v+10).

Syntax

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

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

Lock Type	Holding Session	Other Sessions
READ	Can read Can't write	Can read Can't write
WRITE	Can read Can write	Can't read Can't write

For a READ lock, no session can update the table. The session holding the lock can read the table but not write it. Other sessions can read the table without explicitly acquiring a READ lock. Multiple sessions can acquire a READ lock for the table at the same time. LOCAL enables non-conflicting INSERT statements by other sessions. If InnoDB is used, READ LOCAL behaves the same as READ.

For a WRITE lock, the session that holds the lock can read and write the table. No other session can access it; lock requests for the table by other sessions block. LOW_PRIORITY is ignored.

All locks are released automatically when the session terminates.

LOCK TABLES and UNLOCK TABLES cannot be used within stored programs. The following statements cannot be run while a table is locked: CREATE TABLE, CREATE VIEW, DROP VIEW, and DDL statements on stored functions, procedures and events.

Views and Triggers

Locking a view locks all underlying tables automatically. Any tables referenced in triggers are also locked automatically.

Transaction

Beginning a transaction releases existing table locks. ROLLBACK does not release table locks.

The example below illustrates the correct way to use LOCK TABLES with transactional tables, combining it with Transactions concepts such as autocommit and COMMIT.

Locking Reads

SELECT ... FOR SHARE sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until the transaction commits. If any of these rows were changed by another transaction that has not yet committed, the query waits until that transaction ends and then uses the latest values.

SELECT ... FOR UPDATE, for index records the search encounters, locks the rows and any associated index entries, the same as if an UPDATE statement had been issued for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... FOR SHARE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.

All locks set by FOR SHARE and FOR UPDATE queries are released when the transaction is committed or rolled back.

To avoid waiting for other transactions to release row locks, the NOWAIT and SKIP LOCKED options may be used with SELECT ... FOR UPDATE or SELECT ... FOR SHARE locking read statements.

NOWAIT: a locking read that uses NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.

SKIP LOCKED: a locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.

Global Read Lock

FLUSH TABLES WITH READ LOCK acquires a global read lock. It is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES, which are table locks. START TRANSACTION does not release it.

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

-- Shared-mode lock: other sessions may read but not modify these
-- rows until this transaction commits.
START TRANSACTION;
SELECT * FROM tbl WHERE id = 1 FOR SHARE;

-- Exclusive lock, equivalent to an UPDATE on the matched rows.
SELECT * FROM tbl WHERE id = 1 FOR UPDATE;

-- Fail immediately instead of waiting if the row is already locked.
SELECT * FROM tbl WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip any already-locked rows instead of waiting for them.
SELECT * FROM tbl WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
COMMIT;

-- Shared-mode lock: other sessions may read but not modify these
-- rows until this transaction commits.
START TRANSACTION;
SELECT * FROM tbl WHERE id = 1 FOR SHARE;

-- Exclusive lock, equivalent to an UPDATE on the matched rows.
SELECT * FROM tbl WHERE id = 1 FOR UPDATE;

-- Fail immediately instead of waiting if the row is already locked.
SELECT * FROM tbl WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip any already-locked rows instead of waiting for them.
SELECT * FROM tbl WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
COMMIT;

MENU