Handling Deadlocks in Concurrent Spatial Updates

← Back to Async PostGIS Transaction Patterns

Stop concurrent updates to overlapping geometry rows from deadlocking by imposing a consistent lock order, retrying on PostgreSQL’s 40P01, and serialising hotspot writes with advisory locks.

# Context & when to use

A deadlock happens when two transactions each hold a lock the other needs. In spatial workloads this is common because a single “update” often touches several rows — a parcel edit that also updates its neighbours’ shared boundaries, a merge that rewrites two overlapping polygons, or a recomputation that locks every feature inside a bounding box. If transaction A locks rows in the order (17, 42) and transaction B locks them as (42, 17), they can each grab one and wait forever for the other. PostgreSQL breaks the cycle by killing one transaction with ERROR: deadlock detected (SQLSTATE 40P01).

This is the concurrency counterpart to the width-and-boundary rules in the parent async PostGIS transaction patterns guide. Reach for these techniques when multiple writers update the same feature set concurrently — collaborative editing, ingestion that overlaps live edits (including the chunked loads in bulk geometry writes), or any endpoint where two requests can legitimately target overlapping geometries.

There are three complementary tools. Consistent lock ordering prevents most deadlocks outright. Retry on 40P01 handles the ones you cannot design away, because deadlocks are always possible in principle. Advisory locks serialise writers on a known hotspot so they queue politely instead of colliding.

# How two spatial transactions deadlock

# Runnable implementation

Two parts: a decorator that retries a transaction on 40P01/40001, and the SQL that locks rows in a deterministic order so the retries are rarely needed.

# app/concurrency/retry.py
import asyncio
import random
import functools
from asyncpg.exceptions import DeadlockDetectedError, SerializationError
from sqlalchemy.exc import DBAPIError

# PostgreSQL SQLSTATEs worth retrying:
#   40P01 = deadlock_detected, 40001 = serialization_failure
RETRYABLE_SQLSTATES = {"40P01", "40001"}


def _is_retryable(exc: Exception) -> bool:
    orig = getattr(exc, "orig", exc)
    if isinstance(orig, (DeadlockDetectedError, SerializationError)):
        return True
    return getattr(orig, "sqlstate", None) in RETRYABLE_SQLSTATES


def retry_on_deadlock(max_attempts: int = 5, base_delay: float = 0.05, cap: float = 1.0):
    """Retry an async transaction fn on deadlock/serialization failure.
    The wrapped fn MUST be idempotent: it may run several times."""
    def decorator(fn):
        @functools.wraps(fn)
        async def wrapper(*args, **kwargs):
            attempt = 0
            while True:
                try:
                    return await fn(*args, **kwargs)
                except DBAPIError as exc:
                    attempt += 1
                    if attempt >= max_attempts or not _is_retryable(exc):
                        raise
                    # Exponential backoff with full jitter to de-synchronise contenders.
                    delay = min(cap, base_delay * (2 ** (attempt - 1)))
                    await asyncio.sleep(random.uniform(0, delay))
        return wrapper
    return decorator

# app/concurrency/spatial_writes.py
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from .retry import retry_on_deadlock


@retry_on_deadlock(max_attempts=5)
async def merge_overlapping_parcels(session: AsyncSession, ids: list[int]) -> None:
    """Rewrite several overlapping parcels atomically.
    Deadlock-safe because every writer locks rows in ascending id order."""
    async with session.begin():                      # one short, atomic transaction
        # 1. Deterministic lock order: ALWAYS ascending id. This is the whole trick.
        locked = await session.execute(
            text("""
                SELECT id, geom
                FROM parcels
                WHERE id = ANY(:ids)
                ORDER BY id            -- consistent order across all writers
                FOR UPDATE
            """),
            {"ids": sorted(ids)},
        )
        rows = locked.mappings().all()
        if len(rows) != len(ids):
            raise ValueError("Some parcels no longer exist")

        # 2. Now that all target rows are locked in order, do the spatial work.
        await session.execute(
            text("""
                UPDATE parcels
                SET geom = ST_MakeValid(ST_Union(geom) OVER ())
                WHERE id = ANY(:ids)
            """),
            {"ids": ids},
        )


