MySQL学习(18)锁

锁

前言

事务的并发一般分为读-读情况、写-写情况和读-写情况。读-读情况不会对记录造成任何影响，写-写情况可能会发生脏写，读-写情况可能会发生脏读、不可重复读和幻读。

什么是锁

当一个事务想对某条记录进行修改时，会在内存中生成一个结构与之关联，使得记录不能被其他事务访问，这个内存结构就是锁。锁结构中重要的信息为：

• 事务信息：申请获取该锁的事务的trx_id。
• is_waiting：false表示加锁成功；true表示加锁成功，线程等待。

当一个事务获取到某个记录的锁后，另一个事务想要获取该记录的锁，此时第一个事务的is_waiting为false，第二个事务的is_waiting为true。第一个事务提交后，会把锁结构释放，第二个事务对应的is_waiting变为false，获取到锁，线程唤醒，开始访问该记录。

• 获取锁成功：生成锁结构，is_waiting为false，可以直接操作。并不是所有的加锁操作都需要生成锁结构，比如加隐式锁，隐式锁并不会生成锁结构，但是仍然可以起到一样的作用。
• 获取锁失败：生成锁结构，is_waiting为true，需要等待，不可以直接操作。
• 不加锁：不需要身成功哼锁结构，可以直接执行操作。

怎样避免脏读、不可重复读、幻读

• **方案1：**读操作使用MVCC，写操作进行加锁。

读取记录时，生成ReadView，找到符合条件的记录版本。写操作肯定要对最新版本的记录，必须加锁。

• **方案2：**读、写操作都进行加锁。

有些场景特殊，需要读、写操作都进行加锁，所有的读、写都要进行“排队”执行。

如果采用方案1，读写操作不冲突，性能高；如果在用方案2，读写操作排队执行，影响性能。一般采用方案1，特殊需求使用方案2。

使用MVCC的读操作也称为一致性读，不会对任何记录加锁。

两种作用的锁

在处理并发的读和写操作时，通过两种类型的锁解决问题。

• 共享锁（S锁）：共享锁是共享的，互不阻塞的。一个事务获取了记录的共享锁后，其他事务也可以获得该记录的共享锁。但其他事务不可以获取排他锁。
• 排他锁（X锁）：排他锁是阻塞的。一个事务获取了记录的排他锁后，直至该事务释放排他锁前，其他事务都不能访问该记录。

锁定读

在读取记录前为该记录加锁的读取方式称为锁定读。

• 对读取的记录加共享锁

SELECT * FROM single_table WHERE id = 5 LOCK IN SHARE MODE;

• 对读取的记录加排他锁

SELECT * FROM single_table WHERE id = 5 FOR UPDATE;

写操作中的锁

当一个操作执行写操作时，它会默认自动加行级锁，以防止其他事务修改该行记录。

• DELETE：实际操作过程是在B+树中定位到待删除的记录，获取记录的排他锁，最后执行delete mark。
• UPDATE：
  • 如果未修改记录的主键且更新前后列占用的存储空间大小不变，则是在B+树中定位到记录的位置，获取记录的排他锁，最后在原记录的位置进行修改。
  • 如果未修改记录的主键且更新前后列占用的存储空间大小发生改变，在B+树中定位到记录的位置，获取记录的排他锁，将该记录真实删除，再插入一条新记录。被删除记录关联的锁会被转移到新插入的记录。
  • 如果修改了记录的主键，在原记录执行DELETE操作之后再INSERT操作。
• INSERT：一般情况下，心插入的一条记录受隐式锁保护，不需要在内存中建立锁结构。

表锁

上面说的共享锁都是行级锁，一个事务也可以对表级别进行加锁。

• 对表加共享锁

  • 其他事务可以获取该表的共享锁。
  • 其他事务可以获取该表中记录的共享锁。
  • 其他事务不能获取该表的排他锁。
  • 其他事务不能获取该表中记录的排他锁。

• 对表加排他锁

  其他事务不能获取该表以及表中记录的任何锁。

锁的大致分类

• 行级锁：
  • 共享锁（S锁）
  • 排他锁（X锁）
• 表级锁：
  • 意向共享锁（IS锁）
  • 意向排他锁（IX锁）
  • 表共享锁
  • 表排他锁
  • AUTO-INC锁

