Database locking — sharedLock(), lockForUpdate()
Concept
Database locking controls concurrent access to rows within a transaction, preventing race conditions where two requests read and modify the same data simultaneously.
sharedLock(): SELECT ... LOCK IN SHARE MODE (MySQL). Other transactions can read the locked rows but cannot modify them until your transaction finishes. Use when you need to ensure data doesn't change while you process it, but don't plan to update it yourself.
lockForUpdate(): SELECT ... FOR UPDATE (MySQL). No other transaction can read (with sharedLock) or modify the locked rows until your transaction commits. Use when you're going to update the rows you've read — prevents lost updates.
Race condition example (without locking):
- Transaction A reads balance = 100.
- Transaction B reads balance = 100.
- Transaction A deducts 80, writes 20.
- Transaction B deducts 80, writes 20. ← Balance should be -60, but it's 20!
With lockForUpdate: Transaction B blocks on the SELECT FOR UPDATE until Transaction A commits. Then B reads the updated balance (20) and correctly writes -60.
Optimistic locking (alternative): Add a version column. Read the version, compute your update, then UPDATE ... WHERE version = [original_version]. If 0 rows affected, another transaction updated first — retry. No DB-level lock held. Better for low-contention scenarios.
useWritePdo(): Forces a specific query to run on the write connection (bypasses read replica) — useful when you need to read back data you just wrote.
Code Example
<?php
use Illuminate\Support\Facades\DB;
// lockForUpdate — prevent concurrent updates to the same row
DB::transaction(function() use ($userId, $amount) {
// Lock the account row so no other transaction can read/write it
$account = DB::table('accounts')
->where('user_id', $userId)
->lockForUpdate() // SELECT ... FOR UPDATE
->first();
if ($account->balance < $amount) {
throw new \DomainException('Insufficient balance');
}
DB::table('accounts')
->where('user_id', $userId)
->decrement('balance', $amount);
});
// Eloquent with lockForUpdate
DB::transaction(function() use ($productId, $qty) {
$product = Product::where('id', $productId)->lockForUpdate()->first();
if ($product->stock < $qty) {
throw new OutOfStockException();
}
$product->decrement('stock', $qty);
});
// sharedLock — read and hold, don't modify
DB::transaction(function() use ($userId) {
// Other transactions can read but NOT modify this user during our read
$user = User::where('id', $userId)->sharedLock()->first();
// ... verify user state, generate report ...
// No update here — sharedLock is sufficient
});
// Optimistic locking (no DB lock held)
$product = Product::find(1);
$originalVersion = $product->version;
// ... process order ...
$updated = DB::table('products')
->where('id', $product->id)
->where('version', $originalVersion) // check version hasn't changed
->update([
'stock' => $product->stock - $qty,
'version' => $originalVersion + 1,
]);
if ($updated === 0) {
throw new ConcurrentUpdateException('Please retry your order');
}