Differences

This shows you the differences between two versions of the page.

--- mysql:tx:locking [2026/05/16 07:54] – [2 Write Lock (Exclusive Lock)] phong2018
+++ mysql:tx:locking [2026/05/16 08:09] (current) – [1 Read Lock (Shared Lock)] phong2018
@@ Line 4: / Line 4: @@
 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) ====
+==== 1. Read Lock (Shared Lock) ====
 Allows multiple transactions to read the same row at the same time,
@@ Line 101: / Line 105: @@
 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 ====
+==== 3. Lock Compatibility Matrix ====
 Shows whether two locks can be held simultaneously on the same row.
@@ Line 189: / Line 298: @@
 ----
-==== 6 Row Lock vs Table Lock ====
+==== 6. Row Lock vs Table Lock ====
 ^ Feature            ^ Row Lock (FOR UPDATE / FOR SHARE) ^ Table Lock ^