Write Skew and Serializable Isolation

Trigger a silent bank corruption bug, then watch PostgreSQL catch it.

Introduction

30 Second Summary

Imagine two bank tellers each checking the same combined account balance at the same instant, both approving a withdrawal that looks safe on its own. Together, the two withdrawals silently break a business rule, and your database says nothing.

In this project, you will execute a "simultaneous withdrawal attack" against your PostgreSQL database using two side-by-side terminal sessions. You will watch the attack silently corrupt data under one isolation level, then see PostgreSQL detect and reject it under another.

What You'll Build

You will run two psql sessions side by side, execute the same dangerous withdrawal under two different isolation levels, and see the exact moment PostgreSQL's SSI engine catches the pattern and stops it cold.

By the end of this project, you'll have:

  • A corrupted database that proves REPEATABLE READ silently allows write skew. Two concurrent withdrawals both committed successfully while violating the $1000 combined-balance rule.
  • A rejected transaction proving SERIALIZABLE detects the same dangerous pattern. PostgreSQL's Serializable Snapshot Isolation engine catches the read/write dependency and rejects the second committer with SQLSTATE 40001.
  • A working retry pattern that handles serialization failures correctly. The retried transaction gets a fresh snapshot, sees the first withdrawal, and your business logic makes the right call.
  • Secret Mission: Trigger a 3-transaction cycle anomaly that produces an impossible result under REPEATABLE READ and gets caught by SERIALIZABLE.

Are there any prerequisites?

This project assumes you have a working PostgreSQL installation with psql available, and an existing accounts table containing Alice (900), Bob (1400), and Charlie (700). You should be comfortable with SQL transactions (BEGIN/COMMIT/ROLLBACK) and understand the READ COMMITTED isolation level.

Before We Start

Before we dive in, let's lock in what you're about to prove and why it matters that you prove it hands-on.

Reading about isolation levels is one thing. Watching your own database silently corrupt data is another.

Set Up Your Sessions and Reset Function

To prove that write skew happens, you need to run two transactions at the exact same time. Each transaction checks the same data and makes a decision based on what it sees.

That means you need two independent psql sessions connected to the same database. You also need a quick way to reset your accounts table between experiments, since the withdrawal attacks will corrupt your data.

In this step, get ready to:
  • Open two terminal windows, each running a psql session against your database.
  • Create a reset function that restores your accounts to their starting balances.
  • Verify both sessions can see your accounts data.
Open two psql sessions

Write skew requires two concurrent transactions running simultaneously. Each psql session holds its own transaction, so you need two separate terminal windows.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal and press Enter to open it.
  • Connect to the database where your accounts table lives by running:
psql

Connecting to a specific database

If your accounts table is in a named database (not your default), add the -d flag followed by the database name when connecting.

You should see the psql prompt appear. This is your Session 1.

  • Open a second terminal window by pressing Cmd+N (macOS) or by searching for Terminal again (Windows).
  • Connect to the same database with psql in this second window. This is your Session 2.

You now have two independent psql prompts side by side. Keep both windows visible so you can switch between them quickly.

Create the reset function

Each time you run the withdrawal attack, it changes your account balances. A PL/pgSQL helper function lets you restore your accounts to their starting state with a single command.

  • In Session 1, create the reset function by running this SQL:
-- Create the reset helper function
CREATE OR REPLACE FUNCTION reset_accounts() RETURNS void AS $$
BEGIN
    UPDATE accounts SET balance = 900 WHERE owner = 'Alice';
    UPDATE accounts SET balance = 1400 WHERE owner = 'Bob';
    UPDATE accounts SET balance = 700 WHERE owner = 'Charlie';
END;
$$ LANGUAGE plpgsql;

What does this function do?

  • CREATE OR REPLACE FUNCTION defines a reusable database function. If it already exists, it overwrites the previous version.
  • The three UPDATE statements force Alice back to 900, Bob back to 1400, and Charlie back to 700.
  • RETURNS void means it does its work without returning a value. You call it with SELECT reset_accounts(); whenever you need a clean slate.

