Part 010 — Isolation Levels and Application Invariants

1. Tujuan Bagian Ini

Bagian ini membahas isolation level sebagai alat untuk menjaga application invariants di bawah concurrency.

Kita tidak akan berhenti pada definisi textbook:

Read committed prevents dirty reads.
Repeatable read prevents non-repeatable reads.
Serializable is highest isolation.

Definisi seperti itu terlalu dangkal untuk production engineering.

Target kita:

1. memahami perilaku nyata isolation level PostgreSQL;
2. membedakan anomaly yang relevan untuk domain aplikasi;
3. memilih isolation level berdasarkan invariant, bukan kebiasaan;
4. tahu kapan cukup memakai constraint atau conditional update;
5. tahu kapan butuh explicit lock;
6. tahu kapan serializable isolation masuk akal;
7. tahu bagaimana retry 40001 secara benar di Java;
8. tahu mengapa “semua method pakai @Transactional” bukan desain correctness.

Kalimat penting:

Isolation level bukan jaminan magic. Isolation level adalah trade-off antara concurrency, blocking, abort/retry, dan jenis anomaly yang masih mungkin terjadi.

2. Mental Model: Invariant First, Isolation Second

Kesalahan umum adalah memilih isolation level dulu.

Lebih baik:

1. Tentukan invariant domain.
2. Tentukan data yang terlibat dalam invariant.
3. Tentukan operasi concurrent yang bisa melanggarnya.
4. Pilih mekanisme proteksi paling kecil yang cukup:
   - constraint
   - atomic conditional update
   - optimistic version
   - pessimistic row lock
   - exclusion/unique constraint
   - advisory lock
   - serializable transaction
5. Desain retry/compensation path.

Contoh invariant:

At most one active policy per customer.

Mekanisme terbaik sering bukan isolation level tinggi, tetapi constraint:

CREATE UNIQUE INDEX one_active_policy_per_customer
ON policy(customer_id)
WHERE status = 'ACTIVE';

Contoh invariant lain:

Total approved budget per department must not exceed limit.

Ini cross-row aggregate invariant. Constraint sederhana tidak cukup. Kita perlu salah satu:

• lock department row sebagai aggregate root;
• maintain allocated budget counter dengan conditional update;
• serializable transaction dengan retry;
• redesign menjadi reservation ledger.

3. PostgreSQL Isolation Levels

PostgreSQL menyediakan empat nama isolation level SQL:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Namun perilaku pentingnya:

• READ UNCOMMITTED diperlakukan seperti READ COMMITTED di PostgreSQL;
• default biasanya READ COMMITTED;
• REPEATABLE READ memakai snapshot transaksi yang stabil;
• PostgreSQL REPEATABLE READ tidak mengizinkan phantom read dalam arti umum karena snapshot tidak berubah;
• SERIALIZABLE memakai Serializable Snapshot Isolation/SSI dan dapat membatalkan transaksi dengan serialization failure;
• aplikasi yang memakai SERIALIZABLE harus siap retry transaksi.

Ringkasan praktis:

Catatan: tabel di atas adalah model PostgreSQL, bukan sekadar ANSI textbook.

4. Read Committed: Statement-Level Snapshot

READ COMMITTED adalah default yang sering dipakai aplikasi OLTP.

Mental model:

Setiap statement melihat snapshot baru dari data yang sudah commit sebelum statement mulai.

Contoh:

Session A:

BEGIN;
SELECT balance FROM account WHERE id = 1;
-- sees 100

Session B:

UPDATE account SET balance = 200 WHERE id = 1;
COMMIT;

Session A:

SELECT balance FROM account WHERE id = 1;
-- under READ COMMITTED, this can see 200
COMMIT;

Dalam satu transaksi yang sama, dua SELECT bisa melihat hasil berbeda karena snapshot dibuat per statement.

Kapan cocok:

