mariadb-corporation Explicit Locking in MariaDB
Last updated: 2026-07-20
This skill covers the delta between what an LLM tends to assume about locking and MariaDB's actual behavior: the LOCK TABLES / UNLOCK TABLES statements and the GET_LOCK() family of named user-level locks. It assumes the agent already knows that databases have locks; it does not re-explain locking in general.
Default context: Assume MariaDB 11.8 LTS unless the user states another version. Anything in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.
What LLMs Often Miss
| If the agent writes / suggests… | …prefer the MariaDB reality |
|---|---|
LOCK TABLES t1 WRITE; then later SELECT * FROM t1 AS a |
Any alias used in a later statement must also appear in the LOCK TABLES clause, or you get ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES. Lock the alias you intend to query with: LOCK TABLE t1 AS a WRITE; |
LOCK TABLES t1 WRITE; then querying t2 for a lookup, or relying on triggers touching other tables |
You must lock every table you will reference in the session, not just the one you intend to write. Touching an unlocked table errors with ERROR 1100. Tables used by triggers on a locked table are locked implicitly, but tables read in application code are not — list them all |
START TRANSACTION; LOCK TABLES t1 WRITE; ... COMMIT; as a "belt and suspenders" pattern |
LOCK TABLES implicitly commits any active transaction before acquiring the locks, and starting a new transaction releases all LOCK TABLES locks. You cannot hold table locks and an open transaction at the same time (autocommit-mode statements are the only exception). Don't mix the two locking models — pick one |
Reaching for LOCK TABLES to serialize access to a row/counter inside a transactional workflow |
Use row locking (SELECT ... FOR UPDATE, see mariadb-select) or GET_LOCK() instead — both compose with transactions. LOCK TABLES does not |
GET_LOCK('mylock', 5) and assuming a second GET_LOCK('other', 5) in the same session fails because "you can only hold one lock" |
Since MariaDB 10.0.2 (MDEV-3917) a connection can hold multiple named locks simultaneously — GET_LOCK() no longer releases previously-held locks on the same connection. All current LTS versions have this |
SELECT RELEASE_LOCK('a'); SELECT RELEASE_LOCK('b'); ... one call per lock name to clean up |
RELEASE_ALL_LOCKS() releases every named lock the session holds in one call and returns the count released |
Treating GET_LOCK() as transactional (expecting COMMIT/ROLLBACK to release it) |
Named locks are independent of transactions. Locks are released only by RELEASE_LOCK(), RELEASE_ALL_LOCKS(), or the connection closing (normally or abnormally) — never by COMMIT or ROLLBACK |
LOCK TABLES inside a stored procedure/function |
Not allowed — errors at creation time with ERROR 1314 (0A000): LOCK is not allowed in stored procedures. Use GET_LOCK() there instead |
Using GET_LOCK/LOCK TABLES output/timing to synchronize statement-based replicas |
Neither is safe for statement-based replication — the lock functions and LOCK TABLES don't reproduce identically on a replica applying via SBR. Prefer row-based replication (the modern default) or application-level coordination outside SQL |
LOCK TABLES / UNLOCK TABLES
LOCK TABLE[S]
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
[WAIT n | NOWAIT]
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
| WRITE CONCURRENT
UNLOCK TABLES
lock_type |
Effect |
|---|---|
READ |
Read lock; no writes from any connection allowed |
READ LOCAL |
Read lock, but permits concurrent inserts from other connections |
WRITE |
Exclusive; no other connection can read or write the table |
LOW_PRIORITY WRITE |
Exclusive write lock, but lets new read-lock requests jump ahead while waiting for the write lock |
WRITE CONCURRENT |
Exclusive write lock that still permits other connections' READ LOCAL locks |
Requires the LOCK TABLES privilege plus SELECT on every table locked. Locking a view locks its underlying base tables automatically. UNLOCK TABLES releases everything the session holds — including the global read lock from FLUSH TABLES WITH READ LOCK.
Key mechanics to get right:
- Alias discipline. The alias in
LOCK TABLESmust match the alias used by subsequent statements referencing that table in the same session — lockt1 AS aif you plan toSELECT ... FROM t1 AS a; locking the bare table name does not cover a later aliased reference, and vice versa. - Implicit commit.
LOCK TABLESunconditionally ends any active transaction first (equivalent to aCOMMIT), then acquires the locks. Starting a new transaction (outside autocommit) releases allLOCK TABLESlocks. Table locks and an open (non-autocommit) transaction cannot coexist. - Lock everything you touch. While locks are held, referencing any table not in the lock set errors with
ERROR 1100; writing to a table locked onlyREADerrors withERROR 1099. - InnoDB needs
innodb_table_locks=1(the default) forLOCK TABLESto actually block InnoDB-level access; with it off, no error is raised but the lock has no InnoDB-side effect. - Not usable in stored routines and not supported on Galera cluster nodes (may crash or deadlock the cluster).
TEMPORARYtables are always lockedWRITE, regardless of the requestedlock_type.
WAIT / NOWAIT
LOCK TABLE t1 WRITE WAIT 5; -- wait up to 5s, then error
LOCK TABLE t1 WRITE NOWAIT; -- fail immediately if unavailable
WAIT n bounds the wait (seconds) before erroring with a lock-wait-timeout; NOWAIT fails immediately (WAIT 0 is equivalent). The same WAIT/NOWAIT clause is accepted by ALTER TABLE, CREATE/DROP INDEX, DROP TABLE, OPTIMIZE TABLE, RENAME TABLE, TRUNCATE TABLE, and SELECT ... FOR UPDATE/LOCK IN SHARE MODE — it's a general DDL/lock-wait control, not specific to LOCK TABLES.
Metadata Locking (brief)
Every transactional or DDL statement takes an implicit metadata lock (MDL) on the objects it touches for the duration of the transaction/statement, independent of LOCK TABLES. This is what makes a concurrent ALTER TABLE wait behind an open transaction on that table, bounded by lock_wait_timeout (default 1 year — effectively unbounded unless overridden). LOCK TABLES ... WRITE also queues behind existing metadata locks. Full model, metadata_locks Performance Schema table: see the metadata-locking reference page. Row-level locking (SELECT ... FOR UPDATE) is covered in the mariadb-select skill, not here.
Named Locks — GET_LOCK() and Friends
GET_LOCK(str, timeout) -- 1 = acquired, 0 = timed out, NULL = error
RELEASE_LOCK(str) -- 1 = released, 0 = held by someone else, NULL = didn't exist
RELEASE_ALL_LOCKS() -- count of locks released by this session
IS_FREE_LOCK(str) -- 1 = free, 0 = in use, NULL = bad argument
IS_USED_LOCK(str) -- connection ID holding it, or NULL if free
These are application/advisory locks, keyed by an arbitrary string name, scoped server-wide (not per-database) — any two sessions that agree on a name cooperate, but any other session can also grab or squat on that name, deliberately or not. Namespace defensively: db_name.lock_name or app_name.lock_name.
- Multiple simultaneous locks per connection (since 10.0.2, MDEV-3917) — a session can hold any number of distinct named locks at once; acquiring a new one does not release previously held ones.
- Recursive re-acquisition: calling
GET_LOCK()again on a name the same connection already holds increments a reference count instead of blocking; it must be released the same number of times. - Independent of transactions —
COMMIT/ROLLBACKnever release a named lock. Release happens only viaRELEASE_LOCK(),RELEASE_ALL_LOCKS()(since 10.5.2), or the connection ending (cleanly or not). timeoutis in seconds but supports fractional/microsecond precision; a negative orNULLtimeout makesGET_LOCK()returnNULL(with a warning) rather than blocking or erroring outright.strempty orNULL→GET_LOCK()/RELEASE_LOCK()/etc. all returnNULLand do nothing; name matching is case-insensitive.- Deadlocks between named locks are detected automatically — the losing connection gets
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. - Not safe for statement-based replication; and on a replica applying via SQL thread,
GET_LOCK()is a no-op that always returns1— it never actually blocks, since replication is already serialized. - With the
metadata_lock_infoplugin (or theMETADATA_LOCK_INFO/performance_schema.metadata_lockstables) installed, named locks are visible alongside regular MDL entries asLOCK_TYPE = User lock.
See Also
mariadb-select— row-level locking (SELECT ... FOR UPDATE,LOCK IN SHARE MODE,SKIP LOCKED)mariadb-transactions— implicit commit, autocommit, and howLOCK TABLESinteracts with transactions- Canonical references on
mariadb.com/docs:- https://mariadb.com/docs/server/reference/sql-statements/transactions/lock-tables
- https://mariadb.com/docs/server/reference/sql-statements/transactions/transactions-unlock-tables
- https://mariadb.com/docs/server/reference/sql-statements/transactions/metadata-locking
- https://mariadb.com/docs/server/reference/sql-statements/transactions/wait-and-nowait
- https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/get_lock
- https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/release_lock
- https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/release_all_locks
- https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/is_free_lock
- https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/is_used_lock
This page is: Copyright © 2026 MariaDB. All rights reserved.