You should see CREATE FUNCTION in the output, confirming PostgreSQL registered your function.

Seeing an error instead of CREATE FUNCTION?

  • Check that you pasted the entire block including the closing $$ LANGUAGE plpgsql; line. Missing the final semicolon or dollar-quote leaves psql waiting for more input.
  • Make sure the accounts table exists in this database. Run \dt accounts to confirm.

Still stuck? Ask for help.

✔️ Awesome, I've got everything!

Great. Make sure you saw CREATE FUNCTION in the output before moving on.

ⓧ I'd like to double check the full code

Here is the complete reset function. Compare it character-for-character with what you pasted.

-- Create the reset helper function
CREATE OR REPLACE FUNCTION reset_accounts() RETURNS void AS $$
BEGIN
    UPDATE accounts SET balance = 900 WHERE owner = 'Alice';
    UPDATE accounts SET balance = 1400 WHERE owner = 'Bob';
    UPDATE accounts SET balance = 700 WHERE owner = 'Charlie';
END;
$$ LANGUAGE plpgsql;
Test the reset and verify both sessions

The function exists, but you need to confirm it actually works and that both sessions see the same data.

  • In Session 1, call the reset function and check the results by running:
SELECT reset_accounts();
SELECT * FROM accounts;

You should see Alice at 900, Bob at 1400, and Charlie at 700.

Now for the real test. Before you run anything in Session 2, what do you expect to see when you query the accounts table from a completely separate connection?

  • Switch to Session 2 and verify it sees the same data by running:
SELECT * FROM accounts;

You see the same three rows: Alice at 900, Bob at 1400, Charlie at 700. Both sessions are reading from the same database and seeing the same committed data.

Why do both sessions see the same thing?

PostgreSQL's default isolation level is READ COMMITTED. Outside of an explicit transaction, every query sees the latest committed state. Both sessions agree on reality right now. The interesting part begins when they start transactions and their snapshots diverge.

Session 2 shows different data?

  • Confirm both sessions are connected to the same database. Run SELECT current_database(); in each. The names must match.
  • If you have an open transaction in Session 2, end it with COMMIT; or ROLLBACK; first, then re-run the SELECT.

Need help figuring out what's different?

Your two sessions are ready and your reset function is standing by. Next up, you will use these sessions to execute a simultaneous withdrawal attack that silently breaks your data under REPEATABLE READ.

Trigger Write Skew Under REPEATABLE READ

Your two psql sessions are connected and your reset function works. Now it's time to break things.

The business rule is straightforward: Alice and Bob's combined balance must stay at or above $1000. Right now that total is $2300, so a single $800 withdrawal seems perfectly safe.

But what happens when two $800 withdrawals arrive at the same time, and both check the combined balance before either one commits?

In this step, get ready to:
  • Begin a REPEATABLE READ transaction in Session 1 and check the combined balance.
  • Execute a concurrent withdrawal from Bob in Session 2 while Session 1 is still open.
  • Complete Session 1's withdrawal from Alice and observe the corrupted state.
Begin the attack in Session 1

Under REPEATABLE READ, each transaction gets a frozen snapshot of the database at the moment it starts. Both sessions will read the same combined balance, even if one of them changes data before the other commits.

  • In Session 1, start a REPEATABLE READ transaction and check the combined balance by running:
-- Session 1: REPEATABLE READ withdrawal attack
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');

What does this do?

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ starts a transaction that locks in a snapshot right now. Every SELECT inside this transaction will see the database exactly as it was at this moment, regardless of what other sessions commit in the meantime.

You should see 2300 returned. That means Alice (900) + Bob (1400) = 2300, which is well above the $1000 minimum.

Not seeing 2300?

Run SELECT reset_accounts(); first to make sure Alice, Bob, and Charlie are at their starting balances. Then re-run the BEGIN and SELECT above.

Help me debug my REPEATABLE READ transaction

Do NOT commit or close Session 1. Leave it sitting with the transaction open. You are about to switch to Session 2.

Execute the concurrent withdrawal in Session 2