• operasi CRUD biasa;
• query yang tidak membutuhkan stable multi-statement view;
• command yang menggunakan single atomic SQL statement;
• transaksi pendek dengan constraint kuat;
• high-throughput OLTP dengan invariant yang dijaga secara lokal.

Risiko:

• read-modify-write race jika logic dilakukan di aplikasi;
• keputusan berbasis beberapa query bisa berubah di tengah transaksi;
• aggregate check bisa invalid saat concurrent insert/update terjadi;
• developer mengira @Transactional otomatis membuat semua read stabil.

5. Read Committed dan Read-Modify-Write Race

Buruk:

@Transactional
public void debit(long accountId, BigDecimal amount) {
    Account account = repo.findById(accountId);
    if (account.balance().compareTo(amount) < 0) {
        throw new InsufficientBalanceException();
    }
    account.debit(amount);
    repo.save(account);
}

Dua transaksi bisa membaca saldo sama dan keduanya menganggap saldo cukup.

Lebih baik dengan conditional update:

UPDATE account
SET balance = balance - :amount
WHERE id = :accountId
  AND balance >= :amount;

Java:

int updated = jdbcTemplate.update("""
    UPDATE account
    SET balance = balance - ?
    WHERE id = ?
      AND balance >= ?
    """, amount, accountId, amount);

if (updated == 0) {
    throw new InsufficientBalanceException();
}

Ini tetap READ COMMITTED, tetapi correctness berasal dari atomic predicate update.

Takeaway:

Naik isolation level bukan satu-satunya solusi. Bentuk query sering lebih penting.

6. Read Committed dan Update Recheck

Dalam READ COMMITTED, UPDATE yang menemukan row yang sedang diupdate transaksi lain akan menunggu. Setelah transaksi lain commit, PostgreSQL mengevaluasi ulang kondisi WHERE terhadap versi row terbaru.

Contoh:

UPDATE task
SET status = 'RUNNING'
WHERE id = 42
  AND status = 'READY';

Jika transaksi lain sudah mengubah status menjadi RUNNING, statement ini tidak akan update row setelah recheck.

Ini bagus untuk claim pattern sederhana:

UPDATE task
SET status = 'RUNNING', locked_by = :worker
WHERE id = :taskId
  AND status = 'READY'
RETURNING *;

Jika tidak ada row returned, task sudah diklaim/diproses pihak lain.

7. Repeatable Read: Transaction-Level Snapshot

REPEATABLE READ memberi snapshot stabil selama transaksi.

Mental model:

Transaksi melihat dunia seperti saat snapshot transaksi dibuat.
Perubahan committed oleh transaksi lain setelah itu tidak terlihat.

Contoh:

Session A:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE id = 1;
-- sees 100

Session B:

UPDATE account SET balance = 200 WHERE id = 1;
COMMIT;

Session A:

SELECT balance FROM account WHERE id = 1;
-- still sees 100
COMMIT;

Kapan berguna:

• report kecil yang perlu konsisten selama beberapa query;
• export data yang membutuhkan snapshot stabil;
• reconciliation batch yang tidak ingin melihat perubahan midway;
• multi-query validation yang harus melihat view konsisten.

Risiko:

• snapshot lama dapat menahan vacuum cleanup;
• transaksi panjang memperbesar bloat pressure;
• write conflict dapat menghasilkan serialization-style failure;
• write skew masih bisa terjadi untuk invariant cross-row tertentu.

8. Repeatable Read Tidak Sama dengan Semua Invariant Aman

Contoh write skew klasik: doctor on-call.

Invariant:

At least one doctor must remain on call.

Table:

CREATE TABLE doctor_shift (
    doctor_id bigint PRIMARY KEY,
    on_call boolean NOT NULL
);

INSERT INTO doctor_shift VALUES (1, true), (2, true);

Session A:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor_shift WHERE on_call;
-- sees 2
UPDATE doctor_shift SET on_call = false WHERE doctor_id = 1;
COMMIT;