其他存储引擎中的锁

InnoDB存储引擎中的锁

表级锁

• 表级别共享锁和排他锁

在对表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎不会对这个表添加表级别的共享锁或排他锁。

在系统崩溃恢复时，会对表进行加锁，另外可以手动对表进行加锁。将系统变量autocommit设置为0，innodb_table_locks设置为1时，可以根据下列语句手动获取表锁。

InnoDB存储引擎对表加表级共享所：

LOCK TABLES t_name READ;

InnoDB存储引擎对表加表级排他锁：

LOCK TABLES t_name WRITE;

尽量不要使用这样的方式，会造成并发性能问题。

在对表执行DDL语句时，其它事务对该表执行SELECT、INSERT等语句时会发生阻塞；在对表执行SELECT、INSERT等语句时，其他会话中对表执行DDL会发生阻塞。这个过程是通过server层使用一种叫元数据锁（Metadata Lock，DML）实现的。
DDL语句在执行时会隐式提交当前会话中的事务，原因是DDL语句的执行一般都会在若干个特殊事务中完成。在开启这些特殊事务前，需要将当前会话中的事务提交掉。

• 表级别意向共享锁和意向排他锁

事务在给表中的某条记录加共享锁时，必须先给该表加意向共享锁；事务在给表中的某条记录加排他锁时，必须先给该表加意向排他锁。

意向锁的作用就是方便事务在表进行加锁时，直接知道该表中是否存在还未释放的行级锁。对表加共享锁时，必须确保表中记录没有被关联排他锁的事务正在执行，也就是无意向排他锁；对表加排他锁时，必须确保表中记录没有被关联共享锁以及排他锁的事务正在执行，也就是无意向共享锁及意向排他锁。

• 表级别AUTO-INC锁

当主键设置为AUTO_INCREMENT且插入语句未指定主键值时，

当不确定插入记录的数量时，比如使用INSERT…SELECT、REPLACE…SELECT或者LOAD DATA语句，会使用AUTO-INC锁为AUTO_INCREMENT修饰的列生成对应的值。在插入语句执行完成后，这个锁就是放了，和事务没有关系。

当确定插入记录的数量时，采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取这个轻量级锁，然后在生成完成这个值后，就立刻释放锁，不需要等整个插入语句完成后才释放锁。

当系统变量innodb_autoinc_lock_mode=0时，一律采用AUTO-INC锁；当系统变量innodb_autoinc_lock_mode=1时，根据插入记录的数量确定性，自动决定使用AUTO-INC锁还是轻量级锁；当系统变量innodb_autoinc_lock_mode=2时一律采用轻量级锁。

行级锁

CREATE TABLE hero (
	number INT,
	name VARCHAR(100),
	country varchar(100),
	PRIMARY KEY (number)
);
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'), 
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴');

• Record Lock

官方名称叫LOCK_REC_NOT_GAP，仅仅把一条记录锁上，分为S锁和X锁。当一个事务获取了一条记录的S锁后，其他事物可以获取该记录的S锁，不可以获取该记录的X锁；当一个事务获取了一条记录的X锁后，其他事物不可以获取该记录的S锁，也不可以获取该记录的X锁。

• Gap Lock

官方名称叫LOCK_GAP，为了防止插入幻影记录。在一条记录上加入了gap锁后，在该事务提交并释放锁之前，不允许其他事务在页中该记录与前一条记录的间隙中插入记录。gap锁不会限制其他事务对这条记录加record锁或gap锁。

为了防止其他事务最末端插入记录，那就给页最后一条记录，也就是Supremum记录加锁。

• Next-Key Lock

官方名称叫LOCK_ORDINARY，在gap锁的基础上，又不让其他事务对记录加锁。既能保护记录，又能防止别的失去匠心记录插入到被保护记录前面的间隙。

• Insert Intention Lock

官方名称叫LOCK_INSERT_INTENTION，也叫插入意向锁。一个事务在插入一条记录时，需要判断插入位置是否已被别的事务加了gap锁或next-key锁。如果有，插入操作需要等待。事务在等待时需要在内存中生成一个锁，表明事务想在某个间隙插入新记录，但是现在处于等待状态。