Session 1 is paused mid-transaction. It checked the balance and everything looked fine. Now a second person (Session 2) arrives and does the same thing: checks the balance, decides it's safe, and withdraws $800 from Bob.

  • Switch to your Session 2 terminal window.
  • Run the full check-and-withdraw sequence by entering:
-- Session 2: REPEATABLE READ withdrawal attack
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Bob';
COMMIT;

Why does Session 2 also see 2300?

Session 1 has not committed yet. Under snapshot isolation, Session 2's snapshot only includes committed data. So it sees the same $2300 total and concludes that withdrawing $800 from Bob is safe.

Session 2 writes to Bob's row. Session 1 will write to Alice's row. Because they touch different rows, REPEATABLE READ sees no conflict at all.

Session 2 commits with no error. The SELECT shows 2300, the UPDATE runs, and COMMIT succeeds silently. Bob's balance is now 600.

Seeing a could not serialize access error?

Make sure you used REPEATABLE READ (not SERIALIZABLE) in the BEGIN TRANSACTION statement. You want both sessions at the REPEATABLE READ level for this experiment.

Help me fix my Session 2 transaction

Complete the attack in Session 1 and reveal the damage

Session 1 is still sitting in its open transaction. Its snapshot is frozen at the moment it started. It has no idea that Bob's balance just dropped by $800.

  • Switch back to your Session 1 terminal window.
  • Execute the withdrawal from Alice and commit by running:
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Alice';
COMMIT;

Why doesn't PostgreSQL stop this?

Session 1 writes to Alice's row. Session 2 wrote to Bob's row. Under REPEATABLE READ, PostgreSQL only blocks concurrent writes to the same row. Since Alice and Bob are different rows, no conflict is detected.

This is write skew: two transactions each read overlapping data and write based on stale reads, producing a state that violates a business rule neither could detect individually.

The COMMIT succeeds with no error. Both transactions committed cleanly.

Before you check the final balances, predict: what do you think Alice and Bob's combined balance is now? Is it still above $1000?

  • Check the damage by running:
-- Check the damage
SELECT owner, balance FROM accounts WHERE owner IN ('Alice', 'Bob');

Alice shows 100. Bob shows 600. Combined = $700. The $1000 minimum business rule is violated, and PostgreSQL said nothing.

What just happened?

Each transaction individually looked correct. Each saw $2300 and withdrew $800, leaving $1500. But together, they withdrew $1600 total, dropping the combined balance to $700.

This is the exact boundary of REPEATABLE READ. It prevents dirty reads and phantom reads in PostgreSQL. But it cannot detect serialization anomalies where two transactions make decisions based on overlapping, stale snapshots.

Seeing different numbers?

If Alice is not 100 or Bob is not 600, one of the sessions may not have committed properly. Run SELECT reset_accounts(); and replay the entire attack from the beginning. Make sure you complete Session 2's full sequence (through COMMIT) before running the UPDATE and COMMIT in Session 1.

Help me replay the write skew attack

✔️ Awesome, I've got everything!

Great. You have successfully triggered write skew. Both transactions committed, the business rule is violated, and PostgreSQL raised no alarm.

ⓧ I'd like to double check the full sequence

Here is the complete SQL for Session 1:

-- Session 1: REPEATABLE READ withdrawal attack
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
-- Wait for Session 2 to also BEGIN and SELECT
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Alice';
COMMIT;
-- Check the damage
SELECT owner, balance FROM accounts WHERE owner IN ('Alice', 'Bob');

Here is the complete SQL for Session 2:

-- Session 2: REPEATABLE READ withdrawal attack
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Bob';
COMMIT;

The critical ordering: Session 2 must COMMIT before you run the UPDATE and COMMIT in Session 1. Both sessions must have their BEGIN and SELECT done before either commits.

You just proved that REPEATABLE READ silently allows write skew. Next up, you'll replay the exact same attack under SERIALIZABLE and watch PostgreSQL catch the fraud before any data is corrupted.

Watch SERIALIZABLE Catch the Attack

