Software QA / Intermediate: integrated product evidence / Lesson 8

Database integrity, transactions, and concurrency

Turn data rules into observable invariants and targeted integration cases. Examine nulls, uniqueness, rollback, and competing seat reservations without using production records.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Test constraints and application handling separately.
Design rollback and concurrent-write cases.
Explain database-specific test limitations.

Describe the state that must remain true

For a workshop, the number of active enrollments must not exceed capacity, every enrollment must reference an existing learner and course, and one learner must not hold duplicate active enrollments. These are state invariants. Some map naturally to primary keys, foreign keys, or unique constraints; capacity across multiple rows may require a transactional application design. Test both database enforcement and the application’s response when enforcement rejects a write. A friendly validation message does not replace durable protection against a second process writing at the same time.

Worked case: null is not a negative number

A fee column should contain a known nonnegative integer. In PostgreSQL, CHECK (fee_cents >= 0) alone does not reject NULL, because a check can be satisfied by an unknown result. Add NOT NULL when the business rule requires a value. In an isolated schema, attempt zero, a positive value, a negative value, and NULL as separate operations. Expect acceptance only for the first two. Also test that the application translates a rejected write into its agreed safe error instead of exposing database internals to the user.

sql Illustrative SQL for an isolated PostgreSQL schema

CREATE TABLE fee_demo (
    id integer PRIMARY KEY,
    fee_cents integer NOT NULL CHECK (fee_cents >= 0)
);
-- Independent cases: 0 -> accept; 500 -> accept; -1 -> reject; NULL -> reject.

The two constraints express different obligations. Run rejected statements separately or use savepoints; a failed PostgreSQL transaction needs recovery before further statements.

A transaction test must cross a meaningful failure point

Suppose confirming enrollment creates a row and decrements remaining capacity. Force a controlled error after the first write and before completion, then inspect from an independent connection after rollback. Expect neither partial enrollment nor capacity loss. A test that wraps everything in its own rollback may hide how the application commits or how another connection observes state. Choose isolation deliberately and reset the fixture between cases. Capture the operation ordering so the evidence demonstrates recovery from partial work rather than rejection before any work began.

Worked case: two attempts for one seat

Seed capacity one with no active enrollments. Coordinate two controlled clients so both attempt a reservation against the same initial state. The invariant is at most one successful enrollment and no negative capacity, regardless of which client wins. Verify the losing response and both final database counts. Simply starting two sequential requests is not a concurrency test; use a test barrier or controlled scheduler to reach the contested point. Repeat selected interleavings, while acknowledging that a few schedules do not prove correctness for every possible execution.

text State invariants across contention and rollback

Initial state | Two competing operations | Required final evidence
capacity=1, enrollments=0 | reserve A and reserve B | successes=1; active rows=1; remaining=0
Failure injection after row write | confirm A | active rows=0; remaining=1

The checks do not assume which actor wins, but they do require a single consistent outcome.

Match the database semantics that matter

An in-memory substitute may differ from the deployed engine in locking, null ordering, collation, constraint behavior, and transaction isolation. Use fast substitutes where their assumptions are acceptable, and run targeted integration checks on the intended engine for those risks. Test migrations with representative synthetic old data and verify transformed values and constraints, not merely successful execution. Never use a production database as an easy fixture. Record engine version and isolation settings with concurrency evidence, because these details affect interpretation far more than a generic “database test passed” statement.

Common errors and fixes

SQLite success is treated as PostgreSQL locking evidence

Why it happens: A substitute’s semantics differ from the target engine.
How to fix it: Run targeted contention tests on an isolated target-engine instance.
Prevent it next time: Classify engine-specific risks explicitly.

A CHECK is assumed to forbid NULL

Why it happens: Three-valued SQL logic is overlooked.
How to fix it: Add and test NOT NULL where required.
Prevent it next time: Include absent values in constraint test matrices.

A race test merely sends sequential requests

Why it happens: The contested state is never shared concurrently.
How to fix it: Coordinate operations at a controlled barrier.
Prevent it next time: Describe the tested interleaving and final invariant.

Check your understanding

1. A uniqueness check runs before INSERT. Why test a database constraint too?

Show worked solution

Two clients can both observe no existing row before either inserts. A unique constraint protects the durable state; the application must handle the losing write safely. Test simultaneous equivalent inserts and expect one row, one success, and the agreed duplicate outcome.

2. How do you verify a migration from decimal amounts to cents?