Session B concurrent:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor_shift WHERE on_call;
-- also sees 2
UPDATE doctor_shift SET on_call = false WHERE doctor_id = 2;
COMMIT;

Akhirnya:

SELECT count(*) FROM doctor_shift WHERE on_call;
-- 0

Setiap transaksi lokal terlihat benar: masing-masing melihat masih ada dua doctor on call sebelum mematikan satu. Tetapi kombinasi concurrent melanggar invariant.

Solusi:

1. lock aggregate root row, misalnya department_schedule;
2. gunakan serializable isolation dengan retry;
3. ubah model menjadi slot/reservation yang bisa diberi constraint;
4. gunakan exclusion/unique/partial constraint jika invariant bisa diekspresikan declaratively.

9. Serializable: Correctness via Abort and Retry

SERIALIZABLE memberi ilusi hasil seperti transaksi berjalan satu per satu dalam suatu urutan serial. PostgreSQL mencapainya bukan dengan membuat semua transaksi blocking total, tetapi dengan mendeteksi dependency berbahaya dan membatalkan salah satu transaksi bila perlu.

Mental model:

Serializable does not mean no conflicts.
Serializable means conflicts that could break serial order become transaction aborts.

Contoh:

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT count(*)
FROM doctor_shift
WHERE on_call;

UPDATE doctor_shift
SET on_call = false
WHERE doctor_id = :doctorId;

COMMIT;

Dalam pola write skew, salah satu transaksi bisa gagal dengan:

SQLSTATE 40001 serialization_failure

Aplikasi harus retry seluruh transaksi dari awal.

10. Predicate/SIREAD Locks di Serializable

Di serializable isolation, PostgreSQL perlu melacak dependency dari read terhadap write concurrent. Ini dapat muncul sebagai predicate/SIREAD locks.

Poin penting:

• SIREAD lock tidak seperti lock biasa yang memblok writer secara langsung;
• ia dipakai untuk mendeteksi dependency dangerous structure;
• error bisa muncul saat statement atau commit;
• retry harus dari awal transaction;
• query yang tidak terindeks dengan baik dapat memperbesar area predicate tracking;
• long serializable transaction lebih rentan conflict.

Implikasi:

Serializable membutuhkan query shape dan index yang baik.
Serializable bukan excuse untuk query sembarangan.

11. Isolation vs Constraint

Constraint sering lebih baik daripada isolation tinggi.

Invariant:

Only one active subscription per customer.

Jangan hanya:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM subscription WHERE customer_id = :id AND status = 'ACTIVE';
INSERT INTO subscription(...);
COMMIT;

Lebih baik:

CREATE UNIQUE INDEX subscription_one_active
ON subscription(customer_id)
WHERE status = 'ACTIVE';

Lalu aplikasi menangani unique violation sebagai domain conflict.

Keuntungan constraint:

• selalu aktif untuk semua code path;
• tidak tergantung service tertentu;
• melindungi data dari manual SQL/migration bug;
• lebih mudah diuji;
• menjadi dokumentasi executable.

12. Isolation vs Explicit Lock

Invariant:

Total reserved seats for event must not exceed capacity.

Model:

CREATE TABLE event (
    id bigint PRIMARY KEY,
    capacity int NOT NULL,
    reserved_count int NOT NULL DEFAULT 0
);

Conditional update:

UPDATE event
SET reserved_count = reserved_count + :qty
WHERE id = :eventId
  AND reserved_count + :qty <= capacity;

Jika affected rows = 1, reservation boleh dibuat.

INSERT INTO reservation(event_id, qty, status)
VALUES (:eventId, :qty, 'CONFIRMED');

Ini menjaga invariant dengan satu hot aggregate row. Tidak perlu serializable untuk kasus ini.

Trade-off:

• simple dan kuat;
• event row menjadi hot spot;
• perlu mengukur contention;
• bisa di-scale dengan sharded counters/reservation buckets jika perlu.

