MySQL 事务与隔离级别：锁、MVCC 与死锁排查

CREATE TABLE orders (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    status     TINYINT NOT NULL,
    amount     DECIMAL(12,2) NOT NULL,
    version    INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    KEY idx_user_status_created (user_id, status, created_at)
) ENGINE=InnoDB;

-- 会话 1（事务 A）                          -- 会话 2（事务 B）
BEGIN;
SELECT amount FROM orders WHERE id = 1;
-- 返回 100
                                             BEGIN;
                                             UPDATE orders SET amount = 60 WHERE id = 1;
                                             COMMIT;
SELECT amount FROM orders WHERE id = 1;
-- 第二次读的结果按隔离级别分岔：
-- 脏读（RU）：把这条 SELECT 放在 B 提交前执行，也能读到 60（读到未提交数据）
-- 不可重复读（RC）：读到 60；RR：仍读到 100（第一次快照）
COMMIT;
-- 幻读是行集合版本的同款问题：
-- B 在两次 SELECT 之间 INSERT 了满足 WHERE 的新行，第二次结果集多出一行

-- 查看与设置隔离级别（8.0 的变量名是 transaction_isolation）
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 可以直接看到隐藏列（仅调试用）
SELECT id, amount,
       DB_TRX_ID, DB_ROLL_PTR
FROM orders WHERE id = 1;

-- 快照读：走 MVCC，不加锁，读的是快照版本
SELECT * FROM orders WHERE id = 1;

-- 当前读：读最新已提交数据并加锁，以下全是
SELECT * FROM orders WHERE id = 1 FOR UPDATE;        -- 排他锁（X 锁）
SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁（S 锁），8.0 可写 FOR SHARE
UPDATE orders SET status = 1 WHERE id = 1;            -- DML 本身就是当前读
DELETE FROM orders WHERE id = 1;

-- 乐观并发：读时带 version，写时校验 version
SELECT id, status, version FROM orders WHERE id = 10;   -- 读到 version = 5
UPDATE orders SET status = 1, version = version + 1
WHERE id = 10 AND version = 5;
-- affected rows = 0：说明别人已改过，业务层决定重试整个读-判-写

-- 事务 A（RR 级别）
BEGIN;
SELECT * FROM orders WHERE user_id = 42 AND status = 1 FOR UPDATE;
-- 若命中行是 id=10 和 id=20，next-key 锁住 (前一段间隙,10] 和 (10 到 20 的间隙,20]
-- 事务 B 此时尝试插入同样满足条件的行：
-- INSERT INTO orders (user_id, status, amount, created_at) VALUES (42, 1, 99.00, '2026-10-22 10:00:00');
-- 会被间隙锁挡住（间隙锁之间互相冲突）

-- 事务 1                        -- 事务 2
BEGIN;                           BEGIN;
UPDATE orders SET amount=1
  WHERE id = 10;                 -- 锁 id=10
                                 UPDATE orders SET amount=2
                                   WHERE id = 20;   -- 锁 id=20
UPDATE orders SET amount=3
  WHERE id = 20;                 -- 等事务 2 放行 → 阻塞
                                 UPDATE orders SET amount=4
                                   WHERE id = 10;   -- 等事务 1 放行 → 死锁
-- InnoDB 检测到等待环：回滚代价小的一方（通常改行少的），另一条继续执行
-- 回滚方收到错误：ERROR 1213 (40001): Deadlock found when trying to get lock

SHOW ENGINE INNODB STATUS\G
-- 找 "LATEST DETECTED DEADLOCK" 段：
-- 两个事务各自正在执行的 SQL、持有的锁（HELD MUTEXES 之后的 TRANSACTION 段）
-- 等待的锁（lock_mode X locks rec but not gap / waiting）
-- 以及 victim 是谁。定期采集进监控（该段只保留最近一次死锁）

SELECT r.trx_id, r.trx_mysql_thread_id AS thread_id,
       r.trx_query, r.trx_rows_locked,
       b.trx_mysql_thread_id AS blocking_thread_id, b.trx_query AS blocking_query
FROM information_schema.INNODB_TRX r
JOIN performance_schema.data_lock_waits w ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;
-- 一眼看到：谁在等谁、各自卡在哪条 SQL

-- 固定顺序的实例：批量扣减按 id 升序处理
SELECT id FROM orders WHERE id IN (30, 10, 20) FOR UPDATE;
-- 实际按索引顺序加锁（10→20→30），两个事务并发跑也只会排队不会死锁

-- 找出运行最久的事务
SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_secs,
       trx_rows_locked, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC LIMIT 5;
-- run_secs 超过几百秒的事务基本都要人工过问

-- 锁等待超时（默认 50 秒，超时报 1205 ERROR）
SET GLOBAL innodb_lock_wait_timeout = 10;
-- 死锁检测开关（默认开启；极高并发的热点更新场景可评估关闭换死锁监控告警）
-- innodb_deadlock_detect = ON

-- SKIP LOCKED：跳过正被别人锁住的行，多 worker 抢任务队列互不等待
SELECT id FROM jobs WHERE status = 0 FOR UPDATE SKIP LOCKED;
-- NOWAIT：锁不到立刻报错（错误 3572），不排队，应用侧决定重试还是放弃
SELECT id FROM jobs WHERE id = 7 FOR UPDATE NOWAIT;