一个事务对某条记录加了gap锁，is_waiting为false，其他事务想在该记录前面的间隙插入记录时，就会生成一个插入意向锁，is_waiting为true。当gap锁被释放后，生成插入意向锁的事务就可以获取插入意向锁了，is_waiting置为false，该事务可以执行插入新记录。

插入意向锁不会组织别的事务获取该记录上的任何类型的锁。在记录前间隙插入记录，本身与该记录的gap锁意外的其他锁，没啥关系。

• 隐式锁

一个事务对新插入的记录可以不显式地加锁，相当于加了一个隐式锁。

当一个事务先插入了一条记录，但未提交事务。其他事务想要获取这条记录的锁，或时想修改这条记录。

对于聚簇索引记录来说，有一个trx_id隐藏列。如果其他事务想添加S锁或X锁，先看这条记录的trx_id代表的事务是否是当前活跃的事务。如果不是，就正常读取；如果是，就为这个事务对该记录增加一个锁，is_waiting为false，为自己对这条记录生成一个锁，is_waiting为true。

对于二级索引来说，页面Page Header部分有一个PAGE_MAX_TRX_ID属性，代表对页面做修改的最大事务id。如果PAGE_MAX_TRX_ID小于当前活跃的事务最小的事务id，那就说明对这个页面做修改的事务全部都提交了；否则要根据页面中二级索引记录的主键，通过回表操作找到它的聚簇索引记录，然后重复上述。

有点像二级索引MVCC。隐式锁的好处就是节省空间，一条记录被加了隐式锁，有其他事务访问这条记录，就在内存中生成锁结构，如果没有其他事务访问这条记录，那就不在内存中加锁结构。

InnoDB锁的内存结构

符合下列要求的记录可以放在一个锁结构里：

• 在同一个事务中进行加锁操作；
• 被加锁的记录在同一个页面；
• 加锁的类型是一样的；
• 等待状态是一样的。

锁结构中的各种信息：

• 锁所在的事务信息：无论是表锁还是行锁，一个锁属于一个事务，这里记录的是该锁对应的事务信息（指针）。
• 索引信息：对于行级锁，要记录加锁的记录属于哪个索引（指针）。
• 表锁/行锁信息：表级锁和行级锁结构对应这个的位置不同的内容。
  • 表级锁记录了加锁的表的信息和一些其他信息。
  • 行级锁记录了下面3个重要的信息：
    • Space ID：加锁记录所在的表空间。
    • Page Number：加锁记录所在的页号。
    • n_bits：表示“一堆比特位”使用了多少比特。这些比特位对应了每条记录是否被加锁。为了避免新插入记录重新分配锁结构，比特位数量比页面中的记录数要多一些。
• type_mode：一个32比特位的数，分为3个部分：
  • lock_mode占用1～4位，可选值有：
    • LOCK_IS（十进制的0）：表示共享意向锁。
    • LOCK_IX（十进制的1）：表示共享拍他锁。
    • LOCK_S（十进制的2）：表示共享锁。
    • LOCK_X（十进制的3）：表示拍他锁。
    • LOCK_AUTO_INC（十进制的4）：表示AUTO-INC锁。
  • lock_type占用5～8位，现阶段只用了5、6位。
    • LOCK_TABLE（十进制的16）：第5位为1时表示表级锁。
    • LOCK_REC（十进制的32）：第6位为1时表示行级锁。
  • rec_lock_type占用剩余的9～32位。只有lock_type为LOCK_REC时，也就是锁的类型为行级锁，才有这些行锁的具体类型。
    • LOCK_ORDINARY（十进制的0）：表示next-key锁。
    • LOCK_GAP（十进制的512）：第10位为1时，表示gap锁。
    • LOCK_REC_NOT_GAP（十进制的1024）：第11位为1时，表示普通的record锁
    • LOCK_INSERT_INTENTION（十进制的2048）：第12位为1时，表示插入意向锁。
    • 其他类型，不常用。
    • LOCK_WAIT（十进制的256）：第9位为1时，表示is_waiting=true；第9位为0时，表示is_waiting=false。

• 其他信息：为了管理各种锁结构而设计的各种哈希表和链表。
• 一堆比特位：如果是行级锁结构，在该锁结构末尾出存放了一堆比特位。比特位的数量用n_bits表示。页面中每一条信息都对应一个heap_no。一个比特位对应着页面中的一个heap_no。

