MYSQL 8 · INNODB · MVCC · READ VIEW · RECORD / GAP / NEXT-KEY LOCK

读的人看照片，写的人排队拿钥匙

InnoDB 让读和写尽量互不打扰。普通 SELECT 不加锁，看的是某一刻拍下的“照片”，这叫 MVCC（多版本并发控制）。改数据的人要先拿到这一行的锁，同一行同一时刻只能一个人改。

后来一个读
后来一个写
先有人在读
不用等各看各的照片
不用等读的人没加锁，照写
先有人在写
不用等去读改之前的旧版本
排队等前一个提交或回滚

这里的“读”指普通 SELECT。SELECT … FOR UPDATE、FOR SHARE、UPDATE、DELETE、INSERT 都要拿锁，算“写”那一边。

行标签DB_TRX_ID · DB_ROLL_PTR

每一行背后藏着两样东西：最后是哪个事务改的，旧版本在哪。

每一行都有，SELECT 看不到
旧版本undo log

改之前的样子都留着，串成一条版本链。

没人要看了才清掉
照片Read View

记下拍照那一刻哪些事务还没提交，决定版本链上哪一个你看得见。

RC 每条 SELECT 拍一次，RR 只拍一次
锁Record · Gap · Next-Key

加在索引上，写和写之间靠它排队。

提交或回滚才放

每一行背后：隐藏列和版本链

InnoDB 改一行时，不会把旧值直接抹掉。旧值先写进 undo 日志，新行上记下“是我改的”和“旧版本在哪”。改几次，就串出一条链。

表里的这一行（最新）balance = 40DB_TRX_ID = 201
undo 里的旧版本balance = 50DB_TRX_ID = 200
undo 里更旧的版本balance = 100DB_TRX_ID = 90
再往前没人要看了后台 purge 线程已经清掉
DB_TRX_ID · 6 字节

最后一次插入或修改这一行的事务 ID。事务 ID 只增不减，越大越晚。

DB_ROLL_PTR · 7 字节

回滚指针，指向 undo 日志里的上一个版本。回滚时用它恢复，MVCC 读旧版本时也顺着它找。

DB_ROW_ID · 6 字节

只有表没有主键、也没有非空唯一键时才有，InnoDB 用它自己造一个聚簇索引。

删除也一样DELETE 不会马上把行删掉，只打一个删除标记。等后台的 purge 线程确认没有哪张照片还需要它，才真正删掉，undo 里的旧版本也是这样清的。

照片怎么决定你看得见哪个版本

普通 SELECT 第一次执行时，InnoDB 给你拍一张照片（Read View）。照片上只记四个数，然后拿它去比版本链上每个版本的 DB_TRX_ID。

m_ids = [200, 203]min_trx_id = 200max_trx_id = 205creator_trx_id = 0
198看得见
199看得见
200看不见
201看得见
202看得见
203看不见
204看得见
205看不见
206看不见
< 200拍照前就提交了
200 ～ 204查名单：在 m_ids 里的还没提交
≥ 205拍照后才开始

m_ids：拍照时还没提交的事务。min_trx_id：名单里最小的。max_trx_id：下一个要分配的事务 ID。creator_trx_id：自己的事务 ID，只读过、没改过数据时是 0。

拿一个版本的 DB_TRX_ID 去比
  1. 等于自己的 ID：自己改的，看得见
  2. 小于 min_trx_id：拍照前就提交了，看得见
  3. 大于等于 max_trx_id：拍照后才开始，看不见
  4. 在 m_ids 名单里：拍照时还没提交，看不见；不在名单里就看得见

看不见，就顺着 roll_ptr 找上一个版本，再比一次，直到找到看得见的那个。

RC
BEGINSELECT 拍一张别人提交了SELECT 再拍一张COMMIT

读已提交：每条 SELECT 重新拍，所以能看到别人刚提交的数据。

RR
BEGINSELECT 拍一张别人提交了SELECT 用老照片COMMIT

