Migration lock safety

​

What Prisma does (verified on Prisma 7.8.0, Postgres 14 and 18)

• migrate deploy does not wrap a file in one transaction. Unless the file has an explicit BEGIN and COMMIT, each statement runs on its own. A bare SET LOCAL lock_timeout = '5s'; at the top of a file has no effect: SHOW lock_timeout on the next statement returns 0 (wait forever).
• A DROP TABLE of a table with a foreign key to a live table takes ACCESS EXCLUSIVE on the referenced table, and waits indefinitely when something holds a conflicting lock. Every request that touches the referenced table queues behind it.
• With an explicit BEGIN; SET LOCAL lock_timeout = '...'; ... COMMIT; the timeout fires and the script rolls back.
• A rolled-back script still leaves a failed _prisma_migrations row (finished_at and rolled_back_at both null). Every later migrate deploy then fails with P3009 until someone runs prisma migrate resolve --rolled-back <name>.
• Prisma reports the aborted transaction, not the timeout. The surfaced error is current transaction is aborted, commands ignored until end of transaction block, not the lock timeout text. Do not grep for the cause in the Prisma output; read the Postgres log for canceling statement due to lock timeout.
• migrateDeploy.ts retries only lock-contention text (P1002, advisory lock) and P1001. It does not retry a lock-timeout failure, and since the failed row blocks the next attempt, it could not help if it did. P3009 is logged with the migration name and a pointer to this page, never retried, and the process exits 1 (encore.service.ts), so the revision fails to start.

​

Required pattern for lock-taking DDL

1. An explicit BEGIN; as the first statement and COMMIT; as the last. SET LOCAL is only valid inside it.
2. SET LOCAL lock_timeout = '2s'; right after BEGIN.
3. Each risky statement in its own DO block that retries a bounded number of times on lock_not_available (SQLSTATE 55P03) or deadlock_detected (40P01), with pg_sleep between attempts. The DO block’s inner BEGIN ... EXCEPTION is a subtransaction, so a caught deadlock rolls back only that attempt and the next one starts clean.
4. A defined outcome when the attempts run out, chosen by whether the change is optional:
   • Optional cleanup (dropping an obsolete table or column that no running code reads): a non-fatal RAISE NOTICE. The object stays, and a later idempotent migration drops it once the table is quiet. The migration never fails on lock contention. The template below does this.
   • Required change (an ADD CONSTRAINT, a type change, or a drop that the new code depends on): never skip silently. A skipped statement still commits, Prisma records the migration as applied, and it is never retried, so the schema the new code needs would be missing. Replace the RAISE NOTICE with RAISE EXCEPTION 'users_email_key not added after % attempts', attempt;. The transaction rolls back, the deploy fails, and you recover with the P3009 runbook below. Where a failed deploy is not acceptable, split the work: ship the change in a later migration or a separate step that you confirm has finished.
5. IF EXISTS / IF NOT EXISTS on every statement, so a replay and a later retry are no-ops (.cursor/rules/sql-migration-idempotence.mdc).

• lock_timeout applies to each lock wait, not to the whole script. statement_timeout is a separate setting.
• Locks acquired earlier in the transaction are held until COMMIT, including across the pg_sleep. Keep the transaction small, and put the lock-taking statements first.
• The EXCEPTION block rolls back only its own subtransaction, so the transaction stays usable and COMMIT still succeeds.
• Put statements that need no retry (backfills, CREATE TABLE IF NOT EXISTS) in the same transaction, outside the DO blocks.
• A DROP TABLE that has a foreign key to a live table: drop the constraint in its own retrying DO block first, so the wait is on the child table.

​

Copy-paste template (optional cleanup)

BEGIN;
SET LOCAL lock_timeout = '2s';

DO $$
DECLARE
  max_attempts constant int := 5;
  attempt int := 0;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      DROP TABLE IF EXISTS "old_thing";
      EXIT;
    EXCEPTION WHEN lock_not_available OR deadlock_detected THEN
      IF attempt >= max_attempts THEN
        RAISE NOTICE 'old_thing not dropped after % attempts; a later migration retries it', attempt;
        EXIT;
      END IF;
      PERFORM pg_sleep(attempt);
    END;
  END LOOP;
END
$$;

COMMIT;

​

Audit table_config seeds

• The seed runs in one BEGIN … COMMIT with SET LOCAL lock_timeout = '2s'.
• Before the re-bake it locks every table the re-bake touches, in name order: the tables with an audit_log_change trigger, the re-attach list, and the org_parent_path parents.
• A lock timeout rolls back that round’s subtransaction, which releases every lock taken in the round. The block then sleeps for min(attempts on that table, 5) seconds and starts a new round. No lock is held during the sleep.
• Each table gets 5 attempts and the rounds stop at 20. When either budget runs out, the block raises audit trigger refresh could not lock <table> ... (SQLSTATE 55P03). The whole seed rolls back and the deploy fails. Recover with the P3009 runbook below.

​

Check

• uses SET LOCAL in a statement that is not between an explicit BEGIN and its COMMIT, or
• contains DROP TABLE or ALTER TABLE ... DROP outside a DO block and outside a BEGIN … COMMIT span.

​

Recover from P3009

[api-events] prisma migrate deploy BLOCKED (P3009): failed migration(s) in _prisma_migrations: <name>. Not retrying; ...

​

1. Reach the environment’s database

ENV=qa
GCP_PROJECT=qa-soundcheck-live
export DATABASE_URL="$(gcloud secrets versions access latest --secret="${ENV}-DatabaseUrl" --project="$GCP_PROJECT")"

​

2. Identify the failed migration

SELECT migration_name, started_at, logs
  FROM _prisma_migrations
 WHERE finished_at IS NULL AND rolled_back_at IS NULL;

​

3. Decide what the failed script left behind

• A script with an explicit BEGIN / COMMIT rolled back completely. Nothing it did is in the database.
• A legacy script without BEGIN ran statement by statement. Earlier statements committed. Inspect the schema, then either finish the rest by hand and mark it applied (--applied in step 4), or roll it back and let it re-run (it must be replay-safe, which the idempotence policy requires of new migrations).

​

4. Resolve, verify, redeploy

cd api-events
./node_modules/.bin/prisma migrate resolve --rolled-back <migration_name> --config prisma.config.ts
npm run db:migrate:status

• No environment has applied it. The rolled-back migration was never applied, so you may edit its SQL to the pattern above, then redeploy.
• Another environment already applied it (so the file must stay as it is, pitfall 17), and it keeps failing in this one. A later repair migration cannot help: after --rolled-back, migrate deploy runs the unchanged file again before any newer migration. Pick one:
  • Remove the blocking condition, then redeploy the unchanged file. For a lock timeout, find the holder with SELECT pid, state, query, xact_start FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start; (the Postgres log line is canceling statement due to lock timeout), end that transaction or wait for it, and redeploy in a quiet window.
  • Finish the change by hand in this database (run the file’s statements yourself in one BEGIN … COMMIT, with the same lock handling), then mark it applied with ./node_modules/.bin/prisma migrate resolve --applied <migration_name> --config prisma.config.ts instead of --rolled-back. Confirm the resulting schema matches what the other environments have before you do.