Anatomy of a Double Billing: Concurrency & SQL Transactions

In production, critical logical bugs can lie dormant for months before suddenly manifesting under heavy traffic. The concurrent double-debit issue is a classic example. Two identical payment requests arriving at the exact same millisecond can bypass application-level validation and drain a user's wallet. Here is the technical breakdown of this race condition.

The Disaster Scenario (Race Condition)

Imagine a user with a wallet balance of 10,000 FCFA trying to buy a service costing 8,000 FCFA. If they double-click the “Pay” button, two separate web servers (or process workers) handle these requests concurrently:

At the end of the execution, the user's final balance is committed at 2,000 FCFA, instead of rejecting the second transaction for insufficient funds (or ending up with a correct negative balance of -6,000 FCFA). The business has lost money, and the ledger is corrupted.

The Bad Fix: Application-Level Optimism

Trying to resolve this in memory (such as checking session states or using an unlocked Redis cache key) is highly prone to failures. Similarly, standard database transactions running under default isolation levels (like READ COMMITTED in PostgreSQL) do not block this race condition, because both processes read the validated database state before the other commits its write.

The Robust Fix: Pessimistic Locking

To secure financial transactions, we must force the database to serialize access to the specific row representing the user's wallet. This is done using the SELECT ... FOR UPDATE statement.

Here is the clean implementation in Laravel and PHP 8+ wrapped inside a database transaction block:

use Illuminate\Support\Facades\DB;
use App\Exceptions\InsufficientBalanceException;

DB::transaction(function () use ($userId, $amount) {
    // 1. Fetch user's wallet and lock the matching SQL row
    // This query blocks any concurrent SELECT ... FOR UPDATE on this record
    $wallet = DB::table('wallets')
        ->where('user_id', $userId)
        ->lockForUpdate()
        ->first();

    // 2. Perform strict balance check insulated from concurrent updates
    if ($wallet->balance < $amount) {
        throw new InsufficientBalanceException("Insufficient balance.");
    }

    // 3. Perform debit operation
    DB::table('wallets')
        ->where('user_id', $userId)
        ->decrement('balance', $amount);

    // 4. Log transaction ledger entry for auditing
    DB::table('ledger_entries')->insert([
        'user_id' => $userId,
        'amount' => -$amount,
        'type' => 'debit',
        'created_at' => now(),
    ]);
});

Under the Hood

When the first worker runs lockForUpdate() (which compiles to SELECT * FROM wallets WHERE user_id = ? FOR UPDATE), PostgreSQL acquires an exclusive write lock on that row.

If request B arrives a millisecond later and tries to lock the same row, the database suspends its execution (status: lock wait). Once worker 1 commits or rolls back, the lock is released. Request B immediately reads the freshly updated balance (2,000 FCFA), detects that 2,000 < 8,000, and safely raises an exception.

Production Rules of Thumb

• Set a lock timeout: Never let a web worker wait indefinitely for a database lock. Configure a short lock timeout (e.g., SET lock_timeout = '3s').
• Always index your search clause: If your lock query does not hit an index, the database may escalate to a Table Lock instead of a Row Lock, blocking all operations on that table and crippling your application.
• Database Constraints: Add a CHECK (balance >= 0) constraint at the database table level. If the application layer fails, the database will refuse the write and keep state intact.