# Add Lock Timeouts to Alembic Migrations on PostgreSQL

Yes. An agent makes each Alembic migration set a PostgreSQL lock timeout in its own transaction, retries a blocked upgrade, proves it, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-migration-lock-timeouts/alembic/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads how migrations run before it changes the rules they run under. In a FastAPI app laid out like fastapi/full-stack-fastapi-template, `backend/app/alembic/env.py` builds an engine from `settings.DATABASE_URL` with `NullPool`, configures the context with `SQLModel.metadata` and `compare_type=True`, and calls `context.run_migrations()` inside one `context.begin_transaction()`, so the whole chain of revisions under `backend/app/alembic/versions` runs as a single transaction. `backend/alembic.ini` sets only `script_location` and logging. `backend/scripts/prestart.sh` runs `alembic upgrade head` and then `app/initial_data.py`, and `.github/workflows/test-backend.yml` runs that script against a `postgres:18` container before the tests. The latest revision, `fe56fa70289e`, adds a `created_at` column to `user` and `item` with `op.add_column`. On PostgreSQL each `ALTER TABLE` takes an `ACCESS EXCLUSIVE` lock; if a long read holds the table, the migration waits behind it, and every new query queues behind the migration. That is how a deploy turns into an outage.

It changes `env.py` so each migration runs with limits. Inside `run_migrations_online`, before `context.run_migrations()`, it executes `SET LOCAL lock_timeout` and `SET LOCAL statement_timeout` on the connection, with the values read from two new settings in `backend/app/core/config.py`, defaulted and documented in `.env.example`, so production can set a short lock wait and a longer statement limit. It passes `transaction_per_migration=True` to `context.configure`, so a chain of several revisions commits one at a time and a lock held by revision three does not keep revision one's changes uncommitted. `SET LOCAL` scopes both values to that transaction and nothing else on the connection.

When `lock_timeout` expires, PostgreSQL raises `lock_not_available`, which psycopg surfaces as `psycopg.errors.LockNotAvailable` and Alembic rolls back the migration cleanly. The agent makes `prestart.sh` retry `alembic upgrade head` a few times with a pause on that failure only, so a migration that lost the race to a long report retries after the report finishes instead of failing the deploy, while any other error still stops it on the first try. It also adds a note to `backend/app/alembic/README` on `op.get_context().autocommit_block()` for a future `CREATE INDEX CONCURRENTLY`, which cannot run inside the per-migration transaction.

It proves the behaviour in its sandbox with a PostgreSQL it installs there. With `DATABASE_URL` pointed at it, it runs the upgrade clean once, then opens a second session that takes a lock on `item` inside an open transaction, runs `alembic upgrade head` on a throwaway revision that alters `item`, and watches it fail with the lock error after the configured wait instead of hanging; it releases the lock and sees the retry in `prestart.sh` succeed. The throwaway revision is removed, and the agent runs `scripts/tests-start.sh` and then `scripts/lint.sh`, which runs `mypy`, `ty` and `ruff`.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add lock and statement timeouts to our Alembic migrations on PostgreSQL. Read backend/pyproject.toml, backend/alembic.ini, backend/app/alembic/env.py, the revisions under backend/app/alembic/versions, backend/app/core/config.py, backend/scripts/prestart.sh and .github/workflows/test-backend.yml first. In env.py, set lock_timeout and statement_timeout with SET LOCAL before running migrations, from two new settings with defaults documented in .env.example, and configure transaction_per_migration. Make prestart.sh retry the upgrade a few times only when the failure is the lock timeout. Add a README note about autocommit_block for concurrent index builds. Start a PostgreSQL in the sandbox, hold a lock on the item table from another session, run the upgrade with a throwaway revision to see it fail fast and then succeed on retry, remove the throwaway revision, run the test script and scripts/lint.sh, and open a pull request with the output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request that changes `env.py`, `config.py`, `.env.example`, `prestart.sh` and the Alembic README. The description shows the two `SET LOCAL` statements, the `transaction_per_migration` line, the retry loop, and the sandbox transcript of the blocked upgrade failing with the lock error and succeeding once the lock is released.

The run's record keeps both sessions, the one holding the lock and the one running Alembic, so a reviewer sees the wait end at the configured limit rather than reading a claim about it.

Before merging, a reviewer sets the production values: a lock wait short enough that a blocked migration gives up before the app's request queue fills, and a statement limit long enough for the slowest legitimate backfill. The pull request suggests reading them from the deploy environment rather than the code.

## Run it on every Linear issue labelled deploy-safety

A team makes this a rule. In the Project for each repository, an automation with the prompt above and a `linear` trigger on `linear.issue.labeled` for the label `deploy-safety` starts a run when someone files "the migration hung behind the nightly export and requests queued for ten minutes". The trigger adds the issue after the prompt, so the run reads which table was involved and makes sure the limits and the retry cover it.

Each run opens its own pull request on its own branch and links the issue. A repository whose `env.py` already sets the timeouts gets a description saying so and a pull request only for whatever is missing, such as the per-migration transaction or the retry.

## Why run it on Rush?

A migration that waits on a lock looks fine in every test environment, because nothing else is running there. The only way to see the failure mode is to hold a lock and run the migration against it, and the only way to trust the fix is to watch it give up at the limit and succeed on retry. The agent does both in its own sandbox with a real PostgreSQL and keeps the transcript.

The change is a few lines in `env.py` and a loop in a script, so review is quick, and a team with several services on this template runs them all at once, each in its own sandbox with its own pull request.

## Frequently asked questions

### Why SET LOCAL and not a value in the connection string?

A connection-string option applies to every statement on that connection for its whole life. `SET LOCAL` lasts for the current transaction, which with `transaction_per_migration` is exactly one migration, and the application's own connections in `core/db.py` are untouched.

### Does transaction_per_migration change what gets committed?

Yes, on purpose. Today the chain is one transaction, so a failure in the last revision rolls back all of them. With one transaction per migration, each revision commits as it finishes, so a retry resumes from the first one that failed. The pull request names this so a reviewer agrees to it.

### What about CREATE INDEX CONCURRENTLY?

It cannot run inside a transaction, so a migration that needs it wraps that statement in `op.get_context().autocommit_block()`. The agent adds the pattern to the Alembic README rather than to a revision, since no current revision builds an index that way.

### Will the retry loop mask real failures?

No. The loop retries only when the output contains the lock timeout error; a syntax error, a failed constraint or a bad connection still exits on the first attempt with the same code as before. The sandbox transcript in the pull request shows a lock failure retrying and nothing else.

## More Alembic jobs

- [Write Migration Rollback Tests for Alembic](https://getrush.ai/run/add-migration-rollback-tests/alembic/)
- [Add Constraint Naming Conventions to Alembic Migrations](https://getrush.ai/run/add-naming-conventions/alembic/)
- [Make a Nullable Column NOT NULL with an Alembic Migration](https://getrush.ai/run/add-not-null-constraint/alembic/)

## More jobs like this: Turn a Linear label into a PR

- [Add Modals to a discord.js Bot](https://getrush.ai/run/add-modals/discord-js/)
- [Add Model Contracts to a dbt Project](https://getrush.ai/run/add-model-contracts/dbt/)
- [Add a Fallback Model to a LangGraph Agent](https://getrush.ai/run/add-model-fallbacks/langgraph/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