Your accounts table tells the story. Alice has $100, Bob has $600, and both transactions committed without a peep. REPEATABLE READ let two withdrawals through based on stale reads, silently violating the $1000 minimum.

Now you'll replay the exact same attack under SERIALIZABLE isolation. PostgreSQL's Serializable Snapshot Isolation (SSI) engine monitors read/write dependencies between concurrent transactions. When it detects a dangerous cycle, it rejects one transaction rather than letting corrupted data through.

In this step, get ready to:
  • Reset accounts to their starting balances.
  • Replay the dual-withdrawal attack under SERIALIZABLE.
  • See PostgreSQL reject the second committer with SQLSTATE 40001.
Reset and begin the attack in Session 1

Your accounts are still in their corrupted state from the previous step. You need a clean slate before running the experiment again.

  • In Session 1, reset the accounts to their starting balances by running:
SELECT reset_accounts();
  • Confirm the reset worked by running:
SELECT * FROM accounts;

You should see Alice at 900, Bob at 1400, and Charlie at 700.

Still seeing the old balances?

Make sure you ran SELECT reset_accounts(); with the semicolon at the end. Without it, psql waits for more input instead of executing.

Help me debug this

Now start the SERIALIZABLE attack. This is the same withdrawal pattern you ran before, but with a stronger isolation level.

  • In Session 1, begin a SERIALIZABLE transaction, check the combined balance, and withdraw 800 from Alice by running:
-- Begin a SERIALIZABLE transaction and withdraw from Alice
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Alice';

What does this code do?

  • BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE starts a transaction at the highest isolation level. PostgreSQL will now monitor this transaction's reads and writes for dangerous dependencies.
  • The SELECT SUM checks the combined balance. SSI records that this transaction read the Alice and Bob rows.
  • The UPDATE subtracts 800 from Alice. SSI now knows this transaction read from and wrote to overlapping data.

You see 2300 from the SELECT. Do NOT commit yet.

Run the same attack in Session 2

With Session 1 still holding an uncommitted write, you'll now run the same pattern in Session 2. Both transactions will be in flight simultaneously, just like in the REPEATABLE READ experiment.

  • Switch to Session 2 and run the matching withdrawal from Bob:
-- Begin a SERIALIZABLE transaction and withdraw from Bob
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Bob';

What's happening here?

Session 2 also sees 2300 from its SELECT. Both transactions now hold writes based on the same snapshot of the combined balance.

Under REPEATABLE READ, this exact setup committed without complaint. The SSI engine is now tracking that both transactions read overlapping rows (Alice and Bob) and each wrote to one of them. That creates a read/write dependency cycle.

No error yet. Both transactions are in flight with no conflict visible.

Commit both and watch the rejection

The moment of truth. Under REPEATABLE READ, both COMMITs succeeded and the data was silently corrupted. The first committer wins rule in SSI means the transaction that commits first survives, and the second gets rejected.

  • In Session 1, commit the transaction by running:
COMMIT;

Session 1 commits successfully. Alice's withdrawal is now permanent.

Before you commit Session 2, what do you think will happen? Will this COMMIT succeed like it did under REPEATABLE READ, or will PostgreSQL intervene?

  • In Session 2, attempt to commit by running:
COMMIT;

Session 2 is rejected. You see an error containing could not serialize access due to read/write dependencies among transactions.

What is SQLSTATE 40001?

This error carries SQLSTATE 40001 (serialization_failure). It means PostgreSQL detected that letting both transactions commit would produce a result impossible under any serial (one-at-a-time) execution order.

The SSI engine saw the cycle: Session 1 read Alice and Bob, then wrote Alice. Session 2 read Alice and Bob, then wrote Bob. Each transaction's write would have affected the other's read. PostgreSQL rejected the second committer to preserve data integrity.

  • In either session, verify the final state by running:
SELECT owner, balance FROM accounts WHERE owner IN ('Alice', 'Bob');

Alice shows 100. Bob shows 1400. The combined balance is $1500, safely above the $1000 threshold. Only Session 1's withdrawal went through. Data integrity preserved.

