GopherForgeA WORKBENCH FOR GO

Around Go: SQL services · 3 MIN READ

SQL isolation and deadlocks: test the business invariant

Explain why atomic statements alone may not preserve a multi-row invariant.

A transaction can be atomic without providing the isolation an invariant needs. Two transactions may both read a condition as true and each make a different write that breaks the combined rule. Exact behavior depends on the database and isolation level; this lesson uses PostgreSQL terminology explicitly.

SQL isolation and deadlocks: test the business invariant · A MAP OF THE IDEA

  1. State the invariant
  2. Choose isolation or locks
  3. Force a concurrent schedule
  4. Assert durable state

Useful when

  • Explain why atomic statements alone may not preserve a multi-row invariant.
  • Choose isolation and locking from the invariant, understand the database-specific failures and prove the protocol with concurrent transactions on the actual engine.

Watch out

  • Isolation levels and lock details are database-specific. Do not promise one engine’s exact behavior for every SQL driver.

Implementation & operation cost: Choose the policy from the actual contract, then verify its failure path in the target environment.

01

Name the anomaly

Imagine two on-call doctors, each allowed to leave only while another remains. Both transactions read two active doctors and each removes its own row. The final state violates the rule even though every individual write is valid. This is a business-level concurrency problem, not an unsynchronized Go map access.

02

Choose a database-enforced protocol

A suitable constraint, conditional update, lock protocol or serializable transaction can protect an invariant, depending on how it is represented. PostgreSQL serializable isolation may reject a transaction that would create an incompatible history. Applications must handle that failure rather than treating serializable as a promise that no request ever retries.

go
tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
if err != nil {
	return err
}
defer tx.Rollback()
// Read and write the complete invariant using tx and the same ctx.
// A documented serialization failure can require retrying the whole unit.
return tx.Commit()
Read the code

This native transaction skeleton imports database/sql and assumes a driver supporting the requested isolation. It deliberately omits application SQL: the correct statements depend on the invariant. Test the protocol against the actual database rather than a fake that always accepts commits.

03

Use consistent lock order

Two transfers can lock accounts in opposite orders and deadlock. Acquire relevant locks in a consistent order where possible, keep transactions short and avoid holding locks across remote calls. Deadlock detection prevents one kind of permanent wait by aborting work; it does not make the application transition automatically succeed.

04

Reproduce with two independent connections

Use synchronization barriers to place both transactions after their initial reads, then release their writes. Assert the final invariant and the expected failure/retry behavior. A process-local mutex cannot establish the same guarantee across every service replica, and the race detector does not observe database interleavings.

TAKE THIS WITH YOU

Choose isolation and locking from the invariant, understand the database-specific failures and prove the protocol with concurrent transactions on the actual engine.

Check your understanding

Two replicas pass Go race tests but violate a multi-row invariant. What evidence is missing?

Go to the source

Progress stays in this browser. No account needed.