Two AI Agents, One Database: Solving Concurrent Updates With Optimistic Locking

AI agents are moving beyond simple chat interfaces and becoming part of real application workflows. In a modern distributed system, one agent might handle customer refunds, another might manage inventory, while a background process updates the same data at the same time.

The main problem isn't always that an agent makes a bad decision. The real issue is that two agents can make valid decisions using the same data and then overwrite each other's changes without knowing that the data has changed.

This article shows how to prevent this problem using Optimistic Concurrency Control (optimistic locking) with Python and SQLite.

1. The Concurrent Agent Problem

In a typical multi-agent setup, multiple agents work through an application or tool layer:

Suppose our database table initially contains a single pending order:

A naive update pattern executes unversioned queries sequentially:

-- Agent A commits approval
UPDATE orders
SET status = 'approved',
    updated_by = 'agent-a'
WHERE id = 101;

-- Agent B commits cancellation moments later
UPDATE orders
SET status = 'cancelled',
    updated_by = 'agent-b'
WHERE id = 101;

Because neither query verifies the record state at the exact moment of execution, Agent B silently destroys Agent A's update. This is the classic lost update anomaly.

2. Why AI Agents Widen the Concurrency Window

In a traditional web application, updates usually happen within a short request-response cycle. The time between reading and updating data is often limited to network and database delays.

Agentic systems work differently. An agent may take much longer to complete a task:

An agent may retrieve data, call an LLM, run tools, and process the results before writing back to the database. This read-to-write cycle can take seconds or even minutes.

During that time, another agent or process may change the same record. The database therefore needs to check that the data has not changed since the agent first read it.

3. Mechanics of Optimistic Locking

Optimistic locking assumes that conflicts are uncommon. It prevents them by checking a record's version before every update.

The database table includes a version number that increases with each successful update:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    status TEXT NOT NULL,
    updated_by TEXT,
    version INTEGER NOT NULL
);

INSERT INTO orders (id, status, updated_by, version)
VALUES (101, 'pending', 'system', 1);

When an agent reads an order, it also reads its current version, such as version = 1. When the agent updates the order, the application checks that the version is still 1 and increases it to 2 as part of the same update.

UPDATE orders
SET status = 'approved',
    updated_by = 'agent-a',
    version = version + 1
WHERE id = 101
  AND version = 1;

If the record is still at version 1, the update succeeds, and the version becomes 2.

UPDATE orders
SET status = 'cancelled',
    updated_by = 'agent-b',
    version = version + 1
WHERE id = 101
  AND version = 1;  -- Evaluates to FALSE! (Current database version is 2)

Now, if Agent B tries to update the same record using version 1, the condition fails because the database already has version 2.

The update affects zero rows, so the application knows that the data has changed and safely rejects the stale update.

4. Python and SQLite Implementation

Here is a simple implementation that shows how to set up the database, read records, and safely update them using version checks.

Database Setup and Helper Functions

import sqlite3