Compare this to REPEATABLE READ

Under REPEATABLE READ, the same attack left Alice at 100 and Bob at 600 (combined $700, below $1000). Both transactions committed silently.

Under SERIALIZABLE, the combined balance never dropped below $1000 because PostgreSQL caught the dangerous dependency. The trade-off is clear: SERIALIZABLE occasionally rejects a transaction, but it never lets impossible results slip through.

Both transactions committed without error?

The SSI engine only detects conflicts when both transactions are truly concurrent. Make sure you did NOT commit Session 1 before starting Session 2's transaction. Both transactions must be open (post-BEGIN) at the same time for the dependency cycle to form.

Reset with SELECT reset_accounts(); and try again. This time, run Session 2's BEGIN, SELECT, and UPDATE while Session 1 is still uncommitted.

Help me fix this

SERIALIZABLE stopped the fraud. But in production, the rejected transaction was not wrong. It was just unlucky enough to commit second. Next, you'll turn that rejection into the correct business decision by retrying with a fresh snapshot.

Retry the Rejected Transaction

SERIALIZABLE detected the dangerous withdrawal and rejected Session 2. But in production, a rejection without retry logic is useless. The rejected transaction was not wrong. It was just unlucky, committing second.

Retrying the entire transaction from scratch gives it a fresh snapshot that now includes Session 1's committed withdrawal. Your business logic can then make the correct decision based on real data, not stale data.

In this step, get ready to:
  • Roll back the failed transaction and start a fresh SERIALIZABLE snapshot.
  • Apply business logic against the fresh data to decide whether the withdrawal is safe.
  • Verify the accounts table preserved data integrity throughout the entire attack-and-retry cycle.
Roll back and start a fresh SERIALIZABLE transaction

Session 2 is stuck in an aborted state after the serialization failure. PostgreSQL ignores every command except ROLLBACK until you clear it. Once you roll back, you can begin a brand new transaction with a fresh snapshot.

Before you run the code below, think about this: what combined balance do you expect the fresh snapshot to show? Remember, Session 1's withdrawal from Alice already committed.

  • In Session 2, roll back the aborted transaction and start a fresh SERIALIZABLE transaction by running:
-- Retry with fresh snapshot:
ROLLBACK;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');

The result is 1500, not 2300. The fresh snapshot sees Alice's balance at 100 (after Session 1's committed withdrawal), plus Bob's 1400.

Why does the fresh snapshot see different data?

The original transaction started before Session 1 committed, so its snapshot froze the old balances. ROLLBACK discards that stale snapshot entirely.

The new BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE takes a fresh snapshot that includes everything committed up to this instant. That is why the PostgreSQL docs say retry must cover the entire transaction. A partial retry would still use stale data.

Apply business logic and deny the withdrawal

Your business rule says the combined balance of Alice and Bob must stay at or above $1000. The fresh snapshot shows 1500. Subtracting the $800 withdrawal would leave 700, which violates the rule.

In a production application, your code would check this condition and skip the UPDATE. Since the check fails, you deny the withdrawal by rolling back.

  • In Session 2, deny the withdrawal by rolling back the transaction:
-- Now sees 1500, not 2300. Business logic denies: 1500 - 800 = 700 < 1000.
ROLLBACK;

What would a production retry loop look like?

In production, your application wraps the entire BEGIN through COMMIT block in a loop. The loop catches SQLSTATE 40001 and reruns the full transaction from the top. The fresh snapshot provides correct data, and your business logic decides whether to proceed or deny.

PostgreSQL does not offer automatic retry because only your application knows which SQL to issue and which values to use. The retry must replay all logic, not just the final COMMIT.

Verify the final state

The retry cycle is complete. Before you check, predict what you expect to see: did Bob's balance change, or did your business logic protect it?

  • Confirm the accounts are intact by running this in either session:
SELECT owner, balance FROM accounts WHERE owner IN ('Alice', 'Bob');

