Locking Mechanisms in MySQL

-- Row-level locking (InnoDB)
START TRANSACTION;
SELECT * FROM orders 
WHERE id = 123 
FOR UPDATE;  -- Exclusive lock
-- Do work
COMMIT;

-- Shared lock
SELECT * FROM products
WHERE category = 'electronics'
FOR SHARE;  -- Multiple reads OK

-- Table locks
LOCK TABLES users WRITE, orders READ;
-- Do work
UNLOCK TABLES;

-- DETAILED_TAB:
-- Skip locked rows (MySQL 8.0+)
SELECT * FROM orders
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- Nowait option (MySQL 8.0+)
SELECT * FROM inventory
WHERE product_id = 456
FOR UPDATE NOWAIT;  -- Fail immediately if locked

-- Check locks
SHOW ENGINE INNODB STATUS;

-- Find blocking queries
SELECT * FROM performance_schema.data_lock_waits;

-- Kill blocking connection
SHOW PROCESSLIST;
KILL CONNECTION 1234;

-- Deadlock retry pattern
DELIMITER $$
CREATE PROCEDURE transfer_with_retry()
BEGIN
  DECLARE retry_count INT DEFAULT 3;
  DECLARE CONTINUE HANDLER FOR 1213  -- Deadlock error
    BEGIN
      SET retry_count = retry_count - 1;
      IF retry_count > 0 THEN
        ROLLBACK;
        START TRANSACTION;
      END IF;
    END;
  -- Transaction logic here
END$$