Why Concurrent Agent Runs Break Invariants Across Database Rows

Two runs, one rule, three credits

The help desk at Belgavi's Book Store lets its agent grant goodwill store credit, with one rule from the finance team: at most two goodwill credits per customer per calendar month. Geetha already has one this month. On a Tuesday morning she writes about a damaged atlas by email (CASE-4101) and, ten minutes later, opens a chat about a late delivery (CASE-4102). Each case starts its own agent run.

Both runs call the read tool list_goodwill_credits within a second of each other and both see one credit. Both reason for a few seconds, both decide a credit is fair, and both call grant_goodwill_credit. Each tool call inserts one row, each insert succeeds, and Geetha ends the month with three credits. No row was overwritten, no version check failed, and no error was logged anywhere.

Why this is not a lost update

A lost update is two writers changing the same row, and the usual fix is a version check: write only if the row still has the version you read. That fix protects the rows you write. Here neither run writes a row the other read or wrote. The rule spans a set of rows, the decision depends on a count over that set, and each run adds a new member.

This is write skew: two transactions read an overlapping set, make disjoint writes, and together break a constraint that each would have kept alone. Snapshot isolation does not prevent it. The PostgreSQL documentation lists serialization anomalies as possible at Repeatable Read, and warns that attempts to enforce business rules at that level are not likely to work correctly without careful use of explicit locks to block conflicting transactions. Across two agent tool calls there is not even one transaction: the read commits long before the write starts.

Agents turn a rare race into a routine one. A web form reads and writes within milliseconds. An agent run reads, then spends seconds producing its next step, and the same customer can have runs open from email, chat, a retry of a timed-out run and a sub-agent fanned out by a planner. The window between check and act is thousands of times longer.

What stays deterministic

The read tool still exists and still helps the agent explain itself, but its answer is advice. The write tool is the only place where the limit is enforced, and it enforces it against data it reads inside its own transaction.

Guards that work and one that does not

The quota row is usually the simplest correct choice because it turns a constraint over a set into a write to one row, which is the case version checks and row locks were built for. The PostgreSQL documentation describes the mechanism: under Read Committed, an UPDATE that finds a row being updated by another transaction waits for it to commit, then re-evaluates its WHERE clause against the updated row. The second run's used < 2 is false after the first commits, so it updates nothing.

The write tool, rewritten

Python with a psycopg-style connection; illustrative, checked for syntax. The quota row and the credit row commit together or not at all.

QUOTA_SQL = """
INSERT INTO goodwill_quota (customer_id, month, used)
SELECT %s, %s, count(*) FROM goodwill_credit WHERE customer_id = %s AND month = %s
ON CONFLICT (customer_id, month) DO NOTHING
"""
CLAIM_SQL = """
UPDATE goodwill_quota SET used = used + 1
WHERE customer_id = %s AND month = %s AND used < %s
RETURNING used
"""

def grant_goodwill_credit(conn, customer_id, case_id, amount, limit=2):
    month = store_month_now()                    # server clock, store time zone
    with conn.transaction():
        conn.execute(QUOTA_SQL, (customer_id, month, customer_id, month))
        row = conn.execute(CLAIM_SQL, (customer_id, month, limit)).fetchone()
        if row is None:
            return {"status": "limit_reached", "limit": limit, "month": month,
                    "next_step": "explain the limit or hand the case to a person"}
        conn.execute("INSERT INTO goodwill_credit (customer_id, case_id, amount, month) "
                     "VALUES (%s, %s, %s, %s)", (customer_id, case_id, amount, month))
    return {"status": "granted", "used": row[0], "limit": limit}

The quota row starts from the credits that already exist, so switching mid-month counts them. Three more details matter. The month is computed by the tool, not passed in by the model, so a run cannot dodge the limit by naming another month. The refusal is a normal result with a next step, not an exception the agent framework might retry. And the transaction contains no model call: the lock on the quota row is held for milliseconds, not for the seconds a model takes to think.

Worked example, with and without the guard

Without the quota row, the 3.8 s call inserts a third credit. The companion lab replays this with up to six runs, different think times and each guard in the table, and counts invariant violations, refusals and retries. With SERIALIZABLE and no retry budget, the second run fails with an error instead of a clean refusal, which an agent loop tends to treat as a transient failure and retry with a different tool. That is why the guard should return a domain result.

Failure modes to test

• Two paths, one guard. The agent tool takes the quota row, but the back-office screen inserts credits directly. Put the guard in the database layer both use, or add a constraint trigger.
• Wrong key. The month is computed in UTC while the rule is in store time, so a credit at 00:30 on the first of the month counts against the previous month.
• Retried reasoning. A serialization failure retries the whole agent step, including a new model call that may now decide differently. Retry the transaction, not the reasoning.
• Workaround tools. After limit_reached, the agent issues a discount code instead. Decide which tools count against the same budget and enforce it on all of them.
• Lock held across thinking. A run takes SELECT ... FOR UPDATE in its read tool and holds the transaction open until its write tool runs. Every other run for that customer waits on the model.

Migration steps for an existing service

1. List the rules that count or sum over rows: limits per customer, capacity, one active item per owner, minimum staffing. These are the ones row-level checks miss.
2. For each, find every write path, including agent tools, admin screens, batch jobs and imports.
3. Pick a guard per rule: a partial unique index for a limit of one, a quota or counter row for small limits, SERIALIZABLE where many rules share a transaction and the team can handle retries.
4. Move the check into the write tool and return a typed refusal. Keep the read tool as advice.
5. Run a concurrency test that starts several writers behind a barrier for the same key and asserts the invariant afterwards. Keep it in CI.
6. Add a periodic query that counts violations in production, so a missed path shows up as a number rather than a finance complaint.

When this is unnecessary

If all work for one customer is serialised already, for example a queue partitioned by customer with one consumer per partition, two runs never overlap and the read-then-write tool pair is safe. If the rule is soft, such as a guideline that is reviewed monthly, an alert on violations may be cheaper than a guard. And if the limit is one, a partial unique index expresses it directly and needs no new table. Adding an agent does not change these cases; it changes how often the unguarded ones fire.

Verification

The companion example runs both tool versions against SQLite with two threads released by a barrier and checks the final count; the unguarded version ends with three credits and the guarded one with two and one limit_reached. In your own system, the same test against the real database is the evidence that matters, together with the violation count staying at zero after release.

Practice and related reading

Hands-on: Write skew between agent runs: guarding a monthly credit limit, an interactive lab on synthetic data.

Further reading:

• Agents and Lost Updates: Version Checks and Re-Planning on Conflict
• Snapshot isolation architecture, in depth
• Postgres Isolation Levels, in depth

Sources and scope

Checked against these sources on 3 November 2026. Timelines and counts are from the deterministic lab model and the companion example, which runs on SQLite with synthetic data; nothing here is a production measurement.

• PostgreSQL documentation: Transaction Isolation (Repeatable Read, Serializable, serialization failures)
• PostgreSQL documentation: Explicit Locking (row-level locks, advisory locks)
• PostgreSQL documentation: UPDATE (WHERE conditions, RETURNING)
• Anthropic: Building effective agents (workflows versus agents, when not to use agents)