Advisory locks: locking ideas, not rows
Sometimes the thing you need to serialize isn't a row. "Only one migration at a time", "one cache rebuild at a time", "one cron instance per task" — there's no table to lock, because the resource is an idea. MySQL's tool for this is the user-level lock: GET_LOCK(name, timeout).
The manual: GET_LOCK "tries to obtain a lock with a name given by the string str, using a timeout of timeout seconds. … The lock is exclusive. While held by one session, other sessions cannot obtain a lock of the same name." It "returns 1 if the lock was obtained successfully, 0 if the attempt timed out".
Two deploy runners must not migrate the same database at once. They agree the name 'migration' means "migration in progress".
A> SELECT GET_LOCK('migration', 0) AS got_it;
got_it
--------
1
(1 row)
B> SELECT GET_LOCK('migration', 0) AS got_it; -- timeout 0 — an instant answer, no waiting
got_it
--------
0
(1 row)A positive timeout waits, but only that long. B gives it one second:
B> SELECT GET_LOCK('migration', 1) AS got_it; -- A still holds it — B gets 0 after the deadline instead of an error
got_it
--------
0
(1 row)
B> SELECT IS_FREE_LOCK('migration') AS free; -- you can ask without trying to take it
free
------
0
(1 row)Advisory locks ignore transaction boundaries entirely — COMMIT releases nothing.
A> BEGIN;
Query OK
A> COMMIT;
Query OK
B> SELECT GET_LOCK('migration', 0) AS got_it; -- A's COMMIT changed nothing — the lock belongs to A's SESSION
got_it
--------
0
(1 row)
A> SELECT RELEASE_LOCK('migration') AS released;
released
----------
1
(1 row)
B> SELECT GET_LOCK('migration', 0) AS got_it; -- now B is the migration runner
got_it
--------
1
(1 row)One session can hold many named locks; RELEASE_ALL_LOCKS drops the lot. Disconnecting releases them too — a crashed runner can't jam the queue forever.
B> SELECT GET_LOCK('cache-rebuild', 0) AS got_it;
got_it
--------
1
(1 row)
B> SELECT RELEASE_ALL_LOCKS() AS released;
released
----------
2
(1 row)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Session-level only — and that's the sharp edge
The transcript's middle section is the part that bites people: transactions are irrelevant to these locks. The manual, verbatim: "Locks obtained with GET_LOCK() are not released when transactions commit or roll back." There is no transaction-scoped variant — PostgreSQL has both (pg_advisory_lock and pg_advisory_xact_lock); MySQL gives you session-scoped or nothing. Release paths, per the manual: "released explicitly by executing RELEASE_LOCK() or implicitly when your session terminates (either normally or abnormally)."
The implicit release is the crash-safety story: a deploy runner that dies takes its session — and its migration lock — with it. But it's also the connection-pool trap: a pooled connection doesn't terminate when your request ends. Forget to release, return the connection to the pool, and the lock lives on in a healthy idle session that no code remembers owning. With a pool, always release in a finally, or pin the lock to a dedicated connection.
A few operational notes worth keeping. GET_LOCK(name, 0) is a try-lock, a positive timeout is a bounded wait that returns 0 rather than erroring when it expires, and -1 waits forever. The locks are session-scoped, so COMMIT and ROLLBACK never touch them and only a disconnect or crash releases them — which, with pooled connections, means nothing auto-releases, so reach for finally. IS_FREE_LOCK peeks without taking, RELEASE_ALL_LOCKS() returns how many locks it dropped, and because names are server-global you'll want to prefix them (myapp:migration) on a shared server.