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;

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;