Two customers buy the last item at the same time and your inventory table goes negative. The read-then-write pattern in your checkout code is the cause, and no amount of application-level checking fixes it. This article shows how PostgreSQL row locking, Laravel's pessimistic locking helpers, and advisory locks actually behave, and which one fits a given write path.
Why does a read-then-write stock update oversell?
A read-then-write sequence is unsafe because the read and the write run in separate statements with no lock held between them. Both transactions read the same stock value, both decide the item is available, and both write a decrement. The second write silently overwrites the first, because the first transaction's read never reserved anything.
Consider the classic version:
// Unsafe under concurrency
$product = Product::find($id);
if ($product->stock >= $quantity) {
$product->decrement('stock', $quantity);
}Under PostgreSQL's default isolation level, Read Committed, each statement sees a snapshot taken at the start of that statement. Two transactions can therefore both pass the if check before either commits. The window is small, but on a flash sale it is hit constantly.
You can verify the failure yourself. Open two psql sessions against the same database, run BEGIN; in both, and in each session run SELECT stock FROM products WHERE id = 1;. Both return the same value. Now run the decrement in both and commit. The final stock is decremented once, not twice, even though two decrements were issued against the same starting value. That is the oversell.
What does SELECT FOR UPDATE actually lock?
SELECT ... FOR UPDATE locks the specific rows returned by the query, blocking other transactions from locking or updating those rows until the current transaction commits or rolls back. It does not lock the table, and it does not prevent plain SELECT statements from reading the rows.
The lock is held until the transaction ends, not until the statement ends. That distinction matters: if you acquire the lock and then make an HTTP call before committing, you hold a row lock for the duration of that call, and every other transaction touching that row queues behind you.
DB::transaction(function () use ($id, $quantity) {
$product = Product::where('id', $id)
->lockForUpdate()
->first();
if ($product->stock < $quantity) {
throw new OutOfStockException();
}
$product->decrement('stock', $quantity);
});The second transaction blocks on the lockForUpdate() call until the first commits, then reads the updated value and correctly rejects the order. Laravel's lockForUpdate() maps to FOR UPDATE; sharedLock() maps to FOR SHARE, which allows concurrent readers but blocks writers. See the Laravel query builder documentation for the supported variants, including lockForUpdate on joined queries.
Always lock inside a transaction. Outside one, PostgreSQL releases the lock as soon as the implicit single-statement transaction finishes, so lockForUpdate() on its own gives you nothing. Laravel's DB::transaction() wraps the callback correctly, but a bare Product::lockForUpdate()->first() in a controller does not.
Which locking strategy fits which write path?
Row locks are the right default for single-row invariants like stock, balances, or seat reservations. Advisory locks and atomic updates solve different problems. The table below maps the common cases.
| Approach | Blocks | Best for | Main risk |
|---|---|---|---|
Atomic UPDATE ... WHERE stock >= ? | Nothing extra | Simple decrements with no read logic | You cannot inspect the row before writing |
SELECT ... FOR UPDATE | Other lockers of the same rows | Read, validate, then write | Lock held across slow work (HTTP, queues) |
SELECT ... FOR SHARE | Writers of the same rows | Reads that must be stable for a decision | Deadlocks when multiple rows are locked out of order |
| Advisory lock | Anyone using the same lock key | Cross-row invariants, job deduplication | Cooperative only, nothing enforces it |
If your logic is genuinely just a decrement, skip the lock and let the database do the check:
$updated = DB::table('products')
->where('id', $id)
->where('stock', '>=', $quantity)
->update(['stock' => DB::raw('stock - ' . (int) $quantity)]);
if ($updated === 0) {
throw new OutOfStockException();
}The WHERE stock >= ? clause is evaluated against the current committed row version, and the update takes a row lock implicitly. This is the cheapest correct option when you do not need to read the row first. Note the cast on $quantity: string interpolation into DB::raw is an injection vector, so cast to integer or use a bound expression.
When should you use PostgreSQL advisory locks instead?
Advisory locks are right when the invariant spans rows or tables rather than a single row, for example ensuring only one nightly settlement job runs, or serializing writes to a logical resource that has no single owning row. They are cooperative: PostgreSQL grants them, but nothing forces your code to use them.
Laravel exposes them through the cache lock API, which uses pg_advisory_lock under the database cache driver:
$lock = Cache::lock('settlement:2026-09-29', 30);
if ($lock->get()) {
try {
// do the work
} finally {
$lock->release();
}
}Two caveats. First, the database cache driver's lock is released when the connection closes, so a crashed worker does not leave a permanent lock, but a long-running worker that never releases holds it indefinitely. Second, advisory locks do not participate in normal row visibility, so they will not appear in pg_locks in a way that maps obviously back to your domain rows. Inspect them with SELECT * FROM pg_locks WHERE locktype = 'advisory'; when debugging.
If you already run cache locks for other reasons, the caching trade-offs are covered in Laravel caching strategies that cut DB load, including how lock TTLs interact with retries.
How do you avoid deadlocks when locking multiple rows?
Deadlocks happen when two transactions acquire locks on the same set of rows in different orders. PostgreSQL detects the cycle and aborts one transaction with SQLSTATE 40P01. The fix is to impose a consistent acquisition order, usually by primary key ascending, before you lock anything.
$ids = collect($cartItemIds)->sort()->values();
DB::transaction(function () use ($ids) {
$products = Product::whereIn('id', $ids)
->orderBy('id')
->lockForUpdate()
->get();
// ...
});The orderBy('id') alone does not guarantee lock order, because the planner may reorder. Sorting the IDs and locking them in a deterministic query is the practical approach; if the planner still reorders, lock each row in a loop over the sorted IDs. A deadlock is not a bug to retry blindly: catching it and retrying the whole transaction is correct, but a transaction that deadlocks repeatedly signals an inconsistent lock order somewhere in the codebase.
What isolation level do you actually need?
Read Committed, PostgreSQL's default, is sufficient for the patterns above because explicit row locks provide the serialization you need. Repeatable Read and Serializable add snapshot consistency across the whole transaction, at the cost of more 40001 serialization failures that your application must retry.
Reach for Serializable only when you have a cross-row invariant that locks cannot express, and budget for retry logic. The isolation level is documented in the PostgreSQL transaction isolation documentation, including exactly which anomalies each level permits.
Run two concurrent transactions against a staging database and assert the final stock value. A single-threaded test will pass regardless of whether your PostgreSQL row locking is correct, which is why the bug survives to production.
Frequently asked questions
Does lockForUpdate work outside a transaction in Laravel?
No. Outside an explicit transaction, the lock is released when the implicit statement transaction ends, so it provides no protection. Wrap it in DB::transaction() or start a transaction with DB::beginTransaction() before acquiring the lock.
Is SELECT FOR UPDATE slower than an atomic UPDATE?
Yes, generally, because it acquires a lock and holds it for the rest of the transaction. The atomic UPDATE ... WHERE stock >= ? form takes the lock only for the duration of the write, so prefer it when you do not need to read the row first.
Do PostgreSQL advisory locks survive a connection drop?
Session-level advisory locks are released when the session ends, including on a dropped connection, which is usually the behavior you want for crash recovery. Transaction-level advisory locks, taken with pg_advisory_xact_lock, are released at commit or rollback instead.