Distributed Locking for Agent Swarms: How We Prevented Two Agents from Editing the Same Postgres Row

Agent swarms are great for parallelizing work, but they introduce a classic problem: two agents updating the same database row simultaneously. Without coordination, you get lost updates, inconsistent state, and hard-to-debug errors. In this article, I'll show you how we solved this using Postgres advisory locks and row-level locking, without introducing external lock managers.

The Problem: Race Conditions in Agent Swarms

Imagine a swarm of agents processing orders. Each agent might need to update the status field of an order row. If two agents pick up the same order concurrently, they both read status = 'pending', both decide to set it to 'processing', and the second write overwrites the first. The order ends up in 'processing' but the first agent's work is lost.

This is a classic lost update problem. In a single-threaded application, you'd use a mutex. In a distributed swarm, you need distributed locking.

Why Not Redis or ZooKeeper?

You could use Redis Redlock or ZooKeeper for distributed locks, but that adds operational complexity and latency. If your state is already in Postgres, using Postgres itself for locking is simpler and faster. No extra services, no network hops, and you get strong consistency guarantees.

Postgres Advisory Locks

Postgres provides advisory locks: application-defined locks that are not tied to any table row. They are stored in memory and are very fast. You can acquire a lock with a unique integer key (often the row's ID) and release it when done.

-- Acquire an exclusive advisory lock for order id 42
SELECT pg_advisory_xact_lock(42);
-- Now do your work
UPDATE orders SET status = 'processing' WHERE id = 42;
-- Lock is released automatically at transaction commit/rollback

But advisory locks have a gotcha: they are session-level by default. pg_advisory_xact_lock is transaction-scoped, which is usually what you want. However, if you need to hold the lock across multiple transactions, use pg_advisory_lock and release manually.

Row-Level Locking with FOR UPDATE

For row-specific coordination, Postgres row-level locks are even more natural. When you SELECT ... FOR UPDATE, that row is locked until the transaction ends. No other transaction can update or delete that row, and any other SELECT ... FOR UPDATE will wait.

BEGIN;
SELECT * FROM orders WHERE id = 42 FOR UPDATE;
-- Agent can now safely update
UPDATE orders SET status = 'processing' WHERE id = 42;
COMMIT;

This is perfect for agent swarms: each agent picks a row, locks it, does its work, and releases on commit. If another agent tries to lock the same row, it waits.

Handling Deadlocks

Deadlocks can happen if agents lock rows in different orders. For example, Agent A locks row 1 then tries row 2, while Agent B locks row 2 then tries row 1. Postgres detects deadlocks and aborts one transaction. Your code must retry.

To minimize deadlocks, enforce a consistent lock order. For example, always lock rows in ascending ID order. If your agents process multiple rows, sort the IDs before locking.

-- Sort IDs to prevent deadlocks
SELECT * FROM orders WHERE id IN (42, 99) ORDER BY id FOR UPDATE;

Practical Implementation in Python

Here's a simple Python function using psycopg2 that acquires a row lock and retries on deadlock:

import psycopg2
from psycopg2 import errors
import time

def update_order_with_lock(order_id, new_status, max_retries=3):
    conn = psycopg2.connect("dbname=orders")
    for attempt in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SELECT id FROM orders WHERE id = %s FOR UPDATE", (order_id,))
                    cur.execute("UPDATE orders SET status = %s WHERE id = %s", (new_status, order_id))
            return True
        except errors.SerializationFailure as e:
            # Deadlock or serialization error
            conn.rollback()
            time.sleep(0.1 * (attempt + 1))
            continue
    conn.close()
    return False

This uses with conn: to auto-commit on success and rollback on exception. The retry with exponential backoff handles transient deadlocks.

Advisory Locks vs Row Locks: When to Use Which

• Row locks (FOR UPDATE): Best when you are going to update the locked row. They integrate naturally with your SQL.
• Advisory locks: Useful when you need to lock a resource that isn't a row, or when you need to lock across multiple tables. Also, advisory locks don't block SELECT queries, only other advisory lock attempts.

In our agent swarm, we often use row locks for direct row updates and advisory locks for coordinating higher-level tasks like "only one agent should process this batch of orders."

Monitoring Locks

You can inspect current locks in Postgres:

SELECT pid, locktype, relation::regclass, page, tuple, mode, granted
FROM pg_locks
WHERE NOT granted;

This shows waiting locks. For advisory locks, use pg_locks with locktype = 'advisory'.

Conclusion

Postgres provides robust locking primitives that are perfect for coordinating agent swarms. Start with row-level FOR UPDATE for simple cases, use advisory locks for cross-row coordination, and always order your locks to avoid deadlocks. No extra infrastructure needed.