13. Isolation vs Optimistic Version

Invariant:

User must not overwrite another user's edit silently.

Optimistic version cukup:

UPDATE document
SET content = :content,
    version = version + 1
WHERE id = :id
  AND version = :expectedVersion;

Jika affected rows = 0:

Return 409 Conflict / show reload-and-merge UI.

Tidak perlu serializable. Problem-nya bukan database anomaly kompleks, tetapi concurrent edit conflict.

14. Isolation vs Advisory Lock

Invariant:

Only one monthly billing close process may run per tenant and month.

Advisory lock cocok:

BEGIN;
SELECT pg_advisory_xact_lock(hashtextextended(:tenantId || ':' || :month, 0));
-- perform close operation
COMMIT;

Tetapi tetap simpan result secara durable:

CREATE TABLE billing_close (
    tenant_id bigint NOT NULL,
    close_month date NOT NULL,
    status text NOT NULL,
    PRIMARY KEY (tenant_id, close_month)
);

Advisory lock mengurangi concurrent execution. Primary key menjaga idempotency durable.

15. Anomaly Catalog untuk Engineer Aplikasi

15.1 Dirty Read

Membaca data belum commit dari transaksi lain.

PostgreSQL tidak mengizinkan dirty read bahkan jika nama isolation READ UNCOMMITTED dipakai.

15.2 Non-Repeatable Read

Transaksi membaca row yang sama dua kali dan mendapat nilai berbeda karena transaksi lain commit di antaranya.

Mungkin di READ COMMITTED.

15.3 Phantom Read

Transaksi mengulang query predicate dan mendapat set row berbeda karena transaksi lain insert/update row yang memenuhi predicate.

Di PostgreSQL READ COMMITTED, ini bisa terjadi. Di PostgreSQL REPEATABLE READ, snapshot stabil mencegah phantom dalam snapshot transaksi.

15.4 Lost Update

Dua transaksi membaca nilai sama, menghitung nilai baru di aplikasi, lalu satu update menimpa update lain.

Solusi:

• atomic update;
• optimistic version;
• SELECT ... FOR UPDATE;
• serializable dengan retry.

15.5 Write Skew

Dua transaksi membaca set data yang sama, lalu mengupdate row berbeda, dan kombinasi commit melanggar invariant cross-row.

Solusi:

• serializable;
• lock aggregate root;
• materialized counter row;
• constraint jika bisa diekspresikan;
• redesign model.

15.6 Read Skew

Transaksi membaca subset data pada waktu berbeda dan melihat kombinasi yang tidak pernah valid secara bersamaan.

Solusi:

• repeatable read for stable snapshot;
• single statement query;
• materialized snapshot/reporting model.

16. Decision Matrix

17. Java Isolation Configuration

Spring example:

@Transactional(isolation = Isolation.READ_COMMITTED, timeout = 10)
public void approveCase(long caseId) {
    // command logic
}

Serializable example:

@Transactional(isolation = Isolation.SERIALIZABLE, timeout = 10)
public void changeDoctorOnCall(long doctorId) {
    int active = jdbcTemplate.queryForObject("""
        SELECT count(*)
        FROM doctor_shift
        WHERE on_call
        """, Integer.class);

    if (active <= 1) {
        throw new DomainException("At least one doctor must remain on call");
    }

    jdbcTemplate.update("""
        UPDATE doctor_shift
        SET on_call = false
        WHERE doctor_id = ?
        """, doctorId);
}

Namun method ini harus dipanggil melalui retry wrapper di luar transaksi:

public void changeDoctorOnCallWithRetry(long doctorId) {
    retryOnSerializationFailure(() -> {
        doctorService.changeDoctorOnCall(doctorId);
        return null;
    });
}

Kenapa retry wrapper harus di luar?

Karena setelah serialization failure, transaksi saat itu sudah gagal. Kita perlu membuka transaksi baru.

