When two agents write the same customer record: transactions and isolation levels

Picture a Monday morning. A customer reports that an account was charged the wrong number of credits. You open the logs. The support agent added 50 credits as compensation, the billing agent deducted a 30-credit fee, and both reported success. The balance should be 120. The database says 70.

The code has no bug in the usual sense. Each agent ran its own logic correctly. They just ran at the same moment. Once you deploy agents into a customer’s system, several processes writing the same record becomes a routine event. The number left in the database then depends on how you use transactions and on the isolation level the database runs at.

Most engineers know the acronym ACID. Few know how the I is configured on the customer’s database. An FDE needs to know, because this class of bug throws no exception. It quietly corrupts the data and waits for the customer to notice.

Where did the 50 credits go?

Put the two sessions side by side in time order and the answer is obvious. Both agents share the same code: read the balance, compute the new balance in Python, write it back.

-- Agent A (support)              -- Agent B (billing)
SELECT balance FROM accounts      SELECT balance FROM accounts
 WHERE id = 42;   -- 100           WHERE id = 42;   -- 100
-- compute 100 + 50 = 150         -- compute 100 - 30 = 70
UPDATE accounts SET balance = 150
 WHERE id = 42; COMMIT;
                                  UPDATE accounts SET balance = 70
                                   WHERE id = 42; COMMIT;

This is a lost update: the later write overwrites the earlier one without ever knowing it existed. The 70 was computed in Python from a stale read, so the database receives a constant and simply stores it.

And Read Committed is PostgreSQL’s default. Unless someone has deliberately changed the configuration, read-compute-write code like this has no protection against two agents interleaving.

Three fixes, each for a different kind of problem

The simplest fix is to let the database do the arithmetic. With UPDATE accounts SET balance = balance + 50 WHERE id = 42, the addition lives inside the statement, so there is no constant computed from a stale read as in the example above.

For changes that add or subtract a number, try this first. Even so, verify it yourself with two psql windows against the customer’s actual database.

Agents, though, often have to read before they decide: deduct the fee only if the balance covers it, or ask an LLM to choose an action based on the customer’s current state. That calls for pessimistic locking. SELECT ... FOR UPDATE locks the rows it reads as if they were about to be updated, stopping the other agent from writing over them.

BEGIN;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- decide in code while row 42 is locked
UPDATE accounts SET balance = 70 WHERE id = 42;
COMMIT;

The third fix is for PostgreSQL: run the transaction at Repeatable Read or Serializable and accept that the database may abort it with a serialization error. The PostgreSQL documentation requires applications running at Repeatable Read to be ready to retry on that error. When it happens, you must abort and rerun the whole transaction from the start, not just the statement that failed.

Note that this describes PostgreSQL’s behaviour. MySQL InnoDB also has a level called REPEATABLE READ, but the same name does not mean the same behaviour, so don’t assume it protects you in the same way.

The retry loop must also catch a second kind of error. In psycopg, SerializationFailure (code 40001) and DeadlockDetected (code 40P01) are separate error classes. Catch only the first and your retry loop will miss deadlocks.

import random
import time

from psycopg import errors

RETRYABLE = (errors.SerializationFailure, errors.DeadlockDetected)

def run_with_retry(conn, work, attempts=5):
    for i in range(attempts):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
                return work(conn)  # re-read from scratch on every attempt
        except RETRYABLE:
            if i == attempts - 1:
                raise
            # jittered backoff so the two agents don't collide again straight away
            time.sleep(0.05 * 2 ** i + random.uniform(0, 0.05))

The most important point in this code is that work must re-read the data on every attempt. If the agent holds on to the old read, or to an LLM answer computed from the old state, a retry just writes the wrong value one more time.

The time.sleep line is exponential backoff with a little randomness. Without it, two agents that have just collided can retry immediately and collide again.

Fix	Mechanism	Suits
Atomic update	The computation lives in the statement, no stale values	Adding, subtracting, counting
SELECT ... FOR UPDATE	Locks the rows just read so the other agent cannot overwrite them	Read then decide, high contention
Repeatable Read/Serializable + retry (PostgreSQL)	PostgreSQL aborts the transaction with a serialization error and the client reruns the whole transaction	Low contention, complex logic

Snapshots still let one kind of bug through

Raising the isolation level leaves one more trap. Jepsen describes snapshot isolation as allowing write skew: transactions read the same shared state, each modifies a different set of objects, and both commit. The PostgreSQL documentation likewise says Repeatable Read prevents every phenomenon in its table except serialization anomaly, the category that contains write skew.

Suppose the customer has a rule that every account must keep at least one emergency contact, and account 42 currently has two. Agent A removes the first contact, agent B removes the second.

Both count two contacts and decide it is safe. Each deletes a different row, no row is written twice, so there is no conflict to report. The account ends up with nobody to call.

That is why the PostgreSQL documentation warns that enforcing business rules at Repeatable Read is hard to get right without careful explicit locking. The essence of write skew is that the two sides never touch the same row, so a sensible fix is to force both through a shared row: SELECT ... FOR UPDATE on account 42’s own row before counting.

The other option is Serializable. The PostgreSQL wiki describes write skew as the simplest anomaly SSI prevents: whoever commits first wins, and the other transaction is rolled back and must run again, which brings back the retry loop above.

Same isolation level name, different meaning in each database

This is where FDEs trip most often when moving from one customer to the next. MySQL InnoDB defaults to REPEATABLE READ, not Read Committed as PostgreSQL does. CockroachDB defaults to SERIALIZABLE and requires the client to implement its own retry logic, with error code 40001.

Martin Kleppmann, in Hermitage, his project testing the I in ACID, observes that a name like repeatable read means different things in different databases.

The consequence is plain. Agent code that worked on CockroachDB in your last project, if it relied on the database to block conflicts, can corrupt data on a default PostgreSQL. Conversely, code written for PostgreSQL without retries will hit 40001 errors on CockroachDB that nothing handles. Check the real configuration; don’t trust memory.

More locks bring another risk: deadlock

Once you use FOR UPDATE, you will meet deadlocks: agent A locks the account and waits for the invoice, agent B locks the invoice and waits for the account. PostgreSQL detects this and aborts one transaction so the others can proceed.

A deadlock is a different error from a serialization failure, so even at Read Committed you need a retry loop, and it must catch DeadlockDetected as the code above does. The MySQL documentation gives similar advice: always be prepared to rerun a transaction that was rolled back because of a deadlock.

According to the PostgreSQL documentation, the best defence is for every application to lock multiple objects in the same order. You can set a convention for the whole agent system, such as always locking the account before the invoice, or locking in ascending id order, then write that convention into the handover documentation so the customer’s team doesn’t break it later.

Common traps on customer sites

The most common trap is letting agents generate read-compute-write SQL without anyone checking it for contention. Just as common: retries that don’t re-read the data, retries fired in rapid succession with no backoff, or retries that catch serialization failures but forget deadlocks.

The trap specific to agent systems is holding a transaction open while waiting for the LLM to respond. The MySQL documentation advises keeping transactions small and short to reduce collisions. So read the data, call the model outside the transaction, then open a short transaction to re-check and write.

Next time, before you let a second agent write to the same table, ask yourself which number would survive if both agents ran in the same millisecond. In an FDE interview, describing a time you found a lost update like this and fixed it will persuade far more than the word “PostgreSQL” on a skills list.