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 (4)

Collapse
 
prpatel05 profile image
Pratik Patel •

The place I'd expect this to bite next is a tool-calling agent. Each model call reserves and settles on its own, so a 12-step run can pass reserve on step 1 and get refused with insufficient_credits on step 8, after the agent already wrote a file or sent something. Per call, the ledger is consistent. Per task, the user paid for half a job and got a half-applied change.

I'd put a run-level hold on top: reserve a budget for the whole loop when the run starts, capture each call against that hold, release the remainder when the run ends, and refuse at the start of the run instead of at step 8. Your expiry worker already covers a run that dies mid-loop. Are your callers mostly single-shot so far, or have you hit this yet?

Collapse
 
harut_petrosyan profile image
Harut Petrosyan •

Thanks, Pratik, that's a sharp point, and from how CreditsKit works today, you're right. Per call the ledger stays consistent; per task it doesn't.

Right now CreditsChatClient reserves and settles per model call: it reserves a hold, then captures the actual usage or releases the hold on failure. On the engine side a hold is single-capture. CaptureAsync closes the hold and returns any uncaptured amount to the lots, and a second capture against the same hold is refused with a 409 unless it's a replay with the same amount. So capturing each call against a shared hold isn't supported today.

Without code changes, you can get close to a run-level budget with the engine directly: ReserveAsync for the full estimated budget when the run starts (so it fails early if the credits aren't there), track usage in the app during the loop, and make one CaptureAsync for the total at the end. If the run dies midway, the expiry worker closes the hold. The trade-offs: per-call usage isn't in the ledger until the run finishes, and you can't capture more than the reserved budget.

Proper multi-capture holds, where partial captures keep the hold open until an explicit close, together with a way to attach calls to an existing run hold (for example through CreditsScope), are a good candidate I'm considering for 1.1. Nothing is committed yet.

To your question: I haven't hit this yet. The kit launched on Nov 18, so I don't have feedback from real multi-step callers. You've spotted the gap in the design before I ran into it in practice.

Collapse
 
lin7c profile image
xcl •

The streaming settlement table is the most useful part for me, especially the rule that a stream completed without a usage report is captured in full. That seems like the right conservative default when the caller may already have part of the answer, though it also makes accurate usage reporting the real cost-control lever. I also liked the TransactionScope.Suppress detail — an ambient scope silently swallowing the reserve is exactly the kind of failure that would look like a race in production. One question: in the 20-call race test, did the PostgreSQL CHECK constraint ever fire as a second line of defence, or did the reserve check alone reject all 15 excess calls? And for the hot settlement row, would sharding that account or batching captures be the first scaling step you would try?

Collapse
 
harut_petrosyan profile image
Harut Petrosyan •

Thanks! Glad the streaming rule and the TransactionScope.Suppress detail stood out.

In the 20-call race, row locks serialized the reservations, and the reserve check rejected all 15 excess calls with insufficient_credits. The balance CHECK fired only in a separate test that deliberately wrote a negative balance.

For settlement, I'd first measure row lock waits under concurrent captures across many accounts. If the settlement row turns out to be a bottleneck, I'd shard the settlement wallet by account ID, which keeps each capture a single atomic, balanced posting. I'd consider batching only if sharding falls short.