可重复读（MySQL 默认）：第一条 SELECT 拍的照片用到事务结束，同样的查询结果不变。

RR 的照片在第一条 SELECT 时才拍，不是在 BEGIN 时。想在开始时就拍，用 START TRANSACTION WITH CONSISTENT SNAPSHOT。

MVCC 实验台

两个事务同时碰账户 1 的余额。一步步走，看版本链怎么长、照片怎么拍、A 每次读到什么。切换 RC 和 RR，对比第 4 步。

隔离级别：

    快照读和当前读

    同一个事务里，有的语句看照片，有的语句看最新数据。实验台第 5 步的“读到 100，扣完却是 40”就是这么来的。

    快照读：看照片

    SELECT …
    • 按照片规则走版本链，可能读到旧版本
    • 不加锁，不会被别人的锁挡住

    当前读：看最新，并加锁

    SELECT … FOR UPDATESELECT … FOR SHAREUPDATEDELETEINSERT
    • 读最新提交的版本，不管照片
    • 读到的行要加锁，有人锁着就排队
    PostgreSQL 不一样同样的剧情在 PostgreSQL 的 REPEATABLE READ 下，A 的 UPDATE 会直接报错 could not serialize access due to concurrent update，要求应用重试；MySQL 则读最新值继续改。另外 PostgreSQL 把旧版本留在表里（靠 VACUUM 清），也没有间隙锁。
    隔离级别普通 SELECT 看到什么当前读加什么锁幻读（多出来的行）
    READ UNCOMMITTED最新版本，连没提交的也看得到只锁行有
    READ COMMITTED每条 SELECT 新拍一张照片只锁行，不锁间隙有
    REPEATABLE READ（默认）第一条 SELECT 拍的照片，用到事务结束临键锁：锁行，也锁行前面的间隙快照读看不到；当前读靠间隙锁挡住插入
    SERIALIZABLE关掉自动提交时，普通 SELECT 也变成 FOR SHARE，要加锁同 RR没有

    RR 下也不是完全没有幻读：先快照读看到 3 行，别人插入并提交了 1 行，再用 UPDATE 改“所有行”，会改到 4 行，之后快照读也能看到这第 4 行（自己改过的，看得见）。Spring 里 @Transactional(isolation = Isolation.READ_COMMITTED) 只改这一个事务的级别，结束后 Spring 会把连接改回去（Spring 第 02 篇）。

    锁有哪几种

    行锁有四种，区别在于锁的是一条记录，还是两条记录之间的空隙。下面的小图里，方块是索引上的记录，虚线框是记录之间的间隙。

    InnoDB 的锁加在索引上，不是加在“行”上。WHERE 条件用得上索引，就只锁扫到的那几条；用不上索引，就只能把主键索引从头扫到尾，扫到一条锁一条，RR 下连间隙一起锁，等于锁了整张表。

    记录锁Record Lock · X,REC_NOT_GAP

    只锁住 10 这一条，别人不能改它、删它。

    间隙锁Gap Lock · X,GAP

    锁住 10 和 15 之间的空隙，只挡插入。间隙锁之间互不冲突，两个事务可以同时锁同一个间隙。

    临键锁Next-Key Lock · X

    记录 + 它前面的间隙，写作 (5, 10]。RR 下当前读默认加的就是它。

    插入意向锁Insert Intention

    INSERT 前在间隙里打的招呼。碰上别人的间隙锁就得等；两个插入只要位置不同，互不影响。

    共享锁 S 和排他锁 X
    后来 S后来 X 先有 S可以等 先有 X等等

    FOR SHARE 加 S，FOR UPDATE、UPDATE、DELETE 加 X。

    意向锁 IS / IX（表级）

    加行锁前，先在表上挂个牌子“表里有人锁了行”。它只是给要锁整张表的人（比如 LOCK TABLES … WRITE）看的，IX 和 IX 互不冲突，不会让行锁之间多排队。

    元数据锁 MDL 和自增锁

    增删改查会自动拿 MDL 读锁，用到事务结束；ALTER TABLE 要 MDL 写锁，得等前面所有事务结束。自增锁最多锁到语句结束，8.0 默认只是一瞬间的轻量锁。

    锁地图：一条语句锁了哪些地方

    选一条语句，看它在两棵索引上锁了哪些记录和间隙，再看另一个事务的插入和修改会不会被挡住。以 MySQL 8.0.18 之后为准，右边是 performance_schema.data_locks 里能查到的样子。

    示例表：t(id 主键, c 普通索引 idx_c, name 没有索引)，五行数据 (5,5,张三) (10,10,李四) (15,15,王五) (20,20,赵六) (25,25,孙七)

    隔离级别：

    PRIMARY（主键 id）
    idx_c（普通索引 c）
    记录被锁间隙被锁两个挨着都锁 = 临键锁
    
    
    
    
    
      
    另一个事务这时来做这些事：
    
      
    
      
    
    
    
  
    

    



    
  
    
    
    等锁和死锁
    
    
    拿不到锁就等。两个事务互相等对方手里的锁，就是死锁：InnoDB 发现后立刻挑一个回滚，让另一个继续。
    
  
    
  
    
    
    
      
    交叉加锁
    
      
    
        事务 A事务 B
        1UPDATE … WHERE id = 1拿到 1 的锁
        2UPDATE … WHERE id = 2拿到 2 的锁
        3UPDATE … WHERE id = 2等 B 放开 2
        4UPDATE … WHERE id = 1等 A 放开 1：死锁，B 被回滚
      
    
      
    解法：大家按同一个顺序加锁，比如批量更新前按主键排好序。
    
    
    
    
    
      
    先查后插（RR 下很常见）
    
      
    
        事务 A事务 B
        1SELECT … WHERE id = 12 FOR UPDATE没这行，拿到间隙锁 (10,15)
        2SELECT … WHERE id = 12 FOR UPDATE间隙锁不冲突，也拿到
        3INSERT … id = 12被 B 的间隙锁挡住
        4INSERT … id = 12被 A 的间隙锁挡住：死锁
      
    
      
    解法：不要先查再插，直接 INSERT，靠唯一键冲突判断“已经有了”；或者这类业务用 RC。
    
    
    
  
    
  
    
    
    1213 · Deadlock found when trying to get lock; try restarting transaction
    死锁，InnoDB 回滚改动行数少的那个事务。默认开着死锁检测（innodb_deadlock_detect），立刻就报。
    
    
    1205 · Lock wait timeout exceeded; try restarting transaction
    不是死锁，只是等太久：默认等 50 秒（innodb_lock_wait_timeout）。默认只回滚这一条语句，事务还开着。
    
  
    
  
    在 Spring 里，这两个错误都会变成 PessimisticLockingFailureException 的子类，可以统一捕获后重试整个事务。
    
  
    
    
    现场怎么查MySQL 8.0
    

    -- 谁拿着什么锁
