In Laravel, a gift-card redemption uses DB::table('gift_cards')->where('id', $id)->lockForUpdate()->first() yet still double-spends a balance under load; what are the likely causes?

seniorshould knowfreq 38%

answer

lock lives until commit
autocommit ends it at once
SQLite grammar emits no lock
lock() switches to the write PDO
conditional decrement as alternative

basics

~10 s

lockForUpdate() only appends FOR UPDATE; without DB::transaction() around the read and the write the lock ends with the statement. On SQLite, the skeleton default, the grammar emits no lock clause at all.

solid answer

~40 s

lockForUpdate() sets the builder's lock so MySQL and PostgreSQL get select ... for update (and SQL Server a with(rowlock,updlock,holdlock) hint). A row lock lasts until the transaction ends, so outside DB::transaction() the autocommitted select releases it immediately and two requests can both read the old balance. Other causes: the SQLite grammar compiles the lock to an empty string, so on the skeleton's default connection nothing is locked; and the later update or decrement must run inside the same closure on the same connection. lock() also calls useWritePdo(), so with read replicas the locked read goes to the primary. Often the simpler fix is a conditional atomic update: where('balance_cents', '>=', $amount)->decrement('balance_cents', $amount), checking the affected-row count.

`lockForUpdate()` sets the builder's lock so MySQL and PostgreSQL get `select ... for update` (and SQL Server a `with(rowlock,updlock,holdlock)` hint). A row lock lasts until the **transaction** ends, so outside `DB::transaction()` the autocommitted `select` releases it immediately and two requests can both read the old balance. Other causes: the SQLite grammar compiles the lock to an **empty string**, so on the skeleton's default connection nothing is locked; and the later `update` or `decrement` must run inside the same closure on the same connection. `lock()` also calls `useWritePdo()`, so with read replicas the locked read goes to the primary. Often the simpler fix is a conditional atomic update: `where('balance_cents', '>=', $amount)->decrement('balance_cents', $amount)`, checking the affected-row count.

code

php · 15 lines

php

<?php

use Illuminate\Support\Facades\DB;

// Broken: autocommit releases the lock right after the select
$card = DB::table('gift_cards')->where('id', $id)->lockForUpdate()->first();
if ($card->balance_cents >= $amount) {
    DB::table('gift_cards')->where('id', $id)->decrement('balance_cents', $amount);
}

// Lock-free alternative: one conditional statement
$ok = DB::table('gift_cards')
    ->where('id', $id)
    ->where('balance_cents', '>=', $amount)
    ->decrement('balance_cents', $amount) === 1;

go deeper

for a junior

Recall that lockForUpdate() adds FOR UPDATE to a select and only protects anything inside a transaction.

for a middle

Explain how each grammar compiles the lock, why SQLite emits nothing, and why the read and the write must share one transaction.

for a senior

Diagnose a double-spend: missing transaction, write outside the closure, SQLite in tests, and choose between a lock and a conditional update.

for a principal

Decide where the team relies on row locks versus single-statement atomic updates, and how to test concurrency on the production database engine.

What `lockForUpdate()` does in the builder

lockForUpdate() is a thin method on Laravel's query builder: it calls lock(true), which stores the lock flag and calls useWritePdo(). Nothing else happens until the query compiles, when each grammar decides what SQL the flag becomes:

Driver	`lockForUpdate()`	`sharedLock()`
MySQL / MariaDB	`for update`	`lock in share mode`
PostgreSQL	`for update`	`for share`
SQL Server	table hint `with(rowlock,updlock,holdlock)`	`with(rowlock,holdlock)`
SQLite	nothing	nothing

You can also pass a string to lock() to emit a custom clause on drivers that accept one, such as lock('for update skip locked') on PostgreSQL or MySQL 8.

Why the balance still double-spends

A row lock taken by select ... for update belongs to the current transaction and is held until it commits or rolls back. Several things commonly defeat it:

