DEV Community

Cover image for Don't Hold a Database Transaction While the LLM Thinks
Harut Petrosyan
Harut Petrosyan

Posted on Edited on AI-assisted

Don't Hold a Database Transaction While the LLM Thinks

Every call to a language model costs money. If your users pay with prepaid credits, each call is charged against a balance that other calls are spending at the same moment. I built this on top of my PostgreSQL double-entry wallet ledger, and both obvious designs turned out to be wrong.

Two naive designs

Charge inside one transaction. Open a transaction, lock the balance row, call the model, write the charge, commit. This never overspends, but the row lock is held for as long as the model takes to answer: seconds, sometimes much longer. Every other operation on that wallet waits behind it, including a top-up or a second AI call, and a pooled connection sits idle the whole time.

Charge after the call. Call the model first, then write the charge. Nothing is locked while the model runs, but nothing is reserved either. Twenty requests can read the same balance, all call the model, and all try to charge. Without a constraint, the balance goes negative. With a non-negative constraint, the late charges fail, which is no better: the model has already done the work, and you can't bill for it.

The pattern: reserve, run, then capture or release

The fix is the one card payments use: an authorization hold.

  1. Reserve. Estimate the most the call can cost (estimated input tokens plus the maximum output tokens, at the model's price). Move that amount from the user's available balance to a held balance, and commit.
  2. Run. Call the model with no database transaction open, no row lock held and no DbContext kept alive.
  3. Capture or release. When the answer arrives, capture the actual cost from the hold and return the rest to the available balance. If the call failed before producing anything, release the whole hold.

Where the wallet lock is held

In my ledger a hold is ordinary double-entry: one movement from available to held, then one from held to the settlement account, or back to available. The existing rules still apply: balanced entries, idempotency and the non-negative balance check.

I wrapped it in a DelegatingChatClient from Microsoft.Extensions.AI, so application code keeps calling a normal IChatClient:

public override async Task<ChatResponse> GetResponseAsync(
    IEnumerable<ChatMessage> messages, ChatOptions? options = null,
    CancellationToken cancellationToken = default)
{
    var input = messages.ToArray();
    var call = await Reserve(input, options, cancellationToken); // commits

    ChatResponse response;
    try
    {
        response = await InnerClient.GetResponseAsync(input, call.Options, cancellationToken);
    }
    catch
    {
        await Settle(call, release: true);
        throw;
    }

    await Settle(call, usage: response.Usage); // capture the actual cost
    return response;
}
Enter fullscreen mode Exit fullscreen mode

One test checks the "nothing is locked while the model runs" claim directly. While the fake model is "thinking", it tops up the same wallet from a separate connection, with a five-second timeout. If the reserve transaction were still open, the top-up would wait on the wallet lock and the test would fail.

Four traps, each with its test

1. An ambient TransactionScope swallows the reserve

Npgsql enlists connections in an ambient TransactionScope by default (Enlist=true). If the caller has a scope open, the reserve doesn't commit on its own: it joins the caller's transaction. If the caller then rolls back, the hold and the charge roll back with it, although the model has already run.

The fix goes around both the reserve and the settlement:

using var ambient = new TransactionScope(
    TransactionScopeOption.Suppress, TransactionScopeAsyncFlowOption.Enabled);
Enter fullscreen mode Exit fullscreen mode

The test opens a scope, makes the call, and disposes the scope without completing it, so the caller's transaction rolls back. The hold must still end up captured, with the right amount.

2. Streaming: when does the user have the answer?

With GetStreamingResponseAsync, part of the answer can reach the user before something goes wrong. So the closing rule depends on when it went wrong:

What happened What the hold does
Failure or cancellation before the first chunk Released: the user got nothing
Stream completed with a usage report Captured at the reported cost
Stream completed without a usage report Captured in full
Failure, cancellation or the caller stops reading after the first chunk Captured in full

In the last row the user already has part of the answer, and the real usage is unknown. The reservation is the most the user agreed to pay, so that is the charge.

var call = await Reserve(input, options, cancellationToken);
var received = false;
var completed = false;
UsageDetails? usage = null;
try
{
    await foreach (var update in InnerClient.GetStreamingResponseAsync(
        input, call.Options, cancellationToken))
    {
        received = true;
        // Usage is a cumulative snapshot: the last one wins. Copy it before
        // yielding, because the caller receives the same object.
        foreach (var content in update.Contents.OfType<UsageContent>())
            usage = Copy(content.Details);
        yield return update;
    }
    completed = true;
}
finally
{
    // Also runs when the caller breaks out of its await foreach.
    await Settle(call, release: !received && !completed,
        usage: completed ? usage : null);
}
Enter fullscreen mode Exit fullscreen mode

Three details here came from tests:

  • Settlement lives in finally, which also runs when the caller breaks out of the loop and the enumerator is disposed.
  • Settlement uses its own 30-second timeout, not the caller's token, which may already be cancelled.
  • The usage is copied before the chunk is yielded. One test sets the token counts to zero inside its own await foreach and checks that the charge doesn't change.

3. Rounding and overage

Prices are per million tokens, and balances have four decimal places. Both the reservation and the actual cost round up to four places (simplified here; the real version also prices cached input tokens):

var cost = (inputTokens * price.InputPerMillion
          + outputTokens * price.OutputPerMillion) / 1_000_000m;
return decimal.Ceiling(cost * 10_000m) / 10_000m;
Enter fullscreen mode Exit fullscreen mode

Rounding down would make every small enough call free. In the test, at 33 credits per million tokens, a reservation for 4 tokens (0.000132) becomes 0.0002, and 2 used tokens (0.000066) are charged 0.0001.

The estimate can also be too low. My default estimator counts characters; it isn't a tokenizer. When the reported cost is higher than the reservation, the capture is capped at the reservation, because that is the amount the balance was checked against. The difference goes to an append-only overage table, in the same database transaction as the capture. The user is never charged beyond the hold, and the operator can see how often the estimate was too low.

A related rule: a failed settlement never turns a successful answer into an error. If the capture fails, the error is logged, the response is returned, and the hold stays open for the expiry worker. Throwing would make the caller retry a call that already worked and pay for it twice. The test forces the failure with a trigger that rejects capture rows.

4. Twenty calls, one small balance

The race test gives a wallet 25 credits. Every call reserves 5, and the fake model reports no usage, so each accepted call captures its full 5. Twenty calls start in parallel, each with its own request id. The test asserts that:

  • exactly 5 calls are accepted and 15 are refused with insufficient_credits;
  • the model is called exactly 5 times;
  • the available and held balances both end at 0;
  • all 5 holds end captured.

The refusal happens at reserve time, before the model is called, so a refused request costs nothing. The reserve locks the account's wallet rows in a fixed order and checks the balance, and a non-negative CHECK constraint in PostgreSQL backs it up.

What this doesn't solve

  • A crash in the middle of a call. If the process dies between the reserve and the settlement, the hold stays open until a background worker expires it after its time-to-live. By default the worker captures the full reservation, because the model may well have run; it can be set to release instead. So a user can pay the reservation for an answer they never saw.
  • Retries. Deduplication works only if the caller passes a stable request id. A second call with the same id is refused before the model runs. It does not replay the first answer, so this is not exactly-once execution. Without a request id, every call gets a new one, and a retry is a new paid call.
  • The estimate. A character count is a heuristic, not an upper bound. Plug in a real tokenizer for your model; a low estimate shows up as overage rows, not as extra charges.
  • One hot row. Every capture credits the same settlement account, so under heavy load that row is where calls queue.

This article was written with help from AI (Claude).

Top comments (0)