Alice shows 100, Bob shows 1400. Combined is 1500, safely above the $1000 threshold. Only Session 1's withdrawal went through. Session 2 was rejected, retried, saw the truth, and correctly denied the second withdrawal.

The three-part guarantee

SERIALIZABLE forced a retry by detecting the dangerous read/write cycle. The retry gave Session 2 a fresh snapshot reflecting Session 1's commit. Your business logic then made the correct decision based on real numbers. No silent corruption, no stale reads, no violated invariants.

Bob's balance changed unexpectedly?

If Bob's balance is not 1400, you may have accidentally run the UPDATE statement before rolling back. Run SELECT reset_accounts(); and repeat Step 3 (the SERIALIZABLE attack) followed by this retry step.

help me figure out why my accounts table has unexpected balances after the SERIALIZABLE retry

✔️ Awesome, I've got everything!

The full retry pattern is complete. You proved that SERIALIZABLE plus retry equals correct data.

ⓧ I'd like to double check the full code

Here is everything Session 2 ran during the SERIALIZABLE experiment, including the retry:

-- Session 2: SERIALIZABLE withdrawal (this one gets rejected)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = balance - 800 WHERE owner = 'Bob';
-- Wait for Session 1 to COMMIT first
COMMIT;
-- ERROR: serialization_failure (SQLSTATE 40001)

-- Retry with fresh snapshot:
ROLLBACK;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE owner IN ('Alice', 'Bob');
-- Now sees 1500, not 2300. Business logic denies: 1500 - 800 = 700 < 1000.
ROLLBACK;

Secret mission

Trigger the Three-Color Cycle Anomaly

Write skew involves two transactions. Can you trigger a more subtle anomaly involving three transactions that form an impossible cycle? You'll need a third psql session and careful coordination to produce a result that could never happen under serial execution.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your PostgreSQL instance, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • The colors table in your PostgreSQL database.
  • The reset_accounts() function in your PostgreSQL database.

Keep everything running

No action needed. Choose this if you want to keep experimenting with isolation levels, retry patterns, or the three-color cycle anomaly.

  • The colors table and reset_accounts() function stay in your database, ready for future use.
  • Your accounts table currently has modified balances (Alice at 100). Run SELECT reset_accounts(); any time to restore the original values.

Pause - I'll come back to this later

Restore your accounts table to its starting state so the data is clean when you return. The helper objects stay in place for your next session.

  • Run this in any psql session to restore your account balances:
SELECT reset_accounts();
  • Close your extra terminal windows. You can reopen psql sessions any time you want to resume.

Delete - I don't want to use this again

Remove all project resources from your database and restore your accounts to their original state.

  • In any psql session, restore your account balances and then drop the objects you created:
-- Restore accounts to original state
SELECT reset_accounts();

-- Drop the objects created during this project
DROP TABLE IF EXISTS colors;
DROP FUNCTION IF EXISTS reset_accounts;
  • Confirm the cleanup by running:
SELECT * FROM accounts;
\df reset_accounts
\dt colors
  • You should see Alice/900, Bob/1400, Charlie/700 in your accounts table. The \df and \dt commands should return no matching results.
  • Close all your extra terminal windows.

That's a Wrap!

That's a Wrap!

Nice work. You just broke a database on purpose, watched PostgreSQL's SERIALIZABLE isolation catch the exact same attack, and proved why full-transaction retry is the correct production response.

You've learned how to:

  • Triggered write skew under REPEATABLE READ: two concurrent withdrawals both checked the same combined balance and both committed, silently violating the $1000 minimum rule.
  • Proved SERIALIZABLE prevents the same attack: PostgreSQL's SSI engine detected the read/write dependency cycle and rejected the second committer with SQLSTATE 40001.
  • Demonstrated the retry pattern: retrying the rejected transaction gave it a fresh snapshot, and the business logic correctly denied the withdrawal based on real (post-first-withdrawal) data.
  • Secret Mission: Triggered a 3-transaction cycle anomaly that produced an impossible result under REPEATABLE READ, and saw SERIALIZABLE break the cycle by rejecting one transaction.

Ready to quiz yourself?