turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

LDG-001

Move balance between two accounts without losing any

Lock both rows in a fixed order, check inside the transaction, and write a ledger entry for each side. Skipping any of the three is how balances drift.

Two common bugs live in transfer code. The first is checking the sender's balance before the transaction starts, so two concurrent transfers both pass a check that only one should. The second is locking rows in whatever order the request happened to name them, which deadlocks the moment two users pay each other at the same time.

Locking in a fixed order — lowest id first — removes the deadlock. Re-reading inside the transaction removes the overdraft. The ledger rows are what let you prove afterwards that the total never moved.

PHP 
function transfer(PDO $pdo, int $fromId, int $toId, int $units, string $memo = ''): bool
{
    if ($fromId === $toId || $units <= 0) {
        return false;
    }

    // Always lock in a fixed order or two crossing transfers will deadlock.
    $first  = min($fromId, $toId);
    $second = max($fromId, $toId);

    $pdo->beginTransaction();
    try {
        $lock = $pdo->prepare('SELECT id, balance FROM users WHERE id = ? FOR UPDATE');
        $lock->execute([$first]);
        $lock->fetch();
        $lock->execute([$second]);
        $lock->fetch();

        $st = $pdo->prepare('SELECT balance FROM users WHERE id = ?');
        $st->execute([$fromId]);
        $balance = (int) $st->fetchColumn();

        if ($balance < $units) {
            $pdo->rollBack();
            return false;
        }

        $pdo->prepare('UPDATE users SET balance = balance - ? WHERE id = ?')->execute([$units, $fromId]);
        $pdo->prepare('UPDATE users SET balance = balance + ? WHERE id = ?')->execute([$units, $toId]);

        $led = $pdo->prepare('INSERT INTO ledger (user_id, kind, units, memo, created_at) VALUES (?, ?, ?, ?, NOW())');
        $led->execute([$fromId, 'transfer_out', -$units, $memo]);
        $led->execute([$toId,   'transfer_in',   $units, $memo]);

        $pdo->commit();
        return true;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Using it

Run a reconciliation query on a schedule: the sum of every ledger row for a user should equal that user's balance. The day it does not, you have found a write path that skipped the ledger.

Store units as signed integers and let the sign carry direction. Separate debit and credit columns invite one of them being forgotten.

What bites people

Balances must never be floats or decimals in this code path. Use the smallest unit and integers.

A rolled-back transaction still needs its lock released, which happens automatically — but only if you actually call rollBack on every failure branch, including the early return above.

This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.

Also in Money and Ledger