def initialize_database():
    with sqlite3.connect("orders.db") as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY,
                status TEXT NOT NULL,
                updated_by TEXT,
                version INTEGER NOT NULL
            )
        """)
        conn.execute("""
            INSERT OR IGNORE INTO orders (id, status, updated_by, version)
            VALUES (101, 'pending', 'system', 1)
        """)
        conn.commit()

def get_order(order_id: int):
    with sqlite3.connect("orders.db") as conn:
        cursor = conn.execute("""
            SELECT id, status, updated_by, version
            FROM orders
            WHERE id = ?
        """, (order_id,))
        return cursor.fetchone()

def update_order(order_id: int, status: str, agent: str, expected_version: int):
    with sqlite3.connect("orders.db") as conn:
        cursor = conn.execute("""
            UPDATE orders
            SET status = ?,
                updated_by = ?,
                version = version + 1
            WHERE id = ?
              AND version = ?
        """, (status, agent, order_id, expected_version))

        conn.commit()

        if cursor.rowcount == 0:
            raise RuntimeError(
                f"Conflict detected on order {order_id}. "
                f"Expected version {expected_version}, but the record has changed."
            )
        return True

Demonstrating a Stale Update

# Both agents inspect the order concurrently
order_agent_a = get_order(101)
order_agent_b = get_order(101)

print(f"Agent A snapshot: {order_agent_a}")
print(f"Agent B snapshot: {order_agent_b}")

# Agent A successfully commits
update_order(
    order_id=101,
    status="approved",
    agent="agent-a",
    expected_version=order_agent_a[3]
)
print("Agent A update: SUCCESS (Version 1 -> 2)")

# Agent B attempts update with stale version
try:
    update_order(
        order_id=101,
        status="cancelled",
        agent="agent-b",
        expected_version=order_agent_b[3]
    )
except RuntimeError as err:
    print(f"Agent B update: FAILED -> {err}")

Console Output: Agent A snapshot: (101, 'pending', 'system', 1)
Agent B snapshot: (101, 'pending', 'system', 1)
Agent A update: SUCCESS (Version 1 -> 2)
Agent B update: FAILED -> Conflict detected on order 101. Expected version 1, but the record has changed.

5. Conflict Resolution: What to Do Next

Detecting a conflict is only the first step. What the application does next is important for keeping the system reliable.

A common mistake is to retry the failed operation with the latest version without checking the updated data.

Don't blindly retry an agent's decision. For example, an agent may decide to cancel an order because it was pending. If the order is now shipped, retrying the cancellation could create a serious data or business logic problem.

6. Optimistic Locking vs. Idempotency

These two patterns solve different problems:

• Optimistic locking: Prevents stale updates when multiple agents or processes change the same record.
• Idempotency keys: Prevents the same request from being processed more than once, such as after a timeout or retry.

7. Architectural Separation: Keep Consistency Out of the Model

LLMs are good at reasoning, but they are not designed to manage database consistency. System prompts alone cannot guarantee data integrity.

Core Principle: Layered Responsibility

• AI agent: Decides what action to take, such as "Cancel order #101."
• Application service: Checks permissions and business rules.
• Database: Ensures data stays consistent using version checks and atomic updates.

8. Testing Concurrent Updates 

The earlier example shows how a stale update is rejected. We can take it one step further and run two updates at the same time.

Python's threading module can help simulate two agents working on the same order:

import threading

def agent_update(agent, status):
    order = get_order(101)

    try:
        update_order(
            order_id=101,
            status=status,
            agent=agent,
            expected_version=order[3]
        )
        print(f"{agent}: update successful")
    except RuntimeError as err:
        print(f"{agent}: update failed -> {err}")


thread_a = threading.Thread(
    target=agent_update,
    args=("agent-a", "approved")
)

thread_b = threading.Thread(
    target=agent_update,
    args=("agent-b", "cancelled")
)

thread_a.start()
thread_b.start()

thread_a.join()
thread_b.join()

Both threads read the order and try to update it. Only the update using the current version can succeed.

The other update receives a conflict instead of overwriting the first change.

The exact order of the output can vary because the threads run concurrently. For example:

agent-a: update successful
agent-b: update failed -> Conflict detected on order 101.

This is the key benefit of optimistic locking: the database decides which update is valid based on the version, rather than relying on which agent happens to write last. 

9. Production Checklist

When building multi-agent workflows that update operational databases, keep these practices in mind:

• Use transactions: Use transactions when updating multiple tables so all changes succeed or fail together.
• Add backoff and jitter: Add random delays between retries to avoid too many agents retrying at the same time.
• Keep audit logs: Record details such as the agent ID, model version, and data version for each update.
• Limit retries: Limit automatic retries to 2–3 attempts. If conflicts continue, send the task for human review.

Final Thoughts 

As AI agents become more involved in real-world applications, traditional distributed systems problems become important again.

Two agents can make correct decisions independently but still cause problems when they update the same data at the same time.

Treating agents as independent clients and using optimistic locking at the database level helps prevent stale updates and keeps data consistent, even when agents take longer to complete their tasks.