Wiki.Quizz.vn

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== Locks in MySQL =====

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

----

==== 1. Read Lock (Shared Lock) ====

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

<code sql>
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
</code>

**Behavior:**

^ Who                  ^ Read ^ Write ^
| Same transaction     | ✅ Yes | ❌ No |
| Other transactions   | ✅ Yes | ❌ Blocked |

**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.

<code sql>
-- 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;
</code>

**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.

----

==== 2. Write Lock (Exclusive Lock) ====

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

<code sql>
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
</code>

**Behavior:**

^ Who                              ^ Read        ^ Write     ^
| Same transaction                 | ✅ Yes       | ✅ Yes    |
| Other — plain SELECT             | ✅ Yes       | —         |
| Other — SELECT FOR SHARE         | ❌ Blocked   | ❌ Blocked |
| Other — SELECT FOR UPDATE        | ❌ Blocked   | ❌ Blocked |
| Other — UPDATE / DELETE          | ❌ Blocked   | ❌ Blocked |

**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.

<code sql>
-- 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
</code>

**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:**
<code sql>
-- 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;
</code>

==== Options after FOR UPDATE ====

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

<code sql>
-- 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;
</code>

----

=== NOWAIT ===

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.

<code sql>
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;
</code>

**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.

----

=== SKIP LOCKED ===

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.

<code sql>
-- 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;
</code>

**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".

----

=== Comparison ===

^ Syntax                    ^ Row is free        ^ Row is locked              ^
| ''FOR UPDATE''            | Lock and proceed   | Wait until released        |
| ''FOR UPDATE NOWAIT''     | Lock and proceed   | Throw error immediately    |
| ''FOR UPDATE SKIP LOCKED''| Lock and proceed   | Skip row, return next free |

----

=== Laravel Syntax ===

<code php>
// 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();
</code>

----

> **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.

----

==== 3. Lock Compatibility Matrix ====

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

^ Held ↓ / Requested →  ^ FOR SHARE ^ FOR UPDATE ^
| FOR SHARE              | ✅ Compatible | ❌ Conflict |
| FOR UPDATE             | ❌ Conflict   | ❌ Conflict |

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

----

==== 4. Deadlock ====

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.

<code>
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
</code>

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

<code sql>
-- 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;
</code>

----

==== 5. Table Locks ====

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.

<code sql>
LOCK TABLES accounts READ;
LOCK TABLES accounts WRITE;
UNLOCK TABLES;
</code>

**Behavior:**

^ Lock Type ^ Same Session       ^ Other Sessions ^
| READ      | Read only          | Read only (writes blocked) |
| WRITE     | Read + Write       | Fully blocked |

**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.

<code sql>
-- 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;
</code>

----

==== 6. Row Lock vs Table Lock ====

^ Feature            ^ Row Lock (FOR UPDATE / FOR SHARE) ^ Table Lock ^
| Granularity        | Single row                        | Entire table |
| Concurrency        | High — only affected rows locked  | Low — all rows blocked |
| Deadlock risk      | Yes                               | No |
| Supported engines  | InnoDB                            | InnoDB, MyISAM |
| Typical use        | OLTP, web applications            | Bulk operations, MyISAM |

----

==== 7. Summary ====

^ Lock             ^ SQL                       ^ Blocks writes ^ Blocks FOR SHARE ^ Blocks plain SELECT ^
| Shared (Read)    | ''SELECT ... FOR SHARE''  | ✅ Yes        | ❌ No            | ❌ No |
| Exclusive (Write)| ''SELECT ... FOR UPDATE'' | ✅ Yes        | ✅ Yes           | ❌ No |
| Table Read       | ''LOCK TABLES t READ''    | ✅ Yes        | —                | ❌ No |
| Table Write      | ''LOCK TABLES t WRITE''   | ✅ Yes