How long does LOCK TABLE hold its lock in a migration?

Explicit LOCK TABLE held for the rest of the migration

Warning: this works, but blocks traffic or rewrites data at scale.

What happens

LOCK TABLE holds its lock until the transaction commits. Migration runners wrap the file in one transaction, so an explicit lock taken early is held across every remaining statement, a manual outage whose duration is the rest of the migration.

Why it is dangerous on a populated table

It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.

Fires on

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

The safe pattern

Drop the explicit LOCK TABLE: DDL acquires exactly the lock it needs, and a SET lock_timeout guard bounds how long it may queue. If statements truly must be serialized against traffic, take the weakest sufficient lock mode as late in the migration as possible, never up front.

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;
-- the ALTER takes its own ACCESS EXCLUSIVE, guarded and brief,
-- no explicit LOCK TABLE needed

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (1)

lock access exclusive

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
UPDATE orders SET region = 'eu' WHERE region IS NULL;

Stays silent (1)

no explicit lock

UPDATE orders SET region = 'eu' WHERE region IS NULL;

How to check locally

Catch this before it ships

This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:

npx bolvrk check migration.sql --remote

Browse all 135 rules

← BV021 BV023 →