@retry_on_deadlock(max_attempts=8)
async def update_hotspot_cell(session: AsyncSession, cell_key: int, delta_geom_wkb: bytes) -> None:
    """A single grid cell that many writers hit at once. Serialise them with an
    advisory lock instead of letting them fight over row locks."""
    async with session.begin():
        # Transaction-scoped advisory lock: auto-released at COMMIT/ROLLBACK.
        # All writers targeting the same cell_key queue on this one lock.
        await session.execute(
            text("SELECT pg_advisory_xact_lock(:k)"),
            {"k": cell_key},
        )
        await session.execute(
            text("""
                UPDATE coverage_cells
                SET geom = ST_Union(geom, ST_SetSRID(ST_GeomFromWKB(:g), 4326))
                WHERE cell_key = :k
            """),
            {"k": cell_key, "g": delta_geom_wkb},
        )

Advisory locks and row locks solve different shapes of the problem. FOR UPDATE ORDER BY id prevents cycles when a transaction touches a set of rows. pg_advisory_xact_lock serialises writers on a single known hotspot (a grid cell, a shared boundary) so they never contend on the row at all — cheaper than letting them deadlock and retry.

# Key parameters & options

# Gotchas & failure modes

• Retrying a non-idempotent write corrupts data. The retry decorator may run the function several times. If the body does parcel_count = parcel_count + 1 outside the locked, atomic scope, a retried transaction double-counts. Fix: make the whole operation idempotent — derive the new state from locked inputs inside the transaction, or key inserts with ON CONFLICT DO NOTHING — so a replay produces the same result. This is the same idempotency requirement flagged for retried bulk writes in managing async transactions for bulk geometry writes.

• Lock ordering must be consistent across tables too. Ordering rows by id within one table is not enough if transaction A locks parcels then boundaries while B locks boundaries then parcels. Fix: define a global order over tables (e.g. always lock parcels before boundaries) and honour it everywhere.

• ERROR: deadlock detected (40P01) leaves the transaction aborted. After a deadlock, PostgreSQL has already rolled the losing transaction back; every subsequent statement on that session fails with current transaction is aborted, commands ignored until end of transaction block. Fix: the retry must start a fresh transaction, not continue the aborted one — the decorator does this by re-invoking the whole function.

• Serialization failures (40001) are not deadlocks but need the same retry. Under REPEATABLE READ or SERIALIZABLE, a concurrent update surfaces as could not serialize access due to concurrent update (40001). It is expected and retryable — include it in RETRYABLE_SQLSTATES, as above.

• Advisory locks that outlive their transaction. pg_advisory_lock (session-scoped) is not released at COMMIT and, under PgBouncer transaction pooling, leaks onto a backend another client will borrow. Fix: always use the transaction-scoped pg_advisory_xact_lock, which releases automatically — the same SET LOCAL discipline described in the connection pooling guide.

# Verification

Reproduce a deadlock deliberately, then confirm the retry recovers. In two psql sessions, lock in opposite order:

-- Session 1                              -- Session 2
BEGIN;                                    BEGIN;
UPDATE parcels SET status='x'             UPDATE parcels SET status='x'
  WHERE id = 17;                            WHERE id = 42;
--                                        --
UPDATE parcels SET status='x'             UPDATE parcels SET status='x'
  WHERE id = 42;   -- waits                 WHERE id = 17;   -- deadlock!
-- one session now shows:
-- ERROR:  deadlock detected
-- SQLSTATE: 40P01

Then run the decorated path under contention and confirm it succeeds without surfacing a 500. Count deadlocks server-side — a healthy service keeps this number low and flat:

SELECT datname, deadlocks FROM pg_stat_database WHERE datname = current_database();
-- If deadlocks climbs steadily, lock ordering is inconsistent somewhere.

Confirm no advisory locks are leaking between requests:

SELECT locktype, objid, pid FROM pg_locks WHERE locktype = 'advisory';
-- Should be empty between transactions when using pg_advisory_xact_lock.

# Related

• Async PostGIS Transaction Patterns — transaction boundaries, isolation levels, and short lock scopes
• Managing Async Transactions for Bulk Geometry Writes — idempotent, chunked writes that coexist with live updates
• Connection Pooling & PgBouncer Setup — why transaction-scoped locks and SET LOCAL are mandatory under pooling

← Back to Async PostGIS Transaction Patterns