-- Acquire a shared lock: other sessions can read but not write
SELECT * FROM orders WHERE order_id = 100 LOCK IN SHARE MODE;

-- Acquire an exclusive lock: other sessions cannot read or write
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;

-- MySQL 8.0+ syntax (preferred)
SELECT * FROM orders WHERE order_id = 100 FOR SHARE;
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;

-- Record lock: locks only the row where order_id = 100
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;

-- Next-key lock: locks the record AND the gap before it
-- Prevents another transaction from inserting order_id between 90 and 100
SELECT * FROM orders WHERE order_id > 90 AND order_id <= 100 FOR UPDATE;

-- Gap lock: if no row exists at that value, only the gap is locked
SELECT * FROM orders WHERE order_id = 95 FOR UPDATE;
-- If order_id 95 doesn't exist, locks the gap (90, 100) to prevent insert

-- Lock a table for reading (shared lock)
LOCK TABLES orders READ;
SELECT * FROM orders;       -- Allowed
-- INSERT INTO orders ...   -- Blocked for all sessions including this one
UNLOCK TABLES;

-- Lock a table for writing (exclusive lock)
LOCK TABLES orders WRITE;
SELECT * FROM orders;       -- Allowed (this session only)
UPDATE orders SET ...;      -- Allowed (this session only)
-- Other sessions: ALL reads and writes are blocked
UNLOCK TABLES;

-- Lock multiple tables (must lock all tables you'll use)
LOCK TABLES orders WRITE, customers READ;
-- ... operations ...
UNLOCK TABLES;

-- Safely update account balance without a lost-update race
START TRANSACTION;

-- Acquire exclusive lock on this row
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Other transactions trying to UPDATE account_id=1 will WAIT here

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;  -- Lock released

-- MySQL 8.0 SKIP LOCKED and NOWAIT options
-- Process the next available job without waiting for locked rows
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- Skip rows locked by other sessions

-- Fail immediately instead of waiting
SELECT * FROM accounts WHERE account_id = 1
FOR UPDATE NOWAIT;  -- Raises error if row is locked

-- Classic deadlock scenario
-- Session A:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;  -- locks row 1
-- (pauses)

-- Session B:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 2;  -- locks row 2
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;  -- WAITS for Session A

-- Session A continues:
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;  -- WAITS for Session B
-- InnoDB detects the deadlock!
-- One session gets: ERROR 1213: Deadlock found when trying to get lock

-- Show all currently waiting lock requests (MySQL 8.0+)
SELECT
  r.trx_id                 AS waiting_trx_id,
  r.trx_mysql_thread_id    AS waiting_thread,
  r.trx_query              AS waiting_query,
  b.trx_id                 AS blocking_trx_id,
  b.trx_mysql_thread_id    AS blocking_thread,
  b.trx_query              AS blocking_query
FROM       information_schema.INNODB_TRX  r
JOIN       information_schema.INNODB_TRX  b
  ON  r.trx_wait_started IS NOT NULL
  AND b.trx_id = (
    SELECT blocking_trx_id
    FROM performance_schema.data_lock_waits
    WHERE requesting_engine_transaction_id = r.trx_id
    LIMIT 1
  );

-- Show all current data locks
SELECT * FROM performance_schema.data_locksG

-- Show lock waits
SELECT * FROM performance_schema.data_lock_waitsG

SHOW ENGINE INNODB STATUSG

-- Key sections to look for in the output:
-- TRANSACTIONS      — active transactions and their state
-- LATEST DETECTED DEADLOCK  — details of the most recent deadlock
-- BUFFER POOL AND MEMORY    — buffer pool usage
-- ROW OPERATIONS    — rows read, inserted, updated, deleted per second

-- How long a transaction will wait for a lock before giving up
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- Default: 50 seconds

-- Change for the current session
SET SESSION innodb_lock_wait_timeout = 5;

-- When a lock wait times out, the current statement is rolled back
-- (not necessarily the entire transaction)
-- ERROR 1205: Lock wait timeout exceeded; try restarting transaction

-- Find transactions holding metadata locks
SELECT
  p.id,
  p.user,
  p.host,
  p.db,
  p.command,
  p.time,
  p.info
FROM information_schema.PROCESSLIST p
WHERE p.command != 'Sleep'
ORDER BY p.time DESC;

-- Show metadata lock holders (MySQL 5.7.3+)
SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE'G

-- Prevent overselling: check and deduct stock atomically
START TRANSACTION;

-- Lock the product row before checking stock
SELECT stock_qty
FROM products
WHERE product_id = 7
FOR UPDATE;

-- Check stock (now safe from concurrent modifications)
-- Application checks: if stock_qty >= requested_qty, proceed
UPDATE products
SET stock_qty = stock_qty - 2
WHERE product_id = 7 AND stock_qty >= 2;

-- ROW_COUNT() = 0 means stock was insufficient
-- Application checks ROW_COUNT() and rolls back if 0

COMMIT;

-- Add a version column to the table
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 1;

-- Application reads the row (no lock)
SELECT product_id, price, stock_qty, version FROM products WHERE product_id = 7;
-- Returns: product_id=7, price=29.99, stock_qty=50, version=3

-- Application updates, including the version check
UPDATE products
SET
  stock_qty = stock_qty - 2,
  version   = version + 1
WHERE product_id = 7
  AND version    = 3;   -- fails if version changed since our read

-- Check if another transaction snuck in
-- ROW_COUNT() = 0 means a concurrent update happened; retry or report conflict
SELECT ROW_COUNT();  -- 0 = conflict, 1 = success

# Python pattern for deadlock retry:
# MAX_RETRIES = 3
# for attempt in range(MAX_RETRIES):
#   try:
#     conn.start_transaction()
#     # ... DML statements ...
#     conn.commit()
#     break   # success
#   except mysql.connector.errors.DatabaseError as e:
#     conn.rollback()
#     if e.errno == 1213 and attempt < MAX_RETRIES - 1:
#       time.sleep(0.1 * (attempt + 1))  # exponential backoff
#       continue
#     raise  # re-raise after max retries