heap_no的介绍查看：

什么是InnoDB数据页

语句加锁

ALTER TABLE hero ADD INDEX idx_name (name);

普通读（普通的SELECT语句）

在不同的隔离级别下，普通的SELECT语句具有不同的表现。

• 在READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本；可能脏读、不可重复读、幻读。
• 在READ COMMITTED隔离级别下，不加锁；每次普通的SELECT语句执行时生成一个ReadView，避免了脏读，可能出现不可重复读和幻读。
• 在REPEATABLE READ隔离级别下，不加锁；在第一次执行普通SELECT语句时生成一个ReadView，避免了脏读、不可重复读，很大程度避免了幻读。
• 在SERIALIZABLE隔离级别下，
  • 系统变量autocommit=0时，禁用自动提交，普通的SELECT语句会被转为SELECT…LOCK IN SHARE MODE语句，也就是给记录加上S锁。
  • 系统变量autocommit=1时，启用自动提交，普通的SELECT语句不会自动加锁，只是利用MVCC生成readVIew的方式来读取记录。自动提交模式下一个事务只包含一条语句，就不存在出现不可重复读和幻读的情况。

REPEATABLE READ隔离级别有一种情况比较特殊：当事务T1执行普通SELECT一条记录，事务T2插入一条记录，事务T1紧接着修改了这条记录，并且这条记录符合事务T1执行的SELECT查询条件，此时这条记录的隐藏列trx_id为T1，所以事务T1再一次执行SELECT时就会查询到这条记录，从而出现幻读现象。这就是说MVCC可以很大程度避免幻读，但是不能完全避免。

锁定读的语句

就是SELECT…LOCK IN SHARE MODE和SELECT…FOR UPDATE。他们在不同的索引中的执行过程如下，其中对于不同的隔离级别有不一样的处理。

精准匹配指的是单点扫描区间，如果形成扫描范围，就不是精准匹配。
唯一性搜索指的是查询语句执行过程中的匹配模式为精准匹配，且实用的索引是主键或唯一二级索引，且搜索条件不能为索引列IS NULL，如果索引中包含多个咧，那么在生成扫描区间时，每一个列都要覆盖到。这就是唯一性搜索。

SELECT…LOCK IN SHARE MODE

• 步骤1：不论是聚簇索引还是二级索引，根据B+树找到扫描区间的第一条记录，作为当前记录。
• 步骤2：为当前记录加锁。
  • 隔离级别为READ UNCOMMITTED和READ COMMITTED时，为当前记录加record lock普通记录锁；
  • 隔离级别为REPEATABLE READ和SERIALIZABLE时，为当前记录加next-key锁。
• 步骤3：判断索引条件下推的条件是否成立。
  • 聚簇索引，跳过本步骤；
  • 二级索引。
    • 符合条件边界，
      • 当前记录符合条件下推的条件，跳过本步骤，继续执行；
      • 当前记录不符合条件下推的条件，获取下一条记录，作为新的当前记录，跳至步骤2。
    • 不符合条件边界，跳过步骤4、步骤5，直接向server层返回“查询完毕”信息。当前记录的锁不会释放。
• 步骤4：执行回表操作。
  • 聚簇索引，跳过本步骤；
  • 二级索引，
    • 索引覆盖，跳过本步骤；
    • 回表，获取到对应的聚簇索引记录，并给聚簇索引记录加record lock普通记录锁，继续执行。
• 步骤5：判断边界条件
  • 该记录符合边界条件，执行步骤6；
  • 该记录不符合边界条件，根据隔离级别决定是否释放锁，并向server层返回“查询完毕”信息。
    • 隔离级别为READ UNCOMMITTED和READ COMMITTED时，释放该记录的锁；
    • 隔离级别为REPEATABLE READ和SERIALIZABLE时，不释放该记录的锁。
• 步骤6：server层判断其余条件
  • 成立，将记录发送给客户端，再执行步骤7，不释放该记录的锁。
  • 不成立，根据隔离级别决定是否释放锁，执行步骤7。
    • 隔离级别为READ UNCOMMITTED和READ COMMITTED时，释放该记录的锁；
    • 隔离级别为REPEATABLE READ和SERIALIZABLE时，不释放该记录的锁。
