Wiki.Quizz.vn

Locks control concurrent access to data during transactions. Without locks, multiple transactions reading and writing the same rows simultaneously can cause dirty reads, lost updates, and write skew.

Read lock (Shared) - FOR SHARE → I am reading, others can read too, but no one can write

Write lock (Exclusive) - FOR UPDATE → I am about to update, no one else can touch this row

Allows multiple transactions to read the same row at the same time, but blocks anyone from writing until all read locks are released.

SELECT * FROM accounts WHERE id = 1 FOR SHARE;

Behavior:

When to use:

• You need to read a row and ensure it is not modified while your transaction is still running.
• Example: generating a financial report — lock the rows being read so no one updates them mid-report.

-- Audit report: lock all accounts in the department
BEGIN;
 
  SELECT id, balance
  FROM accounts
  WHERE department = 'engineering'
  FOR SHARE;
  -- Other transactions can still read these rows
  -- but cannot UPDATE or DELETE them until COMMIT
 
COMMIT;

Key point: Multiple transactions can hold a shared lock on the same row simultaneously. As soon as any transaction wants to write, it must wait for all shared locks to be released.

Blocks other transactions from both reading-with-lock and writing. Plain SELECT (without FOR UPDATE or FOR SHARE) is not blocked.

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

Behavior:

When to use:

• You need to read a row and then write to it in the same transaction.
• Example: check balance before deducting — lock the row on read so no one changes it before your write.

-- Debit: lock row, check balance, then deduct
BEGIN;
 
  SELECT balance
  FROM accounts
  WHERE id = 1
  FOR UPDATE;
  -- Row is now exclusively locked
  -- Other FOR UPDATE / FOR SHARE / UPDATE must wait
 
  -- Check balance in application code
  -- If sufficient, proceed:
  UPDATE accounts
  SET balance = balance - 500
  WHERE id = 1;
 
COMMIT;
-- Lock is released here

Important: Only one transaction can hold an exclusive lock on a row at a time. All other write-intent transactions are queued and run one after another.

Plain SELECT is never blocked:

-- While Transaction A holds FOR UPDATE on id=1:
 
-- Transaction B — plain SELECT → goes through immediately
SELECT balance FROM accounts WHERE id = 1;
 
-- Transaction B — FOR UPDATE → blocked, must wait for A
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

By default, FOR UPDATE blocks and waits if a row is already locked. MySQL provides two modifiers to change this behavior.

-- 1. Default — wait until lock is released (blocking)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
 
-- 2. NOWAIT — if row is locked, throw error immediately, do not wait
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
 
-- 3. SKIP LOCKED — if row is locked, skip it and return next free row
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;

Use when you want to try locking but fail fast if someone else already holds the lock. The application catches the error and responds immediately — no waiting.

BEGIN;
 
  SELECT balance FROM accounts WHERE id = 1
  FOR UPDATE NOWAIT;
  -- If row is already locked by another transaction:
  -- ERROR 3572: Statement aborted because lock(s) could not be acquired
  -- Error thrown immediately — app catches and shows "please try again"
 
COMMIT;

When to use:

• APIs where waiting is unacceptable — fail fast is better than a slow timeout.
• Optimistic workflows where conflict is rare — if locked, just retry.
• Showing a user-friendly message instead of hanging the request.

Use when you want to skip locked rows and move on to the next available one. Perfect for job queues — multiple workers process rows in parallel without stepping on each other.

-- Worker picks the next available pending job
-- Skips any rows already being processed by other workers
BEGIN;
 
  SELECT *
  FROM jobs
  WHERE STATUS = 'pending'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
  -- If top row is locked by Worker 2 → skipped automatically
  -- Returns the next unlocked row immediately
 
COMMIT;

When to use:

• Job queues — multiple workers run in parallel, each picks a different row.
• Task processing systems — avoid two workers picking the same task.
• Any scenario where “next available” is more important than “exact row”.

// Default — wait for lock
Account::lockForUpdate()->find(1);
 
// NOWAIT — throw error if locked
Account::lockForUpdate()->noWait()->find(1);
 
// SKIP LOCKED — skip locked rows, return next free row
Job::where('status', 'pending')
   ->orderBy('id')
   ->lockForUpdate()
   ->skipLocked()
   ->first();

Note: SKIP LOCKED is the closest behavior to a plain SELECT —
instead of blocking, it moves on to the next unlocked row.
However it still acquires a lock on the row it returns —
it is not a lock-free read.

Shows whether two locks can be held simultaneously on the same row.

• ✅ Compatible — both transactions proceed at the same time.
• ❌ Conflict — the requesting transaction is blocked until the held lock is released.

Deadlock occurs when two transactions each hold a lock the other needs, causing both to wait forever. MySQL detects this automatically and kills one transaction, returning an error.

Transaction A: locks row id=1, then needs row id=2
Transaction B: locks row id=2, then needs row id=1

Both wait for each other → deadlock

Prevention rule: always lock rows in a consistent order (ascending id).

-- RISK: inconsistent order
-- Transaction A locks id=1 then id=2
-- Transaction B locks id=2 then id=1
-- → deadlock possible
 
-- SAFE: always lock in ascending id order
BEGIN;
  SELECT id, balance
  FROM accounts
  WHERE id IN (1, 2)
  ORDER BY id          -- id=1 first, id=2 second — always
  FOR UPDATE;
 
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Lock the entire table instead of individual rows. Rarely used in modern applications — row locks are preferred because table locks block all concurrent access to the whole table.

LOCK TABLES accounts READ;
LOCK TABLES accounts WRITE;
UNLOCK TABLES;

Behavior:

When to use:

• Bulk imports or exports where you need the table to be completely stable.
• MyISAM engine (does not support row-level locks).
• InnoDB always prefers row-level locks — avoid table locks with InnoDB.

-- Example: bulk export, ensure no writes during read
LOCK TABLES accounts READ;
 
  SELECT * FROM accounts;
  -- No other session can INSERT, UPDATE, or DELETE
  -- Other sessions can still SELECT
 
UNLOCK TABLES;