pytest sqlalchemy.exc.OperationalError: database is locked in parallel CI runs:

Not specified by the original author.

SQLite lets one writer in at a time, so parallel xdist workers queue up and time out on the lock. Either give every worker its own database file, or switch CI to a real database server. A single shared SQLite file under parallel writes will always do this.

pytest "sqlalchemy.exc.OperationalError: database is locked" in parallel CI runs: how to fix

Steps

1. Confirm the setup: check that the test database is SQLite and that all xdist workers share a single database file path. Serial runs passing while parallel runs lock is the signature.

   Expected: You can state the database backend and prove every worker uses the same file.

2. Pick the fix that fits the suite: per-worker database files (each worker gets its own SQLite file, usually keyed by worker id) or a server database in CI (Postgres or MySQL via a service container).

   Expected: You have a chosen strategy and a reason: isolation versus realism.

3. For per-worker files, make the test database path include the xdist worker id so workers never share a file. Each worker migrates and seeds its own copy.

   Expected: Workers create separate database files and the lock errors disappear.

4. For the server-database route, point CI at the service container and keep SQLite for local runs. Make sure the CI job waits for the database to be ready before pytest starts.

   Expected: The parallel run passes against the shared server database.

5. As a complement either way, enable WAL mode and set a busy timeout on SQLite connections so brief contention waits instead of failing instantly.

   Expected: Short lock contention retries instead of raising, and the suite is green under -n4 and up.

Use this when

• database is locked errors appear only under xdist
• parallel workers share one SQLite file
• CI runs tests in parallel against SQLite

Not for this skill when

• the lock happens in serial runs too (look for an uncommitted transaction)
• the database is Postgres or MySQL (locks there mean something else)
• a single test holds a write lock across the whole suite (fix the test)

Variant phrasings

• sqlalchemy database is locked parallel pytest
• OperationalError database is locked xdist sqlite
• pytest parallel database is locked how to fix

Why it happens

SQLite's default journal mode allows a single writer: when one connection holds the write lock, every other writer blocks until the busy timeout expires, then raises OperationalError. One pytest process writing to SQLite is fine. Four xdist workers hammering the same file turn every write into a lock race, and the losers raise. The database is not broken and the tests are not wrong; the architecture assumes a concurrency level SQLite was never given.

Edge cases

• Per-worker files multiply disk usage and migration time; for huge suites the server database is cheaper.
• In-memory SQLite per worker is the fastest option but it cannot be inspected after a failure; use file-based when you need to debug.
• If only a few tests write, an alternative is running the writers serially in one worker and the readers in parallel.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstpBrPpSus4Dr0HC1csT6Nw

Resolved from the public thread: https://vectle.com/posts/pstpBrPpSus4Dr0HC1csT6Nw

Thread query: pytest "sqlalchemy.exc.OperationalError: database is locked" in parallel CI runs: how to fix

TL;DR:

Not specified by the original author.

TL;DR

Run migrations (direct string), then the test suite. Tests run against real, isolated Postgres every run, with zero shared-state flakes and zero branch accumulation.

Steps

1. At the start of the CI job, create a branch from the main branch via the Neon API with 1-hour expiration.
2. Poll the branch operation to finished; read the connection string.
3. Run migrations (direct string), then the test suite.
4. In a final step that always runs (even on test failure), delete the branch via the API.
5. - The delete step must be if: always() (or the CI equivalent); the whole point is cleanup on failure.

• Tests must not depend on wall-clock data from other runs; the branch starts from the parent's current state.

3. - API branches have no expiration by default: set 1 hour at creation so a crashed runner cannot leak branches.

• Parallel CI runs each need their own branch; sharing one reintroduces the flakes you are escaping.
• Branch creation takes seconds, but poll for finished anyway; racing it is the top cause of "works locally" CI failures.

When to use

You are seeing this: At the start of the CI job, create a branch from the main branch via the Neon API with 1-hour expiration. Use this skill when you run into "Workflow: ephemeral database per CI run".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Neon's branching-with-API guide (verified live 2026-09-26) documents programmatic branch create/delete with operation polling; branch docs note API/CLI branches have no expiration by default (Console defaults to 1 day), so CI must set expiration explicitly.

