Locking — row-level, table-level, deadlocks, and how to avoid them

Concept

Locking is how databases control concurrent access to data — ensuring transactions don't corrupt each other's work.

Shared lock (S-lock / Read lock): Multiple transactions can hold a shared lock on the same row simultaneously. A shared lock allows reading but prevents modifications by others. SELECT ... LOCK IN SHARE MODE (MySQL) / SELECT ... FOR SHARE (PostgreSQL).

Exclusive lock (X-lock / Write lock): Only one transaction can hold an exclusive lock on a row. All other readers and writers must wait. Set by SELECT ... FOR UPDATE, and implicitly by UPDATE/DELETE.

Row-level locking: Most common in modern databases (InnoDB, PostgreSQL). Locks only the specific rows touched by a transaction. High concurrency.

Table-level locking: Locks the entire table. LOCK TABLES. Rarely needed — used by MyISAM (old MySQL engine). Avoid with InnoDB.

Gap locks (InnoDB): Lock the "gap" between index records to prevent phantom inserts. Example: if you query WHERE id BETWEEN 10 AND 20, a gap lock prevents other transactions from inserting rows with id = 15 while your transaction is open. Specific to InnoDB at REPEATABLE READ.

Deadlock: Transaction A waits for B's lock, Transaction B waits for A's lock. Neither can proceed. The database detects this and kills one transaction (the "victim") with an error. The victim transaction must be retried. Deadlocks cannot be entirely prevented — design to minimize them:

• Always access resources in the same order.
• Keep transactions short.
• Use lower isolation levels where possible.

Optimistic locking: No database locks. Add a version column. On update: WHERE id = ? AND version = ?. If 0 rows affected → someone else updated first → retry. Laravel uses $model->version with ->lockForUpdate() equivalent via custom logic.

Code Example

-- Exclusive lock — prevents concurrent updates
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- X-lock on row id=1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock released

-- Shared lock — read-only, prevents writes by others
SELECT * FROM accounts WHERE id = 1 FOR SHARE;  -- or LOCK IN SHARE MODE (MySQL)

-- Deadlock scenario
-- Transaction A:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- (waits for row 2)

-- Transaction B (concurrent):
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- locks row 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- waits for row 1 (A has it)
-- A is waiting for B's row 2, B is waiting for A's row 1 → DEADLOCK
-- DB kills B with "Deadlock found" error

-- Prevention: always lock in consistent order
-- Both A and B should lock row 1 then row 2 (lowest id first)

<?php
// Laravel — lockForUpdate (exclusive lock)
DB::transaction(function () {
    $account = Account::where('id', 1)->lockForUpdate()->first();
    $account->decrement('balance', 100);
});

// sharedLock (shared lock)
$product = Product::where('id', 1)->sharedLock()->first();

// Optimistic locking in Laravel (no built-in — implement manually)
class Order extends Model
{
    protected function performUpdate(\Illuminate\Database\Eloquent\Builder $query): bool
    {
        $result = $query->where('version', $this->version)->update(
            array_merge($this->getDirty(), ['version' => $this->version + 1])
        );

        if ($result === 0) {
            throw new \RuntimeException('Optimistic lock conflict — order was modified by another process');
        }
        return true;
    }
}