No transaction. Without DB::transaction(), each statement autocommits. The locking select commits as soon as it returns, the lock is gone, and two requests can read the same balance before either writes. The docs call wrapping locks in a transaction recommended rather than obligatory, but for a read-then-write it is what makes the lock mean anything.
The write happens elsewhere. The update or decrement must run inside the same transaction closure. If it runs after the closure returns, or on another connection via DB::connection('other'), the lock no longer protects it.
SQLite. A fresh Laravel 13 skeleton uses DB_CONNECTION=sqlite, and the SQLite grammar compiles the lock to an empty string. Tests and local runs pass without any locking; behaviour changes only on MySQL or PostgreSQL.
Reading before locking. Code that first loads the card without a lock, checks the balance, and only then locks, still decides on a stale value.

The corrected flow

php

use Illuminate\Support\Facades\DB;

DB::transaction(function () use ($id, $amount) {
    $card = DB::table('gift_cards')
        ->where('id', $id)
        ->lockForUpdate()
        ->first();

    if ($card === null || $card->balance_cents < $amount) {
        throw new InsufficientBalance();
    }

    DB::table('gift_cards')->where('id', $id)
        ->decrement('balance_cents', $amount);
});

Here the check and the write happen while the row is locked, and the exception rolls the transaction back and releases the lock.

Often you do not need the lock

When the rule fits in one statement, an atomic conditional update avoids holding a lock across PHP code at all:

php

$updated = DB::table('gift_cards')
    ->where('id', $id)
    ->where('balance_cents', '>=', $amount)
    ->decrement('balance_cents', $amount);
// $updated === 0 means insufficient balance or no such card

decrement() returns the number of affected rows, so a zero tells you the condition failed. Keep lockForUpdate() for flows that must read several values, call other code, and then write.

Operational notes

sharedLock() lets other transactions read the rows but not modify them; use it when you only need the values to stay put while you read related data.
Replicas: because lock() switches to the write PDO, a locked read goes to the primary even on a connection configured with read hosts.
Lock order: when one transaction locks several rows, lock them in a consistent order, for example by orderBy('id'), to reduce deadlocks.
Short transactions: do not call HTTP APIs or send mail while holding the lock; every waiting request queues behind it.

A review checklist

Is the locking select inside DB::transaction() together with the write that depends on it?
Does every query in that closure use the same connection?
Is the decision made on the locked read, not on a value loaded earlier?
Does the environment where the race was reported run MySQL, MariaDB, PostgreSQL or SQL Server, rather than SQLite?
Could a single conditional update or decrement replace the whole read-check-write sequence?