SELECT ENGINE_TRANSACTION_ID, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA
FROM performance_schema.data_locks;

-- 谁在等谁，被谁挡住
SELECT * FROM sys.innodb_lock_waits;

-- 开着没提交的事务，最老的排前面
SELECT trx_id, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx ORDER BY trx_started;

-- 最近一次死锁的现场：看 LATEST DETECTED DEADLOCK 那一段
SHOW ENGINE INNODB STATUS;
-- 想把每次死锁都记进错误日志
SET GLOBAL innodb_print_all_deadlocks = ON;
    
  
    

    



    
  
    
    
    MySQL 5.7 和 8.0
    
    
    MVCC 和锁的规则两边一样。变化在查看工具、新语法和 undo 的存放位置。JDK 8 还是 17 不影响这些，影响的是驱动。
    
  
    
  
    
    
    
      
    MySQL 5.7
    Boot 2 常见
    
      
      
        
    • 看锁用 information_schema.INNODB_LOCKS，只显示有人在等的锁
      • 
        
    • 加共享锁写 LOCK IN SHARE MODE
      • 
        
    • undo 默认放在系统表空间 ibdata1，长事务把它撑大后不会缩回去
      • 
        
    • innodb_autoinc_lock_mode 默认 1
      • 
      
    
    
    
    
    
      
    MySQL 8.0
    Boot 3 常见
    
      
      
        
    • 看锁改用 performance_schema.data_locks，持有的锁全看得到；老的 INNODB_LOCKS 删了
      • 
        
    • 新增 FOR SHARE、NOWAIT、SKIP LOCKED，多个消费者抢任务时用 SKIP LOCKED 跳过别人锁着的行
      • 
        
    • undo 默认放在独立的 undo 表空间，可以自动截断
      • 
        
    • innodb_autoinc_lock_mode 默认 2（binlog 默认改成 ROW 格式了）
      • 
        
    • 8.0.18 起，范围查询顺带多锁一条范围外记录的情况，大部分修掉了
      • 
      
    
    
    
  
    
  
    驱动Boot 2 用 8.0 版驱动 mysql:mysql-connector-java，Boot 3 只认新坐标 com.mysql:mysql-connector-j。MySQL 8 把变量 tx_isolation 改名成 transaction_isolation，老的 5.1 驱动连 8.0 会报 Unknown system variable 'tx_isolation'。
    
  
    没变的：隐藏列 + undo 版本链照片的四个数和判断规则默认 RR临键锁和间隙锁死锁检测
    

    



    
  
    
    
    常踩的坑
    
    
    锁的问题，多半是锁得太多、锁得太久，或者加锁顺序乱了。
    
  
    
  
    
    
    一条 UPDATE，整张表都改不动了
    WHERE 里的列没有索引，RR 下扫到的每一行和间隙都锁上了。给条件列建索引，用 EXPLAIN 确认走了索引。
    
    
    明明有索引，还是锁了全表
    字符串列用数字去比，比如 WHERE phone = 13800000000，MySQL 要把每一行转成数字再比，索引用不上。参数类型要和列一致。
    
    
    “没有就插入”，高峰期一直报 1213
    先 FOR UPDATE 查一条不存在的记录再插入，两个请求拿到同一个间隙锁，互相挡住对方的插入。直接 INSERT，靠唯一键判断。
    
    
    批量更新时偶尔死锁
    两个事务按不同顺序更新同几行。更新前按主键排序，大家用同一个顺序加锁。
    
    
    很多请求同时插同一个唯一键，也会死锁
    重复键冲突时，等着的事务会在那条记录上加 S 锁。第一个事务一回滚，后面几个都拿着 S 锁再要 X 锁，互相卡住。用 INSERT … ON DUPLICATE KEY UPDATE 或在应用层去重。
    
    
    Lock wait timeout exceeded（1205）
    有事务拿着行锁很久不提交，多半是事务里调了外部接口。用 sys.innodb_lock_waits 找到挡路的那个（数据库第 01 篇讲了怎么缩短事务）。
    
    
    两个人同时扣钱，有一笔没扣上
    先 SELECT 读到 100，在 Java 里算出 90，再 SET balance = 90，把别人刚扣的覆盖掉了。改成 SET balance = balance - 10 WHERE id = ? AND balance >= 10 并检查影响行数，或者先 FOR UPDATE，或者加 version 字段做乐观锁。
    
    
    以为换成 RC 就没有锁了
    RC 只是没有间隙锁，行锁一样有，两个人改同一行照样排队。RC 换来的是更少的锁冲突和死锁，代价是同一事务里两次读可能不一样。
    
    
    库越用越慢，ibdata1 越来越大
    有个事务开了几个小时没提交，它的照片还要看旧版本，purge 一直清不掉。SHOW ENGINE INNODB STATUS 里的 History list length 会越来越大。查 innodb_trx 找出它。
    
    
    加个字段，整张表的查询都卡住了
    ALTER TABLE 在等一个没提交的事务放开 MDL，排在它后面的所有查询都显示 Waiting for table metadata lock。改表前先查长事务，并把 lock_wait_timeout 设短一点（默认一年）。