18. Retry Template untuk PostgreSQL

Pseudo-code:

public final class PostgresTransactionRetrier {

    public <T> T run(Supplier<T> operation) {
        int maxAttempts = 4;
        RuntimeException last = null;

        for (int attempt = 1; attempt <= maxAttempts; attempt++) {
            try {
                return operation.get();
            } catch (RuntimeException ex) {
                if (!isRetryable(ex) || attempt == maxAttempts) {
                    throw ex;
                }
                last = ex;
                sleepWithJitter(attempt);
            }
        }

        throw last;
    }

    private boolean isRetryable(Throwable ex) {
        String sqlState = findSqlState(ex);
        return "40001".equals(sqlState) || "40P01".equals(sqlState);
    }
}

Jangan retry:

• validation error;
• not-null violation;
• check constraint violation;
• FK violation yang berasal dari input invalid;
• unique violation kecuali memang bagian dari idempotency flow.

Retry harus:

• terbatas;
• memakai exponential backoff/jitter;
• metrics-aware;
• log attempt count;
• tidak menggandakan side effect external;
• berada di boundary use-case, bukan di DAO statement kecil.

19. Idempotency dan Retry

Retry isolation failure aman hanya jika command idempotent atau side effect belum terjadi.

Buruk:

@Transactional(isolation = Isolation.SERIALIZABLE)
public void payInvoice(Command command) {
    paymentGateway.charge(command.card(), command.amount());
    invoiceRepository.markPaid(command.invoiceId());
}

Jika transaksi gagal setelah charge, retry bisa double charge.

Lebih baik:

1. Receive command with idempotency key.
2. Insert command record with unique idempotency key.
3. Transition invoice/payment intent in database transaction.
4. Write outbox event.
5. External worker charges using provider idempotency key.
6. Store provider result.

Schema:

CREATE TABLE payment_command (
    id uuid PRIMARY KEY,
    idempotency_key text NOT NULL UNIQUE,
    invoice_id bigint NOT NULL,
    amount numeric(19, 4) NOT NULL,
    status text NOT NULL
);

Jika retry HTTP mengirim command yang sama, unique key mengarahkan aplikasi ke result yang sama, bukan membuat charge baru.

20. Read-Only Serializable Transactions

PostgreSQL mendukung mode transaksi read-only dan deferrable untuk beberapa use case serializable read-only.

Contoh:

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

SELECT ... complex consistent report ...;

COMMIT;

Mental model:

• cocok untuk report yang butuh snapshot serializable;
• database bisa menunda awal transaksi sampai mendapat snapshot yang aman;
• setelah snapshot aman, risiko serialization failure lebih rendah untuk read-only transaction;
• tidak cocok untuk latency-sensitive request interaktif jika penundaan awal tidak bisa diterima.

21. Hibernate, Flush, dan Isolation

Dalam Hibernate, isolation level database bukan satu-satunya sumber perilaku concurrency. Flush timing juga penting.

Contoh:

@Transactional
public void updateThenQuery(long id) {
    Entity e = repository.findById(id).orElseThrow();
    e.setStatus("APPROVED");

    // Hibernate may flush before this query depending on flush mode
    List<Entity> rows = repository.findPendingRows();
}

Masalah:

• SQL dikirim lebih awal dari yang engineer kira;
• lock bisa diambil sebelum query berikutnya;
• query tambahan bisa memperpanjang transaksi;
• batch ordering bisa mempengaruhi deadlock;
• isolation tidak menyelamatkan desain flush yang tidak dipahami.

Praktik:

• nyalakan SQL logging di test concurrency;
• gunakan repository method eksplisit untuk lock;
• hindari transaksi besar yang memuat banyak aggregate;
• gunakan @Version untuk optimistic flows;
• gunakan native SQL/JdbcTemplate untuk critical command yang butuh precise SQL shape.

22. Common Domain Patterns

22.1 State Machine Transition