• 步骤7：获取下一条记录，作为新的当前记录，跳至步骤2。

索引条件下推的目的是为了减少回表次数，在索引中进行记录的条件过滤。

SELECT…FOR UPDATE

加锁方式和SELECT…LOCK IN SHARE MODE类似，只不过给记录加的是X锁。

UPDATE

加锁方式和SELECT…FOR UPDATE类似。如果更新了二级索引列，那么所有被更新的二级索引记录在更新前都需要加X型record lock普通记录锁。

• 隔离级别为READ UNCOMMITTED和READ COMMITTED时，根据上述步骤，对扫描区间中的聚簇索引记录加上X型普通记录锁，不符合边界条件和其他条件的记录，释放锁。对被更新的记录的二级索引记录加X型普通记录锁。
• 隔离级别为REPEATABLE READ和SERIALIZABLE时，同上，区别在于不符合边界条件和其他条件的记录不会释放锁。

DELETE

加锁方式与SELECT…FOR UPDATE类似。如果表中包含二级索引，那么二级索引记录被删除之前需要加X型普通记录锁。

对于UPDATE、DELETE语句来说，实际加的是隐式锁。对于隔离级别不大于READ COMMITTED，采用的是半一致性的方式执行UPDATE。

特殊情况

• 隔离级别为READ UNCOMMITTED和READ COMMITTED时

  • 二级索引

    • 精确匹配模式，不会为扫描区间后面的下一条记录加锁。获取到下一条记录时，由于精确匹配模式，在存储引擎内部就可以判断该记录不符合精确匹配的条件，直接向server层报告“查询完毕”。不需要先加锁，再给server层判断是否释放锁。

      SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
      

      

• 隔离级别为REPEATABLE READ和SERIALIZABLE时，

  • 二级索引

    • 精确匹配模式，为扫描区间后面的下一条记录加gap锁。如果扫描区间没有记录，也要为扫描区间后面的下一条记录加gap锁。目的是防止其他事务在间隙中插入记录。

      SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
      # 扫描区间不存在记录，就为扫描区间后面的下一条记录加gap锁。
      

      

      

    • 非精确匹配模式，且扫描区间没有记录，为扫描区间后面的下一条记录加next-key锁。

      SELECT * FROM hero WHERE name > 'd' AND name < 'l' FOR UPDATE;
      # 扫描区间不存在记录，就为扫描区间后面的下一条记录加next-key锁。
      

      

  • 聚簇索引，且扫描区间是左闭区间，定位到的第一条聚簇索引记录的主键值与扫描区间中最小的值相同，也就是正好存在等于最左区间的值的记录，为该记录加record lock普通记录锁。

    SELECT * FROM hero WHERE number >= 8 FOR UPDATE;
    # 扫描区间为[8, +∞)，且存在number=8的记录时，为number=8的记录加record lock，为符合扫描区间条件的其他记录加next-key锁。
    

    

    因为主键值是唯一的，其他事务不可能插入相同主键的记录，如果给这个记录加next-key锁，会导致该记录前的间隙无法插入新记录。

  • 以从右至左的方式对扫描区间进行扫描时（比如ORDER BY 索引列 DESC），会给匹配到的第一条记录的下一条（右边）记录加gap锁。

    SELECT * FROM hero FORCE INDEX(idx+name) WHERE name > 'c曹操' AND name < 'x荀彧' AND country != '吴' ORDER BY name DESC FOR UPDATE;
    # 语句使用ORDER BY 索引列 DESC，在扫描记录时会从扫描区间，直接定位到扫描区间的最后一条记录，给下一条记录加gap锁。
    

    

• 唯一性搜索，且读取的记录没有标记删除，也就是记录头信息中delete_flag=1，就为读取到的记录加record lock普通记录锁。不会获取下一条记录并加锁。

  SELECT * FROM hero WHERE number = 8 FOR UPDATE;
  # 扫描区间为[8, 8]，且number=8的记录没有被删除，给该记录加record lock。
  

加锁的目的就是为了避免并发事务执行过程中可能出现的脏写、脏读、不可重复读、幻读等现象，MVCC是另一种解决脏读、不可重复读、幻读的方案。

半一致性读语句

