Race Condition in Fastapi with Cockroachdb

A race condition in a Fastapi service using Cockroachdb typically arises when multiple concurrent requests read and write the same data without strict serialization or isolation guarantees being enforced at the application level. Cockroachdb provides strong consistency and serializable isolation by default, but application code that performs read-modify-write sequences without explicit locking or conditional writes can still expose logical races.

Consider an endpoint that reads a bank balance, computes a new balance, and writes it back. Between the read and the write, another request can commit a conflicting transaction, leading to lost updates. Cockroachdb’s serializable isolation will cause one of the concurrent serializable transactions to abort with a retry error (a serialization conflict), but if Fastapi does not retry or otherwise handle this correctly, the client sees a 500 or an inconsistent state. The combination of Fastapi’s asynchronous request handling and Cockroachdb’s distributed serializable model means developers must explicitly manage retries and write conditions, or logical races manifest as data corruption or authorization bypasses such as BOLA/IDOR.

In the context of middleBrick’s checks, such patterns are surfaced under BOLA/IDOR, Property Authorization, and Input Validation tests. For example, an unauthenticated probe may detect that a balance endpoint does not validate ownership on each request, and a property authorization check may identify missing invariants enforced via WHERE clauses or conditional writes. Without proper handling of Cockroachdb transaction retries, an attacker can race two requests to change permissions or financial values, effectively bypassing authorization checks that were assumed to be enforced by the database.

Real-world attack patterns mirror CVE-like scenarios such as time-of-check-to-time-of-use (TOCTOU) in distributed systems. For instance, an inventory endpoint that checks stock > 0 then decrements can be raced to oversell if the decrement is not performed as a single conditional UPDATE. middleBrick’s LLM/AI Security checks do not directly test this, but the broader scan will flag missing authorization and input validation findings that often correlate with these concurrency flaws.

Remediation centers on using explicit SQL conditions and retrying serializable transactions. Always perform read and write as a single conditional UPDATE in Cockroachdb, and implement retry logic for serialization errors returned by the database.

import asyncio
from typing import Optional
from sqlalchemy import text, update
from sqlalchemy.ext.asyncio import AsyncEngine, create_async_engine
from sqlalchemy.pool import NullPool

# Example engine setup; in Fastapi, manage lifecycle with Depends
engine: AsyncEngine = create_async_engine(
    "cockroachdb+asyncpg://user:password@host:26257/dbname?sslmode=require",
    poolclass=NullPool,
)

async def transfer_balance(
    conn, from_account: str, to_account: str, amount: int
):
    # Conditional update in a single statement; Cockroachdb will enforce serializable isolation
    result = await conn.execute(
        update(text("accounts SET balance = balance - :amt WHERE id = :id AND balance >= :amt"))
        .params(amt=amount, id=from_account)
    )
    if result.rowcount != 1:
        raise ValueError("Insufficient funds or account not found")
    await conn.execute(
        update(text("accounts SET balance = balance + :amt WHERE id = :id"))
        .params(amt=amount, id=to_account)
    )

async def safe_transfer(from_account: str, to_account: str, amount: int):
    retry = True
    while retry:
        retry = False
        async with engine.connect() as conn:
            try:
                async with conn.begin() as tx:
                    await transfer_balance(tx, from_account, to_account, amount)
                    await tx.commit()
            except Exception as e:
                # Cockroachdb returns serialization errors for conflicting commits in serializable mode
                if "serializable isolation" in str(e).lower() or "restart transaction" in str(e).lower():
                    retry = True
                    continue
                raise

# Fastapi endpoint example
from fastapi import APIRouter, Depends, HTTPException
router = APIRouter()

@router.post("/transfer")
async def api_transfer(body: dict):
    from_account: str = body.get("from")
    to_account: str = body.get("to")
    amount: int = int(body.get("amount", 0))
    if amount <= 0:
        raise HTTPException(status_code=400, detail="Invalid amount")
    try:
        await safe_transfer(from_account, to_account, amount)
        return {"ok": True}
    except ValueError as ve:
        raise HTTPException(status_code=400, detail=str(ve))

The key points:

• Use conditional UPDATE statements (WHERE balance >= amount) so the database enforces invariants in a single, atomic write.
• Wrap transactions in retry loops for serialization conflicts; Cockroachdb will abort one of the concurrent serializable transactions, and the retry ensures progress without data loss.
• Keep transactions short and avoid client-side read-then-write patterns; if you must read first, validate constraints inside the same conditional write or use SELECT … FOR UPDATE if your access pattern requires explicit locking (Cockroachdb supports this within serializable transactions).
• In Fastapi, manage connection and transaction lifetimes carefully, especially with async handlers; ensure retries do not leak connections or produce side effects like double-charges.

For broader findings such as BOLA/IDOR or Property Authorization, combine these database-level practices with endpoint-level ownership checks. middleBrick’s scans will highlight missing validations; remediation should include both server-side conditional writes and explicit retry logic for Cockroachdb serializable transactions.