-- 手动加表锁（不建议在 InnoDB 中使用）
LOCK TABLES users READ;    -- 读锁：其他会话可读不可写
LOCK TABLES users WRITE;   -- 写锁：其他会话不可读写

-- 解锁
UNLOCK TABLES;

-- 事务A：查询（持有 MDL 读锁）
BEGIN;
SELECT * FROM users;

-- 事务B：修改表结构（需要 MDL 写锁，被事务A阻塞）
ALTER TABLE users ADD COLUMN age INT;
-- 事务B 被阻塞

-- 如果事务A长时间不提交，事务B和其他所有查询 users 的操作都会被阻塞

-- 查看 MDL 等待
SELECT * FROM performance_schema.metadata_locks;

-- 查看阻塞的线程
SHOW PROCESSLIST;
-- 找到 State = 'Waiting for table metadata lock' 的线程

-- 事务A：给某行加行锁（自动添加 IX 锁）
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 行锁 + IX 锁

-- 事务B：尝试加表锁（MySQL 检查意向锁，发现有 IX 锁，直接等待）
LOCK TABLES users WRITE;  -- 被事务A阻塞

-- 事务A：锁住 id=1 的记录
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 在 id=1 的主键索引上加锁

-- 事务B：尝试修改其他记录可以
UPDATE users SET name = 'new' WHERE id = 2;  -- 正常执行

-- 事务B：尝试修改 id=1 的记录被阻塞
UPDATE users SET name = 'new' WHERE id = 1;  -- 阻塞等待

-- 数据：users 表中有 id=1, 3, 5, 7（无 id=2,4,6）
--                     间隙：(1,3)、(3,5)、(5,7)、(7,+∞)

-- 事务A：
BEGIN;
SELECT * FROM users WHERE id > 2 AND id < 6 FOR UPDATE;
-- 锁住：id=3 上的记录锁 + (1,3) (3,5) (5,7) 的间隙锁
-- 实际上加的是临键锁，后面会讲

-- 事务B：尝试插入 id=4（落在 (3,5) 间隙中）
INSERT INTO users (id, name) VALUES (4, 'new');  -- 等待

-- 数据：id=1, 3, 5, 7
-- 临键锁范围：(-∞, 1], (1, 3], (3, 5], (5, 7], (7, +∞)

-- 事务A：
BEGIN;
SELECT * FROM users WHERE id > 3 FOR UPDATE;
-- 锁住：id=5,7 的记录锁 + (3,5], (5,7], (7,+∞) 的临键锁
-- 其他事务不能插入 id=4,5,6,7,8... 也不能修改 id=5,7

-- 等值查询 - 唯一索引 - 命中：
--    只加记录锁
SELECT * FROM users WHERE id = 5 FOR UPDATE;

-- 等值查询 - 唯一索引 - 未命中：
--    加间隙锁
SELECT * FROM users WHERE id = 4 FOR UPDATE;  -- 间隙锁 (3,5)

-- 范围查询：
--    临键锁
SELECT * FROM users WHERE id > 5 FOR UPDATE;

-- 无索引 WHERE 条件：
--    所有记录加行锁 + 间隙锁 → 相当于表锁（性能极差）
SELECT * FROM users WHERE name = '张三' FOR UPDATE;

-- 事务A：
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- 锁 id=1
-- 此时事务B已经锁了 id=2
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- 等待事务B释放 id=2
-- 事务B也等待事务A释放 id=1 → 死锁

-- 事务B：
BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 2;  -- 锁 id=2
UPDATE account SET balance = balance + 100 WHERE id = 1;  -- 等待事务A释放 id=1

-- 1. 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS \G;
-- 查看 LATEST DETECTED DEADLOCK 部分

-- 2. 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

-- 3. 查看锁信息
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 4. 查看被阻塞的线程
SHOW PROCESSLIST;

-- 1. 固定操作顺序
-- 坏：不同事务更新顺序不一致
事务A：UPDATE a → UPDATE b
事务B：UPDATE b → UPDATE a

-- 好：所有事务都按相同顺序更新
事务A：UPDATE a → UPDATE b
事务B：UPDATE a → UPDATE b

-- 2. 缩短事务时间
BEGIN;
  -- 快速执行
  UPDATE a SET ...;
  UPDATE b SET ...;
COMMIT;  -- 尽快提交

-- 3. 设置锁等待超时
SET innodb_lock_wait_timeout = 5;  -- 5秒超时

-- 4. 使用 READ COMMITTED 隔离级别（间隙锁少很多）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 5. 控制并发量
-- 使用队列或限流减少同时更新同一行