半一致性读介于一致性读（MVCC）和锁定读之间。当隔离级别为READ UNCOMMITTED和READ COMMITTED时，UPDATE语句使用半一致性读。

解释：当UPDATE语句读取到已经被其他事务加了X锁的记录时，会读取该记录的最新已提交版本（根据trx_id判断），然后判断该版本是否与UPDATE语句中的搜索条件匹配。

• 匹配，则再次读取该记录（当前最新）并对其加锁；
• 不匹配，则不对该记录加锁，从而跳到下一条记录。

目的：先拿最新提交版本中的值给server层进行条件匹配，再决定加不加锁。为了让UPDATE语句尽量少被别的语句阻塞。

INSERT语句

INSERT语句一般不需要在内存中生成锁结构，只依靠隐式锁保护插入的记录。在插入一条记录前，先定位到该记录在页面中的位置。如果该位置的下一条记录被加了gap锁，那么当前事务会为该记录加上一个插入意向锁，事务进入等待状态。

有两种情况比较特殊

• 遇到重复键，插入一条记录前，需要确定在B+树中新记录应该插入的位置。如果发现有记录的主键或唯一二级索引列与待插入的主键值或唯一二级索引列值相同，就会报错。在报错之前的处理如下：

  • 如果是主键重复，根据隔离级别的不通，会在聚簇索引会对这个已存在相同键值的记录加S锁。

    • 隔离级别为READ UNCOMMITTED和READ COMMITTED时，加S型record lock；
    • 隔离级别为REPEATABLE READ和SERIALIZABLE时，加S型next-key锁。

  • 如果是唯一二级索引列重复，会在唯一二级索引中为重复的这条记录加S型next-key锁。

    READ UNCOMMITTED或READ COMMITTED隔离级别下使用next-key的目的，是考虑到如果只加record lock，可能出现多条记录的唯一二级索引值都相同的情况。

  使用INSERT…ON DUPLICATE KEY…语句插入记录时，遇到主键或唯一二级索引列的值重复，会对B+树已存在的相同键值的记录加X锁。

• 外键检查，InnoDB支持外键。

  CREAtE TABLE horse (
  	number INT PRIMARY KEY,
  	horse_name VARCHAR(100),
  	FOREIGN KEY (number) REFERENCES hero (number)
  )Engine=InnoDB CHARSET=utf8;
  

  向子表中插入记录时，

  • 待插入记录的外键值在主表中能找到，插入成功。在插入成功前，无论当前事务的隔离级别是什么，只需要直接给父表中对应键值的记录加S型record lock即可。
  • 待插入记录的外键值在主表中找不到，插入失败。根据当前事务隔离级别不同，对父表对应键值的聚簇索引记录进行加锁。
    • 隔离级别为READ UNCOMMITTED和READ COMMITTED时，不对该记录加锁；
    • 隔离级别为REPEATABLE READ和SERIALIZABLE时，对该记录加gap锁。

查看事务加锁情况

通过information_schema数据库中相关的表查看锁的信息

有几个与事务和锁相关的表记录了一些有用的信息。

• INNODB_TRX表：该表存储了InnoDB存储引擎当前正在执行的事务信息。提交后的事务看不到了。

  列名	值	描述
  trx_id	1870	事务id。如果没有分配事务id，则输出事务对应的内存结构的指针
  trx_state	RUNNING	事务的状态。正在运行；等待获取锁
  trx_started	2023/11/20 4:10	事务开启时间
  trx_requested_lock_id	NULL
  trx_wait_started	NULL
  trx_weight	2
  trx_mysql_thread_id	189
  trx_query	NULL
  trx_operation_state	NULL
  trx_tables_in_use	0
  trx_tables_locked	1	该事务目前加了多少个表级锁
  trx_lock_structs	2	该事务生成了多少个内存中的锁结构
  trx_lock_memory_bytes	1136
  trx_rows_locked	1	该事务目前加了多少个行级锁（不包括隐式锁）
  trx_rows_modified	0
  trx_concurrency_tickets	0
  trx_isolation_level	REPEATABLE READ	事务的隔离级别
  trx_unique_checks	1
  trx_foreign_key_checks	1
  trx_last_foreign_key_error	NULL
  trx_adaptive_hash_latched	0
  trx_adaptive_hash_timeout	0
  trx_is_read_only	0
  trx_autocommit_non_locking	0

