Coordinating Access to a Shared Test Database

Shared test databases fail in more interesting ways than shared servers. A server is either running your branch or someone else's. A database can be half-migrated, partly reset, full of another test run's records, or locked by a long transaction nobody remembers starting.

Most teams have at least one shared database they can't easily give everyone a copy of: a staging database with realistic data, a test database seeded with records that took days to assemble, or a database behind a shared environment. This post covers how to decide what to isolate, what to coordinate, and the specific patterns that keep a shared database usable.

Postgres behaviour described here is from the PostgreSQL documentation (current as of 2026). Other databases have equivalents, but check their documentation for the details.

What actually conflicts

Four kinds of activity cause nearly all the trouble:

Activity	How it breaks things for others
Schema migrations from a branch	Changes tables everyone uses; other branches' code may no longer work against the schema
Data resets or refreshes	Wipes or replaces data someone is relying on mid-test
Parallel test runs	Two suites create, modify and delete the same records
Long-running jobs or transactions	Holds locks that block or slow everyone else

Reads are rarely the problem. Someone querying the database to debug something doesn't break anyone. The conflicts come from writes that change shared state: structure, bulk data, or the specific records another person depends on.

That gives you the first rule: coordinate writes that change shared state, and leave reads alone.

Step 1: Isolate what you can

Before coordinating access, reduce how much has to be shared. Every test run that gets its own database is one less thing to coordinate.

Throwaway databases for automated tests. Most automated test suites don't need shared data. Spinning up a fresh database per run, in a container in CI or locally, removes parallel-run conflicts entirely.

Template databases for fast copies (Postgres). If tests need a known dataset, keep it in a template database and clone it per run:

CREATE DATABASE test_run_4821 TEMPLATE seeded_template;

or from the shell:

createdb -T seeded_template test_run_4821

The PostgreSQL docs note the main limitation: no other sessions can be connected to the source database while it's being copied. CREATE DATABASE fails if any other connection exists when it starts. So keep the template database as a template only, and don't let applications or people connect to it.

Schema per developer or per branch. Where a separate database is too heavy, a schema per developer (dev_alice, dev_bob) gives you isolation within one database server. It works well for application code that can be pointed at a schema via search_path.

After this, what's left on the shared database should be things that genuinely need to be shared: realistic data, integrations that point at it, or an environment that depends on it.

Step 2: Decide what needs a lock

For the remaining shared database, decide which activities need exclusive access:

Activity	Needs exclusive access?
Reading and debugging	No
Manual QA using existing records	Usually no, unless the test modifies shared records
Running a migration	Yes
Resetting or refreshing data	Yes, and announce it
Running a destructive or bulk-write test suite	Yes
Load testing against it	Yes

Write the list down and share it. Most collisions happen because someone didn't realise their activity affected others.

Step 3: Use advisory locks for scripts that must not overlap

For automated processes that touch the database, such as migration runners or reset scripts, the database itself can enforce mutual exclusion. Postgres has advisory locks: application-defined locks that the server stores but doesn't enforce on its own. Your code takes the lock; other code that asks for the same lock waits.

There are two flavours, according to the PostgreSQL docs:

• Session-level (pg_advisory_lock): held until explicitly released or the session ends. Not tied to transactions: a lock acquired in a transaction that rolls back is still held. Lock requests stack, so each lock needs a matching unlock.
• Transaction-level (pg_advisory_xact_lock): released automatically at the end of the transaction, with no explicit unlock.

For a migration runner, a transaction-level lock is usually simplest:

BEGIN;
-- Any 64-bit key; pick one constant for "migrations on this database"
SELECT pg_advisory_xact_lock(727001);

-- run migration statements here

COMMIT;  -- lock released automatically

A second runner reaching the same pg_advisory_xact_lock(727001) waits until the first commits or rolls back.

For a reset script that runs outside a single transaction, use the session-level form and make sure you unlock:

SELECT pg_advisory_lock(727002);
-- truncate, reload, reseed
SELECT pg_advisory_unlock(727002);

If the script crashes, the lock is released when its connection closes.

