Database Testing Course / Lesson 8 of 10

Concurrency and Isolation Testing

Use coordinated transactions to expose lost updates, duplicate work, lock contention, and retry defects under the target database's actual isolation rules.

Reproduce concurrency deliberately

A sequential test cannot prove behavior when two clients act at once. Use two independent connections and explicit synchronization barriers so both transactions reach the intended point before either continues. Add timeouts so a failing test cannot hang the suite.

  1. Prepare known shared data in a disposable database.
  2. Start transaction A on connection A and pause at a controlled barrier.
  3. Start transaction B on connection B and perform the competing operation.
  4. Release both at a deterministic point and capture results/errors.
  5. Assert the allowed final state and clean up the fixture.

Test a conditional update

For inventory or balances, protect the invariant in the database operation rather than relying only on a prior read.

UPDATE inventory
SET available = available - 1
WHERE sku = 'TEST-1'
  AND available > 0
RETURNING available;
-- With one item and two concurrent buyers:
-- assert no negative value and at most one successful reservation.

This example must run against a fixture in an isolated test database. The exact expected error/result depends on application logic and transaction isolation.

Test isolation and retry behavior

  • Check the transaction guarantees required by the business operation.
  • Test unique constraints as a final defense against duplicate concurrent inserts.
  • Test deadlock or serialization-failure handling where the engine/isolation mode can produce them.
  • Retry only errors known to be transient; use bounded attempts and backoff.
  • Make externally retried requests idempotent so a lost response does not create duplicate writes.
  • Verify connection state is cleaned up after a failed or cancelled transaction.

Avoid false confidence

Use the production database engine and supported isolation level. Keep workload bounded, label test clients, and do not run contention experiments on shared staging without coordinating with its owner. A test should assert invariants, not require a particular scheduler interleaving unless the barrier controls it.

Progress stays in this browser.