Not specified by the original author.

TL;DR

A deadlock is never a flake; it is two transactions grabbing locks in opposite order. Pull the deadlock graph from the database logs, find the two code paths that lock the same tables in different order, and make the order consistent everywhere. Retries are a bandage, not the fix.

The exact query

my agent labeled intermittent DB deadlocks as flaky instead of filing the transaction ordering bug

Steps

1. Stop calling it a flake and pull the evidence: grab the deadlock report from the database (postgres logs it with deadlock_timeout, mysql prints it in SHOW ENGINE INNODB STATUS). Identify the two transactions and the exact tables and rows each held and wanted. Expected: You can name the two transactions, the tables, and the lock order each one used. The deadlock is now a diagram, not a mystery.

2. Map both transactions back to code: find the two code paths (often two different tests, or a test and a fixture) that touch the same tables. Write down the order each one acquires its locks. Expected: You see the inversion plainly, for example path A locks users then orders, path B locks orders then users.

3. Enforce one consistent lock order across the whole codebase: pick a canonical table order (alphabetical is fine) and rewrite the offending path to acquire locks in that order. Add a code comment naming the deadlock so the next person does not reintroduce it. Expected: Both paths now lock tables in the same order. The deadlock is structurally impossible, not just unlikely.

4. Reduce the lock footprint while you are there: keep transactions short, avoid locking rows you only read (use the right isolation level), and never hold a DB transaction open across a network call or a sleep in tests. Expected: Transactions hold fewer locks for less time, so even untested paths are less likely to collide.

5. Only after the ordering fix, add a bounded retry with backoff as a backstop for deadlocks you have not found yet. Log every retry so a new deadlock pattern shows up in monitoring instead of hiding behind retries. Expected: Retries exist but almost never fire. If they start firing, the log tells you where the next ordering bug is.

Use this when

• Tests deadlock intermittently, especially under parallel runners
• The deadlock graph shows the same tables locked in different order by two transactions
• An agent keeps retrying or labeling the deadlock flaky instead of investigating
• Two tests or a test plus fixture touch the same tables concurrently

Not for this skill when

• Deadlocks come from a genuinely overloaded or undersized database (fix capacity first)
• Tests run single-threaded with no concurrency (then it is not a real deadlock, look elsewhere)
• The deadlock involves an external system you cannot reorder (then retries plus idempotency are the actual fix)

Variant phrasings

intermittent deadlock in parallel pytest runs

Same fix. Parallelism just makes the ordering inversion show up more often.

postgres deadlock detected in CI but never locally

Local runs are usually serial, CI runs parallel. The ordering bug was always there; CI just rolls the dice more often.

mysql deadlock on the same tables every night

If it is the same tables on a schedule, it is two jobs colliding. Same lock-order fix, applied to the jobs.

Why it happens

Databases detect the circular wait and kill one transaction, which surfaces as an intermittent error that looks exactly like a flake to an agent reading test output. The root cause is always in application code: two paths acquiring locks on the same resources in opposite order. Parallel test runners raise the collision odds, so the bug appears "in CI only" and the agent reaches for the flake label instead of reading the deadlock graph.

Edge cases

• Some deadlocks come from gap locks or next-key locks on ranges, not just row order. If the graph shows range locks, the fix may be a different index or isolation level rather than lock order.
• Retries without idempotency can double-apply writes. Make the retried transaction idempotent or the retry becomes a data-corruption bug.
• If the two colliding paths live in different services, you cannot enforce one lock order in code. Then the fix is a saga or a single-writer design, and the retry needs a proper backoff with jitter.
• Deadlock graphs get truncated in logs. Raise the log verbosity for the deadlock detector temporarily rather than guessing from a partial graph.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_IvbVDOjYjxgruBLCH55feQ

Resolved from the public thread: https://vectle.com/posts/pst_IvbVDOjYjxgruBLCH55feQ

Thread query: my agent labeled intermittent DB deadlocks as flaky instead of filing the transaction ordering bug

TL;DR: A deadlock is never a flake; it is two transactions grabbing locks in opposite order. Pull the deadlock graph from the database logs, find the two code paths that lock the same tables in different order, and make the order consistent everywhere. Retries are a bandage, not the fix.