Transactions and Locks

Transactions, Pessimistic Locking, and Query Debugging

DB::transaction(fn () => ...) commits when the closure returns, rolls back and rethrows when it throws, and with attempts: 3 retries after a deadlock (Deadlocks). DB::beginTransaction(), commit() and rollBack() work by hand; nesting uses savepoints. lockForUpdate() appends for update and sharedLock() lock in share mode (Row and Gap Locks):

Appended to routes/console.php: reserving stock under a row lockPHP

Artisan::command('stock:reserve {sku} {qty} {--hold=0}', function (string $sku, int $qty) {
    $t = microtime(true);
    $log = fn ($msg) => $this->line(sprintf('%.2f s  %s', microtime(true) - $t, $msg));
    try {
        DB::transaction(function () use ($sku, $qty, $log) {
            $p = DB::table('products')->where('sku', $sku)->lockForUpdate()->first();
            $log("locked, stock is {$p->stock}");
            throw_if($p->stock < $qty, RuntimeException::class, "only {$p->stock} left");
            sleep((int) $this->option('hold'));
            DB::table('products')->where('id', $p->id)->decrement('stock', $qty);
        }, attempts: 3);
        $log('committed');
    } catch (RuntimeException $e) {
        $log('rolled back: '.$e->getMessage());
    }
});

Output

A: 0.01 s  locked, stock is 7
A: 2.03 s  committed
B: 1.51 s  locked, stock is 2
B: 1.51 s  rolled back: only 2 left

Run A was stock:reserve BK-UX-01 5 --hold=2; B, without --hold, started half a second later, waited on A's lock, then read the committed 2 and refused. Without lockForUpdate(), B also read 7, and only the UNSIGNED column stopped it (error 1690, out of range). Keep locked transactions short.

To see what ran, use toRawSql() or dumpRawSql(), DB::listen() (its event carries time in milliseconds), DB::enableQueryLog() with getQueryLog(), and DB::whenQueryingForLongerThan(500, ...) for slow requests; then EXPLAIN the culprit (Reading EXPLAIN Output), or let Telescope 5,228 (Laravel Telescope) collect everything.

MENU

Transactions and Locks