• INNODB_LOCKS表：该表存储了一些锁的信息：

  • 事务获取的锁为等待状态，则记录该锁信息；
  • 事务获取的某为正在执行状态，但是这个锁阻塞了别的事务，则记录该锁信息。

  当系统中有事务阻塞，这个表中才会有记录。

  # 事务T1
  BEGIN;
  SELECT * FROM hero WHERE number = 8 FOR UPDATE;
  

  # 事务T2
  SELECT * FROM hero WHERE number = 8 FOR UPDATE;
  

  # 查询INNODB_LOCKS表
  SELECT * FROM information_schema.INNODB_LOCKS;
  

  

• INNODB_LOCK_WAITS表：该表存储了每个阻塞的事务是因为获取不到哪个事务持有的锁而导致阻塞。

  SELECT * FROM information_schema.INNODB_LOCK_WAITS;
  

  

  结合INNODB_LOCK_WAITS表和INNODB_LOCKS表中的信息一起看，requesting_trx_id表示因为获取不到锁而被阻塞的事务id（T2）；blocking_trx_id表示获取了锁导致别的事务阻塞的事务id（T1）。

  MySQL 8.0中已将INNODB_LOCKS表和INNODB_LOCK_WAITS表移除。

使用SHOW ENGIN INNODB STATUS查看锁的信息

=====================================
2023-11-20 07:03:51 0x7f3e08433700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 63 srv_active, 0 srv_shutdown, 1058594 srv_idle
srv_master_thread log flush and writes: 1058537
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 38
OS WAIT ARRAY INFO: signal count 36
RW-shared spins 0, rounds 84, OS waits 36
RW-excl spins 0, rounds 66, OS waits 2
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 84.00 RW-shared, 66.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
# 下一个待分配的事务id
Trx id counter 1893

# 一些关于purge的信息
Purge done for trx's n:o < 1891 undo n:o < 0 state: running but idle

# 每个回滚段中的History链表的长度
History list length 16

# 各个事务的具体信息
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421379820700200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421379820699280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421379820698360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
431 OS file reads, 1204 OS file writes, 380 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 16434133
Log flushed up to   16434133
Pages flushed up to 16434133
Last checkpoint at  16434124
0 pending log flushes, 0 pending chkp writes
113 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 223365
Buffer pool size   8191
Free buffers       7396
Database pages     787
Old database pages 270
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 406, created 381, written 873
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 787, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=1, Main thread ID=139904543950592, state: sleeping
Number of rows inserted 21852, updated 0, deleted 0, read 32472
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

修改系统变innodb_status_output_locks为ON，再执行SHOW ENGIN INNODB STATUS会看到事务记录了哪些锁。

死锁

时间序号	T1	T2
1	BEGIN;
2		BEGIN;
3	SELECT * FROM hero WHERE number = 1 FOR UPDATE;
4		SELECT * FROM hero WHERE number = 3 FOR UPDATE;
5	SELECT * FROM hero WHERE number = 3 FOR UPDATE;
（发生阻塞）
6		SELECT * FROM hero WHERE number = 1 FOR UPDATE;
（发生死锁，记录日志，服务器回滚一个事务）

当不同的事务以不同的顺序获取某些记录的锁时，可能会发生死锁。InnoDB有一个死锁检测机制。当它检测到发生死锁时，会选择一个事务执行中插入、更新或删除的记录条数较少的事务进行回滚，并向客户端发送一条消息。

1213 - Deadlock found when trying to get lock; try restarting transaction

寻找死锁记录

通过SHOW ENGINE INNODB STATUS语句查看最近发生的一次死锁信息。

SHOW ENGINE INNODB STATUS;

=====================================
2023-11-20 11:38:59 0x7f3e08433700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 68 srv_active, 0 srv_shutdown, 1075094 srv_idle
srv_master_thread log flush and writes: 1075041
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 38
OS WAIT ARRAY INFO: signal count 36
RW-shared spins 0, rounds 84, OS waits 36
RW-excl spins 0, rounds 66, OS waits 2
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 84.00 RW-shared, 66.00 RW-excl, 0.00 RW-sx
------------------------

