InnoDB Locking: Row Lock, Gap Lock, and Next-Key Lock

-- Setup: orders table with an index on status
CREATE TABLE orders (
  id     INT PRIMARY KEY,
  status VARCHAR(20),
  total  DECIMAL(10,2),
  INDEX  idx_status (status)
);
INSERT INTO orders VALUES (1,'PENDING',100),(5,'PENDING',200),(10,'DONE',50);

-- 1. ROW LOCK: locks the exact index record
--    Acquired by SELECT ... FOR UPDATE on a unique index hit
START TRANSACTION;
SELECT * FROM orders WHERE id = 5 FOR UPDATE;
-- Locks only the row with id=5. Other transactions can still
-- read/write id=1 or id=10 concurrently.

-- 2. GAP LOCK: locks the gap BETWEEN index values, prevents inserts
--    Acquired when searching a range on a non-unique index
START TRANSACTION;
SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE;
-- Locks the gap before the first PENDING row and after the last PENDING row.
-- Another transaction CANNOT insert a new PENDING order until this commits.
-- Gap locks are SHARED — two transactions can both hold gap locks on same gap.

-- 3. NEXT-KEY LOCK: row lock + gap lock before it (default InnoDB lock)
--    Used for range scans to prevent phantoms
START TRANSACTION;
SELECT * FROM orders WHERE id BETWEEN 3 AND 8 FOR UPDATE;
-- Acquires next-key lock on (id=5 row) + gap (1, 5] + gap (5, 10)
-- Prevents: insert of id=4, id=6, id=7, or id=8 by other transactions

-- DIAGNOSING DEADLOCKS in Spring Boot:
-- InnoDB rolls back the lighter transaction and throws:
-- com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException
-- (mapped to Spring's CannotAcquireLockException)

-- Check the latest deadlock details:
SHOW ENGINE INNODB STATUS;
-- Look for 'LATEST DETECTED DEADLOCK' section

-- Avoid deadlocks: always acquire locks in the same order across transactions
-- Use @Retryable in Spring to retry on CannotAcquireLockException