Concurrent credit wallets: reserve, settle and reconcile

Managing concurrent credit state

In AI platforms and SaaS environments, metering API usage requires precise management of account balances under highly concurrent conditions. When systems like WarHound AI charge credits based on dynamic token consumption, such as billing two times the actual provider tokens including reasoning tokens, the exact cost of an operation is unknown until the task completes. Applications cannot simply decrement a balance post-execution, as simultaneous requests could easily exceed the user's total allocation.

Consider a WarHound AI Mini tier user with their 1 dollar of expiring monthly credits. If two isolated processes concurrently read the balance, both will see 1 dollar available. If both processes initiate tasks that each consume 0.80 dollars, the resulting sequence of naive read-modify-write operations will overwrite one another or leave the account at negative 0.60 dollars. To prevent overdrafts and ensure exact accounting, the database must enforce sequential access to the shared wallet record using explicit concurrency controls.

The reserve, settle, and reconcile pattern

To handle asynchronous operations with unpredictable costs, engineers employ a three-phase pattern: reserve, settle, and reconcile. During the reserve phase, the application calculates a maximum potential cost, known as a hard execution budget. It atomically deducts this maximum amount from the available balance and adds it to a pending hold balance. If the available balance is insufficient for the maximum budget, the request is denied immediately.

The settle phase occurs after the asynchronous workload, such as a model inference task, completes. The system receives the final exact cost, deducts this cost from the pending hold balance, and adds it to a consumed balance ledger. Any unused funds from the reserved budget are returned to the available balance. Finally, the reconcile phase is an asynchronous background process that periodically scans the ledger. It ensures that the sum of the available balance, pending holds, and consumed balance precisely matches the total allocated credits, flagging any mathematical discrepancies caused by partial system failures.

PostgreSQL row-level locking

Enforcing the reserve phase requires database-level guarantees. PostgreSQL provides explicit row-level locks to control concurrent access. Using the SELECT FOR UPDATE statement causes the retrieved rows to be locked as though for an update. According to PostgreSQL documentation, this prevents the rows from being modified or deleted by other transactions until the current transaction ends.

When a transaction executes SELECT FOR UPDATE on a wallet row, other transactions attempting to execute UPDATE, DELETE, or their own SELECT FOR UPDATE on that same row will be blocked. They wait in a queue until the first transaction either commits or rolls back. Notably, row-level locks do not affect standard data querying; a simple SELECT statement without locking clauses will still read the currently committed version of the row, adhering to Multiversion Concurrency Control principles. PostgreSQL implements row locks by modifying the selected rows on disk to mark them as locked, which incurs a disk write penalty.

Concrete worked example: wallet reservation

Imagine a synthetic fixture where a WarHound AI Plus user with 5 dollars of expiring monthly credits initiates a high execution budget task through the authenticated MCP endpoint at /api/mcp. The system must reserve a 2 dollar budget.

The application opens a database transaction and executes a query to fetch and lock the wallet: SELECT available_balance, pending_balance FROM wallets WHERE user_id = 99 FOR UPDATE. The database returns an available balance of 5 dollars and a pending balance of 0 dollars. The application logic verifies that 5 is greater than or equal to 2. It then issues an update within the same transaction: UPDATE wallets SET available_balance = 3, pending_balance = 2 WHERE user_id = 99. The transaction commits.

If a second request arrives milliseconds after the first request acquires the lock but before it commits, the second SELECT FOR UPDATE query hangs. Once the first transaction commits, the second query unblocks and reads the new state: 3 dollars available and 2 dollars pending. If the second request requires a 4 dollar budget, the application logic determines the funds are insufficient, rolls back the transaction, and returns an HTTP 402 Payment Required response.

Deadlocks and lock ordering

The use of explicit locking inherently increases the likelihood of deadlocks. A deadlock occurs when two or more transactions each hold locks that the other requires to proceed. PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the involved transactions, allowing the others to complete. The exact transaction chosen for termination is unpredictable.

The primary defense against deadlocks is acquiring locks on multiple objects in a strictly consistent, globally defined order. If an application needs to lock multiple wallets, for example transferring credits between two accounts, it should always lock the row with the lower primary key ID first. Furthermore, transactions holding locks must be kept as short as possible. Applications should never hold a database transaction open while waiting for external network requests, user input, or long-running AI model inference to complete. The reserve phase must commit before the external task begins.

Implementation tradeoffs and advisory locks

Row-level locking is robust but modifies table pages, causing disk I/O and potential table bloat. An alternative approach utilizes PostgreSQL advisory locks. Advisory locks have application-defined meanings and are not enforced by the database engine itself; the application must consistently request them. They can be acquired at the session level or the transaction level. Because they are stored in a shared memory pool rather than on disk, they are faster and avoid table bloat.

However, advisory locks introduce severe failure modes if misused. The shared memory pool size is limited by the max_locks_per_transaction and max_connections configuration variables. Exhausting this memory prevents the server from granting any locks. Additionally, when using advisory locks in queries with explicit ordering and LIMIT clauses, the LIMIT is not guaranteed to be applied before the locking function executes. A query like SELECT pg_advisory_lock(id) FROM wallets WHERE id > 10 LIMIT 1 might acquire locks on multiple rows before filtering, resulting in dangling locks that persist until the session ends.

Safe test matrix and expected outcomes

To verify wallet concurrency controls, engineers must execute a matrix of simultaneous operations against a known synthetic state.

First, transaction A acquires a FOR UPDATE lock on user 100. Concurrently, transaction B attempts to acquire a FOR UPDATE lock on user 100. Expected outcome: Transaction B is blocked and waits. Second, transaction A updates the balance and commits. Expected outcome: Transaction B unblocks, reads the updated balance, and proceeds.

Third, transaction A acquires a FOR UPDATE lock on user 100. Concurrently, transaction C executes a standard SELECT balance without a lock clause. Expected outcome: Transaction C returns immediately with the pre-update, currently committed balance. Finally, transaction A locks user 100, then transaction D locks user 200. Transaction A attempts to lock user 200, blocking. Transaction D attempts to lock user 100, triggering a deadlock. Expected outcome: PostgreSQL detects the deadlock and aborts either transaction A or D, throwing a deadlock_detected exception.

Failure modes and evidence limits

Engineers must recognize the explicit limits of this architecture. A successful database lock and reserve operation does not guarantee that downstream API calls, such as WarHound's external provider inference, will succeed. If the provider API times out or the application container crashes before the settle phase, the credits remain trapped in the pending balance indefinitely. The system must include a background reconciliation worker that sweeps for stale pending reservations exceeding a defined timeout and restores them to the available balance.

Furthermore, transaction aborts release all held locks immediately. If the application encounters an unhandled exception during the reserve phase, the transaction rolls back, safely discarding the reservation attempt. However, if the application catches an error but fails to explicitly roll back the transaction before returning to a connection pool, the lock might persist until the idle transaction timeout is reached, artificially freezing the user's wallet.

Sources and verification

The locking mechanisms and conflict matrices detailed in this document are derived from the official PostgreSQL explicit locking documentation.

Drafted with Gemini and checked for source links, advisory identifiers, structure and unsupported product claims. Examples are synthetic and are not scan results.

Drafted with Gemini and checked for source links, advisory identifiers, structure and unsupported product claims. Examples are synthetic and are not scan results.

• PostgreSQL 18 Documentation: Explicit Locking