#10725 Retry the migration transaction on deadlock or lock timeout

️: Deadlock retry in migrate.ts. Attempt 1 of the #10591 production deploy was killed by a plain reader taking the tables in the other order, which no lock ordering can prevent. Catching SQLSTATE 40P01 and 55P03 and retrying the transaction is the fix. Do that before the next DDL migration ships.

The migration runner applies every pending migration in one transaction and takes ACCESS EXCLUSIVE locks on the tables it alters. Deploying migration 050 to production (hotfix #10723, run 34445025779) deadlocked on the first attempt: the migration held one table and waited for another, while an ordinary read transaction held the second and wanted a shared lock on the first. Postgres resolved the cycle by killing the migration. The dry run seconds earlier and the staging deploy had both gone through first time, and a manual rerun of the same deploy applied the migration in about nine seconds. Reader lock order cannot be controlled, so the runner should treat a deadlock (40P01) or a lock timeout (55P03) as retryable: roll back, wait briefly, and run the transaction again a bounded number of times before failing the deploy. Raising deadlock_timeout for the session is not an option on Supabase.

Open questions

How many attempts and what backoff feel right for a deploy step? Awaiting answer.

Confidence: 88%, ready to get started.