Lock Queues, Timeouts and Safe Migrations

Scenario

Example app: an e-commerce API on Postgres. A developer deploys a migration that adds a nullable column to orders — normally an instant operation. At the same time, an analyst's long report query is reading orders.

What you see in production

Within seconds, every request that touches orders hangs. Connection pools fill up, health checks fail, and the site is down for four minutes — until the report finishes. The migration itself then completes in 20 ms.

The naive code

ALTER TABLE orders ADD COLUMN gift_message text;   -- needs an ACCESS EXCLUSIVE lock, briefly

operations = [
    migrations.AddField("orders", "gift_message", models.TextField(null=True)),
]

Schema::table('orders', function (Blueprint $table) {
    $table->text('gift_message')->nullable();
});

Reproduce it

Locks are granted in queue order. The ALTER waits behind the long report; every later query waits behind the ALTER, even though they would be compatible with the report:

holding = [("report", "ACCESS SHARE", 240)]     # (who, lock, seconds left)
queue = [("migration", "ACCESS EXCLUSIVE")] + [(f"api request {i}", "ROW EXCLUSIVE") for i in range(1, 6)]

def conflicts(a, b):
    return "ACCESS EXCLUSIVE" in (a, b)          # simplified: exclusive conflicts with everything

def can_grant(request, ahead):
    _, mode = request
    if any(conflicts(mode, h[1]) for h in holding):
        return False
    return not any(conflicts(mode, q[1]) for q in ahead)   # fairness: don't jump a conflicting waiter

for i, req in enumerate(queue):
    granted = can_grant(req, queue[:i])
    print(f"{req[0]:15} wants {req[1]:16} -> {'granted' if granted else 'WAITING'}")
print("everyone waits ~240 s for the report to finish")

print("-- with SET lock_timeout = '2s' on the migration --")
print("migration      gives up after 2 s (ERROR: canceling statement due to lock timeout)")
print("api requests   proceed immediately; retry the migration later")

migration       wants ACCESS EXCLUSIVE -> WAITING
api request 1   wants ROW EXCLUSIVE    -> WAITING
api request 2   wants ROW EXCLUSIVE    -> WAITING
api request 3   wants ROW EXCLUSIVE    -> WAITING
api request 4   wants ROW EXCLUSIVE    -> WAITING
api request 5   wants ROW EXCLUSIVE    -> WAITING
everyone waits ~240 s for the report to finish
-- with SET lock_timeout = '2s' on the migration --
migration      gives up after 2 s (ERROR: canceling statement due to lock timeout)
api requests   proceed immediately; retry the migration later

Root cause

Most DDL needs a strong lock (Postgres ACCESS EXCLUSIVE; MySQL a metadata lock) even when the change itself is instant. If something holds a conflicting lock — a long query, an idle-in-transaction session — the DDL waits, and because lock requests queue fairly, every new query on that table queues behind the DDL. A 20 ms migration plus a 4-minute query equals a 4-minute outage.

The fix

Make DDL give up quickly instead of waiting, and retry:

SET lock_timeout = '2s';            -- fail fast if the lock isn't available
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN gift_message text;
-- retry with backoff a few times; alert if it never succeeds

SET SESSION lock_wait_timeout = 2;  -- metadata lock wait (default is 1 year!)
ALTER TABLE orders ADD COLUMN gift_message TEXT, ALGORITHM=INSTANT;

class Migration(migrations.Migration):
    atomic = True
    operations = [
        migrations.RunSQL("SET LOCAL lock_timeout = '2s'"),
        migrations.AddField("orders", "gift_message", models.TextField(null=True)),
    ]

public function up(): void
{
    DB::statement("SET lock_timeout = '2s'");
    Schema::table('orders', fn (Blueprint $t) => $t->text('gift_message')->nullable());
}

-- V42__add_gift_message.sql
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN gift_message text;

Plus app-level guard rails: statement_timeout for the web role (e.g. 5–10 s), reports on a replica, and idle_in_transaction_session_timeout so a forgotten session can't hold locks for hours.

Trade-offs

• A short lock_timeout makes migrations fail on busy tables; the deploy tooling needs retries and a way to run migrations at quieter times.
• Not all DDL is instant: rewriting a table (changing a column type, adding a volatile default) or a plain CREATE INDEX holds locks for the whole operation — use CREATE INDEX CONCURRENTLY, expand/contract migrations and online schema-change tools (covered in Scaling Databases).
• MySQL's lock_wait_timeout defaults to 31,536,000 seconds — always set it for migrations.

Detect it in production

-- Postgres: what is blocking the migration?
SELECT pid, wait_event_type, state, now() - query_start AS running_for, left(query, 60)
FROM pg_stat_activity
WHERE pid = ANY(pg_blocking_pids(<migration_pid>));

• MySQL: SHOW PROCESSLIST showing many Waiting for table metadata lock.
• Alert on queries waiting for locks longer than a few seconds; pages about "everything hangs, CPU idle" during deploys point here.

Key points

• DDL needs strong locks; a waiting DDL blocks every query queued after it.
• Always set lock_timeout (Postgres) / lock_wait_timeout (MySQL) for migrations, and retry.
• Cap statement and idle-in-transaction time for application roles.
• Use concurrent/online operations for anything that rewrites data.