-- 场景：某个 UPDATE 卡住了，排查步骤

-- 步骤1：查看当前所有事务
SELECT trx_id, trx_state, trx_started,
       TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) AS trx_age_seconds
FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING';

-- 步骤2：查看锁等待
SELECT
    waiting_trx_id,
    waiting_thread,
    blocking_trx_id,
    blocking_thread
FROM performance_schema.data_lock_waits;

-- 步骤3：查看阻塞的 SQL
SELECT thd.PROCESSLIST_ID, thd.PROCESSLIST_INFO
FROM performance_schema.threads thd
JOIN performance_schema.data_lock_waits dlw
  ON thd.PROCESSLIST_ID = dlw.BLOCKING_THREAD_ID;

-- 步骤4：kill 阻塞的线程（紧急情况下）
KILL [CONNECTION] 123;  -- 123 是线程ID

-- 场景：库存扣减，使用悲观锁防止超卖
BEGIN;

-- 锁定商品行，防止其他事务读取/修改
SELECT stock FROM inventory WHERE product_id = 100 FOR UPDATE;

-- 检查库存
IF stock >= 1 THEN
    UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
    INSERT INTO orders (product_id) VALUES (100);
    COMMIT;
ELSE
    ROLLBACK;
    RETURN '库存不足';
END IF;

-- 场景：使用版本号或条件实现乐观锁

-- 方式1：版本号
-- 建表时加 version 字段
ALTER TABLE inventory ADD COLUMN version INT DEFAULT 0;

-- 更新时检查版本号
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE product_id = 100
  AND stock > 0
  AND version = ?;  -- 传入上次查询到的 version

-- 如果 affected_rows = 0，说明数据已被修改，需要重试

-- 方式2：条件校验（无需 version 字段）
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 100
  AND stock > 0;

-- 如果 affected_rows = 0，说明库存不足或条件不满足

-- 商品表
CREATE TABLE inventory (
    id INT PRIMARY KEY,
    product_id INT,
    stock INT,
    version INT DEFAULT 0,
    INDEX idx_product (product_id)
);

-- 会话1：
BEGIN;
SELECT stock FROM inventory WHERE product_id = 100 FOR UPDATE;
-- 加锁：idx_product 上的临键锁 + 聚簇索引上的记录锁

-- 会话2：此时尝试更新同一商品
UPDATE inventory SET stock = stock - 1
WHERE product_id = 100 AND stock > 0;
-- 被会话1阻塞，等待行锁

-- 会话3：更新其他商品不受影响
UPDATE inventory SET stock = stock - 1
WHERE product_id = 200;
-- 可以正常执行

-- 数据：orders 表 id=1,5,10

-- 事务A：
BEGIN;
SELECT * FROM orders WHERE id BETWEEN 3 AND 7 FOR UPDATE;
-- 加锁范围：id=5 的记录锁 + (1,5] 和 (5,10] 的间隙锁
-- 实际：临键锁覆盖 (1,5] 和 (5,10]

-- 事务B：以下操作都会被阻塞
INSERT INTO orders (id) VALUES (2);  -- 落在 (1,5) 间隙
INSERT INTO orders (id) VALUES (4);  -- 落在 (1,5) 间隙
INSERT INTO orders (id) VALUES (6);  -- 落在 (5,10) 间隙
UPDATE orders SET ... WHERE id = 5;  -- id=5 有记录锁

-- 事务B：以下操作可以执行
INSERT INTO orders (id) VALUES (11);  -- 落在 (10,+∞) 间隙
UPDATE orders SET ... WHERE id = 1;   -- id=1 不在锁范围
UPDATE orders SET ... WHERE id = 10;  -- id=10 不在锁范围

-- 场景：A给B转账 + B给A转账，交叉更新

-- 安全方案：固定更新顺序（按用户ID升序）
BEGIN;

-- 先锁小ID，再锁大ID
IF 1 < 2 THEN  -- id=1 是转出，id=2 是转入
    UPDATE account SET balance = balance - 100 WHERE id = 1;
    UPDATE account SET balance = balance + 100 WHERE id = 2;
ELSE
    UPDATE account SET balance = balance + 100 WHERE id = 2;
    UPDATE account SET balance = balance - 100 WHERE id = 1;
END IF;

COMMIT;

-- 另一种方案：使用锁超时自动回滚
SET innodb_lock_wait_timeout = 3;  -- 3秒超时
SET innodb_deadlock_detect = ON;   -- 默认ON，主动检测死锁