# 死锁信息
LATEST DETECTED DEADLOCK
------------------------
# 死锁发生时间和操作系统为当前会话分配的线程编号
2023-11-20 11:29:23 0x7f3e084f6700

# 死锁发生时第一个事务的有关信息
*** (1) TRANSACTION:

# 为事务分配的事务id为1893
# 事务处于活跃状态已经20秒
# 事务现在正在做的操作是starting index read
# 该事务id为1893，比下面的事务id为1894要小，所以这个事务是T1，下面为T2
TRANSACTION 1893, ACTIVE 20 sec starting index read

# 此事务当前执行的语句使用了1个表，为1个表加锁
mysql tables in use 1, locked 1

# 此事务处于LOCK WAIT状态，拥有3个锁结构，2个行锁
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)

# 执行此事务的线程信息
MySQL thread id 220, OS thread handle 139904403855104, query id 21165 58.35.211.9 root statistics

# **发生死锁时正在执行的语句**
SELECT * FROM hero WHERE number = 3 FOR UPDATE

# 此事务当前在等待获取的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `study`.`hero` trx id 1893 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000749; asc      I;;
 2: len 7; hex bd00000135011c; asc     5  ;;
 3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
 4: len 3; hex e89c80; asc    ;;

# 死锁发生时第二个事务的有关信息
*** (2) TRANSACTION:

# 为事务分配的事务id为1894
# 事务处于活跃状态已经11秒
# 事务现在正在做的操作是starting index read
# 该事务id为1894，比下面的事务id为1893要大，所以这个事务是T1，下面为T2
TRANSACTION 1894, ACTIVE 11 sec starting index read

# 此事务当前执行的语句使用了1个表，为1个表加锁
mysql tables in use 1, locked 1

# 此事务拥有3个锁结构，2个行锁
3 lock struct(s), heap size 1136, 2 row lock(s)

# 执行此事务的线程信息
MySQL thread id 221, OS thread handle 139904404121344, query id 21169 58.35.211.9 root statistics

# **发生死锁时正在执行的语句**
SELECT * FROM hero WHERE number = 1 FOR UPDATE

# 此事务已经获取的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `study`.`hero` trx id 1894 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 000000000749; asc      I;;
 2: len 7; hex bd00000135011c; asc     5  ;;
 3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
 4: len 3; hex e89c80; asc    ;;

# 此事务等待获取的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `study`.`hero` trx id 1894 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000749; asc      I;;
 2: len 7; hex bd000001350110; asc     5  ;;
 3: len 7; hex 6ce58898e5a487; asc l      ;;
 4: len 3; hex e89c80; asc    ;;

# **InnoDB决定回滚第二个事务**
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1895
Purge done for trx's n:o < 1891 undo n:o < 0 state: running but idle
History list length 16
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421379820700200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421379820699280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421379820698360, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1893, ACTIVE 596 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 220, OS thread handle 139904403855104, query id 21176 58.35.211.9 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
431 OS file reads, 1219 OS file writes, 380 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.17 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 16434133
Log flushed up to   16434133
Pages flushed up to 16434133
Last checkpoint at  16434124
0 pending log flushes, 0 pending chkp writes
113 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 223365
Buffer pool size   8191
Free buffers       7396
Database pages     787
Old database pages 270
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 406, created 381, written 888
0.00 reads/s, 0.00 creates/s, 0.17 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 787, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=139904543950592, state: sleeping
Number of rows inserted 21864, updated 0, deleted 0, read 32491
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

优化死锁

• 优化语句改变加锁顺序；
• 建立合适的索引改变加锁过程。

查找过程：

1. 找到发生死锁时各个事务正在执行的语句。
2. 然后去自己的业务代码中找到语句所在事务的其他语句。
3. 最后对照着各个事务获取到的锁和正在等待的锁的信息来分析死锁发生过程。
   1. 根据发生死锁的语句正在等待的锁的记录，找到记录的锁被哪条语句获取了；
   2. 调整事务对记录的加锁顺序。

SHOW ENGINE INNODB STATUS只能显示最近一次发生的死锁信息。可以设置系统变量innodb_print_all_deadlocks为ON，这样可以将每个死锁发生的信息都记录在MySQL错误日志中。

阅读学习《MySQL是怎样运行的》。