Schema:

CREATE TABLE case_file (
    id bigint PRIMARY KEY,
    state text NOT NULL,
    version bigint NOT NULL DEFAULT 0
);

Command:

UPDATE case_file
SET state = 'APPROVED',
    version = version + 1
WHERE id = :id
  AND state = 'UNDER_REVIEW';

If affected rows = 0:

Invalid transition, already changed, or not found.

Read committed cukup karena transition guard ada di WHERE.

22.2 Cross-Entity Invariant

Invariant:

A case cannot be closed if unresolved enforcement action exists.

Option A: lock parent case row and check children:

BEGIN;

SELECT *
FROM case_file
WHERE id = :caseId
FOR UPDATE;

SELECT 1
FROM enforcement_action
WHERE case_id = :caseId
  AND status IN ('OPEN', 'PENDING')
LIMIT 1;

-- if none:
UPDATE case_file
SET state = 'CLOSED'
WHERE id = :caseId
  AND state <> 'CLOSED';

COMMIT;

Tapi apakah ini cukup? Tergantung apakah transaksi lain yang membuat enforcement action juga mengunci parent case row dengan urutan yang sama.

Rule:

Jika invariant parent-child dilindungi dengan parent lock, semua writer child yang relevan juga harus mengambil parent lock.

Kalau tidak, child baru bisa muncul setelah check.

22.3 Unique Active Assignment

CREATE UNIQUE INDEX assignment_one_active_per_case
ON assignment(case_id)
WHERE status = 'ACTIVE';

Aplikasi boleh berjalan di read committed. Database menjaga invariant.

22.4 Schedule Overlap

Range type + exclusion constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_booking (
    id bigserial PRIMARY KEY,
    room_id bigint NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room_id WITH =,
        period WITH &&
    )
);

Invariant overlap dijaga declaratively.

23. Isolation Testing

Concurrency bug jarang ditemukan oleh unit test single-thread.

Gunakan test seperti ini:

@Test
void concurrentApprovalShouldNotBreakInvariant() throws Exception {
    ExecutorService pool = Executors.newFixedThreadPool(2);
    CyclicBarrier barrier = new CyclicBarrier(2);

    Callable<Result> c1 = () -> {
        barrier.await();
        return service.approve(command1);
    };

    Callable<Result> c2 = () -> {
        barrier.await();
        return service.approve(command2);
    };

    Future<Result> f1 = pool.submit(c1);
    Future<Result> f2 = pool.submit(c2);

    // assert one success, one domain conflict/retry success depending invariant
}

Untuk database integration test:

• gunakan Testcontainers PostgreSQL;
• matikan mock repository;
• jalankan real SQL;
• assert final database invariant;
• test dengan READ COMMITTED, REPEATABLE READ, dan SERIALIZABLE bila relevan;
• log SQL dan SQLSTATE.

24. Observability untuk Isolation Failures

Metrics yang perlu dipantau:

postgres.sqlstate.40001.count
postgres.sqlstate.40P01.count
postgres.sqlstate.55P03.count
transaction.retry.attempts
transaction.retry.exhausted
transaction.duration
lock.wait.duration
command.conflict.count
optimistic.lock.failure.count

Log harus memuat:

• command type;
• aggregate id;
• tenant id;
• transaction attempt;
• SQLSTATE;
• isolation level;
• lock timeout;
• statement timeout;
• application name;
• correlation id.

Tanpa ini, serialization failure terlihat seperti random 500.

25. Performance Trade-Offs

Tidak ada pilihan yang selalu terbaik. Gunakan mekanisme paling kecil yang menjaga invariant secara jelas.

26. Hands-On Lab: Non-Repeatable Read

Setup:

CREATE TABLE isolation_account (
    id bigint PRIMARY KEY,
    balance int NOT NULL
);

INSERT INTO isolation_account VALUES (1, 100)
ON CONFLICT (id) DO UPDATE SET balance = 100;

