FDE Times: When two agents write the same customer record — transactions and isolation levels
A 4 November FDE Times guide shows how read-compute-write agent code loses updates under PostgreSQL’s default Read Committed: prefer atomic UPDATEs, SELECT … FOR UPDATE, or Repeatable Read/Serializable with retries that re-read state and catch both serialization failures (40001) and deadlocks (40P01).
Two agents both read balance 100, one adds 50 and one subtracts 30, both “succeed”—and the database ends at 70. FDE Times’ 4 November guide frames this as a classic lost update: each agent’s logic is fine; they interleaved on the customer’s DB, and nothing threw an error for ops to catch.
PostgreSQL defaults to Read Committed (MySQL InnoDB to REPEATABLE READ, CockroachDB to SERIALIZABLE), so the same Python read-compute-write pattern behaves differently across customers. For add/subtract, put the arithmetic in the statement (SET balance = balance + 50). When the agent must read before deciding, use SELECT … FOR UPDATE, or raise isolation and retry whole transactions on serialization failure—re-reading state every attempt, with jittered backoff.
Retry loops must catch both SerializationFailure (40001) and DeadlockDetected (40P01). Write skew still slips through snapshot/Repeatable Read when two agents each delete a different emergency contact after counting “two remaining”; force a shared row lock or use Serializable. Don’t hold a transaction open while waiting on an LLM—read, call the model outside, then open a short transaction to re-check and write.
Before a second agent writes the same table, ask which number survives if both run in the same millisecond—and verify on the customer’s real engine, not the name of the isolation level alone.
Source: fdetimes.net