## What `lockForUpdate()` does in the builder `lockForUpdate()` is a thin method on Laravel's query builder: it calls `lock(true)`, which stores the lock flag and calls `useWritePdo()`. Nothing else happens until the query compiles, when each **grammar** decides what SQL the flag becomes: | Driver | `lockForUpdate()` | `sharedLock()` | |---|---|---| | MySQL / MariaDB | `for update` | `lock in share mode` | | PostgreSQL | `for update` | `for share` | | SQL Server | table hint `with(rowlock,updlock,holdlock)` | `with(rowlock,holdlock)` | | SQLite | **nothing** | **nothing** | You can also pass a string to `lock()` to emit a custom clause on drivers that accept one, such as `lock('for update skip locked')` on PostgreSQL or MySQL 8. ## Why the balance still double-spends A row lock taken by `select ... for update` belongs to the current **transaction** and is held until it commits or rolls back. Several things commonly defeat it: 1. **No transaction.** Without `DB::transaction()`, each statement autocommits. The locking `select` commits as soon as it returns, the lock is gone, and two requests can read the same balance before either writes. The docs call wrapping locks in a transaction recommended rather than obligatory, but for a read-then-write it is what makes the lock mean anything. 2. **The write happens elsewhere.** The `update` or `decrement` must run inside the same transaction closure. If it runs after the closure returns, or on another connection via `DB::connection('other')`, the lock no longer protects it. 3. **SQLite.** A fresh Laravel 13 skeleton uses `DB_CONNECTION=sqlite`, and the SQLite grammar compiles the lock to an empty string. Tests and local runs pass without any locking; behaviour changes only on MySQL or PostgreSQL. 4. **Reading before locking.** Code that first loads the card without a lock, checks the balance, and only then locks, still decides on a stale value. ## The corrected flow ```php use Illuminate\Support\Facades\DB; DB::transaction(function () use ($id, $amount) { $card = DB::table('gift_cards') ->where('id', $id) ->lockForUpdate() ->first(); if ($card === null || $card->balance_cents < $amount) { throw new InsufficientBalance(); } DB::table('gift_cards')->where('id', $id) ->decrement('balance_cents', $amount); }); ``` Here the check and the write happen while the row is locked, and the exception rolls the transaction back and releases the lock. ## Often you do not need the lock When the rule fits in one statement, an **atomic conditional update** avoids holding a lock across PHP code at all: ```php $updated = DB::table('gift_cards') ->where('id', $id) ->where('balance_cents', '>=', $amount) ->decrement('balance_cents', $amount); // $updated === 0 means insufficient balance or no such card ``` `decrement()` returns the number of affected rows, so a zero tells you the condition failed. Keep `lockForUpdate()` for flows that must read several values, call other code, and then write. ## Operational notes - **`sharedLock()`** lets other transactions read the rows but not modify them; use it when you only need the values to stay put while you read related data. - **Replicas:** because `lock()` switches to the write PDO, a locked read goes to the primary even on a connection configured with read hosts. - **Lock order:** when one transaction locks several rows, lock them in a consistent order, for example by `orderBy('id')`, to reduce deadlocks. - **Short transactions:** do not call HTTP APIs or send mail while holding the lock; every waiting request queues behind it. ## A review checklist 1. Is the locking `select` inside `DB::transaction()` together with the write that depends on it? 2. Does every query in that closure use the same connection? 3. Is the decision made on the locked read, not on a value loaded earlier? 4. Does the environment where the race was reported run MySQL, MariaDB, PostgreSQL or SQL Server, rather than SQLite? 5. Could a single conditional `update` or `decrement` replace the whole read-check-write sequence?

Why can a feature test with lockForUpdate() pass locally but the race still happen in production?
The skeleton and a typical test setup use SQLite, whose grammar compiles the lock to nothing, and a test runs requests one after another rather than concurrently. Nothing exercises the lock. Reproduce against MySQL or PostgreSQL with two concurrent connections, or prove the logic with a conditional update whose affected-row count you assert.
When would you choose sharedLock() instead of lockForUpdate() in Laravel?
When the transaction only needs rows to stay unchanged while it reads related data, not to change them. `sharedLock()` lets other transactions take shared locks and read, but blocks writers until you commit. For read-then-write on the same row, `lockForUpdate()` is the right one.

A lockForUpdate outside a transaction is like signing out a meeting room for zero minutes: the booking is recorded and released in the same instant, so the next person walks straight in.

saying these in an interview costs you the question

Believes lockForUpdate() opens a transaction by itself
Thinks the lock lasts until the PHP request ends
Assumes SQLite honours lockForUpdate() like MySQL
Performs the decrement after the transaction closure returns
Holds the lock while calling an external payment API

In Laravel, a gift-card redemption uses DB::table('gift_cards')->where('id', $id)->lockForUpdate()->first() yet still double-spends a balance under load; what are the likely causes?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

What lockForUpdate() does in the builder

Why the balance still double-spends

The corrected flow

Often you do not need the lock

Operational notes

A review checklist

▾common follow-ups2 questions

▾analogy~10 s

▾red flags5 flags

What lockForUpdate() does in the builder

Why the balance still double-spends

The corrected flow

Often you do not need the lock

Operational notes

A review checklist

answer

go deeper

What `lockForUpdate()` does in the builder

What `lockForUpdate()` does in the builder