Session A:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM isolation_account WHERE id = 1;
-- 100

Session B:

UPDATE isolation_account SET balance = 200 WHERE id = 1;
COMMIT;

Session A:

SELECT balance FROM isolation_account WHERE id = 1;
-- 200 under READ COMMITTED
COMMIT;

Repeat with:

BEGIN ISOLATION LEVEL REPEATABLE READ;

Session A should keep seeing the original snapshot.

27. Hands-On Lab: Write Skew

Setup:

DROP TABLE IF EXISTS doctor_shift;
CREATE TABLE doctor_shift (
    doctor_id bigint PRIMARY KEY,
    on_call boolean NOT NULL
);

INSERT INTO doctor_shift VALUES (1, true), (2, true);

Session A:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor_shift WHERE on_call;
UPDATE doctor_shift SET on_call = false WHERE doctor_id = 1;
-- do not commit yet

Session B:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor_shift WHERE on_call;
UPDATE doctor_shift SET on_call = false WHERE doctor_id = 2;
COMMIT;

Session A:

COMMIT;

Check:

SELECT count(*) FROM doctor_shift WHERE on_call;

Now repeat with:

BEGIN ISOLATION LEVEL SERIALIZABLE;

Expected: one transaction should fail with serialization failure in the dangerous concurrent pattern.

28. Hands-On Lab: Aggregate Root Lock Fix

Add parent table:

CREATE TABLE shift_group (
    id bigint PRIMARY KEY,
    name text NOT NULL
);

INSERT INTO shift_group VALUES (1, 'ER')
ON CONFLICT (id) DO NOTHING;

ALTER TABLE doctor_shift ADD COLUMN group_id bigint NOT NULL DEFAULT 1;

Pattern:

BEGIN;

SELECT *
FROM shift_group
WHERE id = 1
FOR UPDATE;

SELECT count(*)
FROM doctor_shift
WHERE group_id = 1
  AND on_call;

UPDATE doctor_shift
SET on_call = false
WHERE doctor_id = :doctorId;

COMMIT;

Now all transactions touching the invariant must lock shift_group(1) first.

29. Hands-On Lab: Partial Unique Constraint

Setup:

CREATE TABLE customer_policy (
    id bigserial PRIMARY KEY,
    customer_id bigint NOT NULL,
    status text NOT NULL
);

CREATE UNIQUE INDEX customer_policy_one_active
ON customer_policy(customer_id)
WHERE status = 'ACTIVE';

Concurrent inserts:

INSERT INTO customer_policy(customer_id, status)
VALUES (42, 'ACTIVE');

Only one active policy can exist. Let the database enforce it.

Application behavior:

23505 unique_violation -> domain conflict: customer already has active policy.

30. Checklist: Choosing Isolation and Protection

[ ] What invariant can be violated under concurrency?
[ ] Is the invariant single-row, multi-row, aggregate, temporal, or external?
[ ] Can a database constraint express it?
[ ] Can a single conditional update express it?
[ ] Is conflict rare enough for optimistic locking?
[ ] Is conflict common enough for pessimistic locking?
[ ] Does every writer follow the same lock discipline?
[ ] Would serializable be simpler and acceptable with retry?
[ ] Is retry safe with external side effects?
[ ] Are idempotency keys stored durably?
[ ] Are SQLSTATE 40001 and 40P01 handled separately from validation errors?
[ ] Are tests concurrent and integration-level?
[ ] Are metrics/logs able to show retries, conflicts, and lock waits?

31. Practical Rules

1. Use READ COMMITTED by default, but not blindly.
2. Push correctness into constraints and conditional statements where possible.
3. Use optimistic locking for user edits and low-contention aggregate changes.
4. Use pessimistic locking for hot state transitions where retry cost is high.
5. Use serializable for complex predicate invariants when alternative design is worse.
6. Never use serializable without retry policy.
7. Never retry a transaction that already performed non-idempotent external side effects.
8. Make retry boundaries match business command boundaries.
9. Use short transactions; long snapshots harm vacuum and increase conflict windows.
10. Test concurrency with real PostgreSQL, not only mocks.