Two cautions. First, advisory locks only coordinate code that uses them. A person running a migration from a laptop with a different tool won't take the lock. Second, the lock lives in the database, so nobody outside it can see who holds it or why. A QA engineer who wants to know "is someone resetting the data?" can't easily find out from an advisory lock.

Step 4: Coordinate people with a visible lock

Advisory locks cover scripts. People need something they can see and use from where they work. The requirements are the same as for any shared environment:

• Before migrating, resetting, or running a destructive suite, take a lock on the database.
• The lock shows who has it and until when.
• It expires on its own, so a forgotten hold clears.
• People waiting are notified when it's released.
• CI takes the same lock before running migrations or destructive tests.

Treat the database as its own resource, separate from the application environment. A developer deploying a branch to staging doesn't necessarily need the database; a developer testing a migration does. Locking them separately lets the two happen independently.

Step 5: Announce resets and refreshes

Data refreshes are the most disruptive activity because they affect everyone, including people only reading. Even with a lock, announce them:

1. Lock the database for the refresh window.
2. Post in the team channel: what's being refreshed, when, and how long it takes.
3. Run the refresh.
4. Release the lock, which notifies anyone waiting.

If refreshes happen on a schedule, put the schedule in the database's description wherever people look it up.

Doing step 4 with Lockit

Lockit is a chat bot for reserving shared resources in Slack, Discord and Microsoft Teams. Create a resource for the database with @Lockit new (for example staging-db, with a description like "Lock before migrations, resets or destructive tests"), and optionally a channel such as #staging where lock and release updates are posted.

Before a migration:

@Lockit lock staging-db

After:

@Lockit release staging-db

Set the resource's default duration to match a typical migration or reset, for example 1 hour, so a forgotten lock clears quickly instead of falling back to the workspace default (48 hours if nothing is set). Anyone who needs the database while it's locked can click "Alert when released", or "Ask for takeover" if it's urgent, which sends the holder Approve / Reject Request buttons.

For CI, on paid plans, a migration job can lock the same resource through the REST API:

body=$(curl -sS -X POST \
  -H "X-API-KEY: $LOCKIT_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"userEmail":"ci@yourcompany.com","duration":30}' \
  https://api.lockitbot.com/api/locky/resources/staging-db/lock)

case "$body" in
  Error:*) echo "staging-db is locked: $body"; exit 1 ;;
esac

Note that a refused lock returns HTTP 200 with a body starting Error:, so check the body rather than the status code. Release in a step that always runs. The full pattern, with GitHub Actions and GitLab CI examples, is in how to lock a staging environment from your CI/CD pipeline.

The two layers work together: Lockit makes the hold visible to people and to CI across systems, and an advisory lock inside the migration runner guarantees two runners can't overlap even if someone skips the chat step.

A shared test database policy

1. Automated test suites use their own throwaway database or a clone from a template. They don't touch the shared database.
2. The shared database is locked (staging-db) before any migration, reset, refresh, bulk write or load test.
3. Reading and debugging don't need a lock.
4. Default lock is 1 hour. Ask for longer when you lock if you need it.
5. Refreshes are announced in the team channel before they start.
6. Migration runners also take a Postgres advisory lock, so two can never overlap.

For the broader picture of how the database fits into environment sharing, see how to manage shared staging environments and dev environment reservation best practices.

FAQ

Should every developer have their own test database?

Where it's cheap, yes. Containers, template databases and per-developer schemas remove most conflicts. Keep a shared database only for what genuinely needs to be shared, such as realistic data or integrations that point at it.

Are Postgres advisory locks enough on their own?

For scripts that all use them, they reliably prevent overlap. They don't help people see who holds the database, and they do nothing about tools or people that don't take the lock. Use them alongside a visible lock, not instead of one.

Should the database be locked separately from the environment?

Usually. Deploying a branch and running a migration are different activities with different risks. Separate resources let a developer test a UI change on staging while someone else waits for the database, or the other way round.

How do we stop parallel test runs from colliding?

Give each run its own database. If that's not possible, give each run its own schema or a unique prefix on the records it creates, and have the suite clean up only its own records.