32. Common Misconceptions

Misconception 1: @Transactional means serial execution

False. It only creates a transaction boundary. Isolation level still matters. Query shape still matters. Locks still matter.

Misconception 2: Repeatable Read prevents all concurrency bugs

False. It gives stable snapshot, but cross-row write skew can still violate invariant.

Misconception 3: Serializable means no retry needed

False. Serializable often protects correctness by aborting one transaction.

Misconception 4: Locking is always bad

False. Locking is often the simplest correct solution for aggregate invariants.

Misconception 5: Constraint errors are exceptional bugs

Not always. Unique violation can be a normal conflict signal in concurrent systems.

33. Capstone Example: Case Closure Correctness

Invariant:

A case can be CLOSED only if:
- state is currently READY_TO_CLOSE;
- no unresolved enforcement action exists;
- no active appeal exists;
- exactly one closure record is created;
- audit event and outbox event are durable.

Recommended design:

CREATE UNIQUE INDEX case_closure_once
ON case_closure(case_id);

Transaction:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '10s';

SELECT *
FROM case_file
WHERE id = :caseId
FOR UPDATE;

SELECT 1
FROM enforcement_action
WHERE case_id = :caseId
  AND status IN ('OPEN', 'PENDING')
LIMIT 1;

SELECT 1
FROM appeal
WHERE case_id = :caseId
  AND status IN ('ACTIVE', 'UNDER_REVIEW')
LIMIT 1;

UPDATE case_file
SET state = 'CLOSED',
    version = version + 1,
    updated_at = now()
WHERE id = :caseId
  AND state = 'READY_TO_CLOSE';

INSERT INTO case_closure(case_id, closed_at, closed_by)
VALUES (:caseId, now(), :userId);

INSERT INTO audit_log(...)
VALUES (...);

INSERT INTO outbox_event(...)
VALUES (...);

COMMIT;

Critical discipline:

Any transaction that creates unresolved action or active appeal for the case must also lock case_file first.

Jika writer lain tidak mengikuti discipline itu, check children tidak cukup. Correctness bukan hanya satu transaksi; correctness adalah kontrak semua writer terhadap invariant.

34. Takeaways

1. Mulai dari invariant, bukan isolation level.
2. PostgreSQL READ COMMITTED memakai snapshot per statement dan cukup untuk banyak command jika query/constraint benar.
3. REPEATABLE READ memberi snapshot stabil, tetapi bukan jaminan semua invariant cross-row aman.
4. SERIALIZABLE kuat, tetapi menuntut retry penuh dan idempotency.
5. Constraint adalah mekanisme correctness terbaik jika invariant bisa diekspresikan declaratively.
6. Atomic conditional update sering lebih baik daripada read-then-write.
7. Explicit lock efektif jika semua writer mengikuti lock discipline yang sama.
8. Java retry harus berada di luar transaction yang gagal.
9. External side effect harus dipisah dari transaksi database melalui outbox/idempotency.
10. Concurrency correctness harus diuji dengan real PostgreSQL dan scenario concurrent.

35. Referensi Resmi

• PostgreSQL Documentation — Transaction Isolation: https://www.postgresql.org/docs/current/transaction-iso.html
• PostgreSQL Documentation — Concurrency Control: https://www.postgresql.org/docs/current/mvcc.html
• PostgreSQL Documentation — Explicit Locking: https://www.postgresql.org/docs/current/explicit-locking.html
• PostgreSQL Documentation — Serialization Failure Handling: https://www.postgresql.org/docs/current/mvcc-serialization-failure-handling.html
• PostgreSQL Documentation — SET TRANSACTION: https://www.postgresql.org/docs/current/sql-set-transaction.html