MySQL InnoDB Lock（二）

MySQL轻松学

发布于 2018-03-09 15:18:11

3.5K0

发布于 2018-03-09 15:18:11

文章被收录于专栏：MYSQL轻松学

MySQL InnoDB Lock主要从5个部分介绍，这篇文章承接 上一篇 ，会详细介绍后3部分。

InnoDBLock基础知识

InnoDB锁按照级别分：

表级别（MySQL Server提供）MySQL Internal Lock或意向锁

行级别（InnoDB存储引擎提供）

InnoDB表级别锁按照操作性分：

IS Lock（意向共享锁）：在对表的记录获取S lock前，表必须先获取表级别的IS锁或者更高级别的锁。

IX Lock（意向排它锁）：在对表记录获取X lock前，表必须先获取到表级别的IX锁

自增lock (AUTO-INC lock)

5.1.22后引入了轻量级互斥自增长机制。

innodb_autoinc_lock_mode=1(default)。

根据Insert类型为simple-insert，bulk-insert（INSERT…SELECT,REPLACE…SELECT,LOAD DATA），mixed-mode-insert，innodb_autoinc_lock_mode=(0,1,2)

0=所有insert采用传统AUTO-INC机制；

1=bulk-insert采用轻量级；

2=所有insert采用轻量级（但是replication只能row-base）

InnoDB行锁按照影响范围分：

Record Lock：锁定单条记录（基于主键的扫描或基于唯一辅助Index的扫描只有record lock，无gap lock）

Gap Lock：锁定一个范围的记录，但不包括记录本身（基于非唯一辅助Index的扫描，有record lock和gap lock，也就是Next Key Lock，范围为(pre_nextkey,nextkey) ）

Next Key Lock：锁定一个范围的记录，并且包含记录本身。MySQL在RR事物隔离级别下，通过Next Key Lock解决幻读问题。

Note：MySQL InnoDB行级锁，其实是index记录锁（Oracle是block锁）

RR事物级别下，基于无Index列扫描的修改（delete、update）操作，将锁定所有行记录，现象像lock了整个表。

1. 主键Record lock

2.唯一 Index Record lock

3. 非唯一Index Next Key Lock

4.无Index 全记录Lock

RR一致级别下SQL对应InnoDB Lock情形

Select … from 无lock，SERIALIZABLE存在S Next-Key lock
Select … from… lock in share mode存在S Next-Key lock
Select… from… for update存在X Next-Key lock
Insert… values(…)存在X row record lock；对于存在auto_increment列，存在X lock在auto_increment列的index，特殊的表级AUTO-INC lock；如果insert产生duplicate-key错误，则在duplicate index record设置S lock，如果多个session插入同一行则可以产生deadlock（示例Insert duplicate）（5.1版本，deadlock会等待，5.6deadlock快速释放，无需等innodb_lock_wait_timeout
Insert into T select … from S where…存在T表的X row record lock，S表存在S的Next-Key lock
Create table … select…from S，S表存在S的Next-Key lock
Replace…如果无主键冲突，X row record lock，否则X Next-Key lock
Update… where存在X Next-Key lock
Delete from… where存在X Next-Key lock
表存在Foreign Key，表的Insert、Update、Delete，存在S的record lock
Lock table是显示增加一个表级lock

Note：RR隔离级别下，为了减少Next-key lock可以设置innodb_locks_unsafe_for_binlog=1（不建议），就是disable Next-Key lock，或者修改事物隔离级别为RC（建议）

示例Insert duplicate

InnoDB Lock定位分析

直接使用show engine innodb status不能获取lock的根源
使用mysqladmin debugmysqladmin -S /DATA/mydata/mysql.sock debug
使用innodb_lock_monitor需要创建表：（任意DB中，不使用时drop此表）CREATE TABLE innodb_lock_monitor(a INT) ENGINE=INNODB; InnoDB monitor有：innodb_monitor，innodb_lock_monitor，innodb_table_monitor，innodb_tablespace_monitor，开启后定期执行将结果输出到errorlog（前2个20s，后两个60s，Note：不使用时注意drop相关表，停止monitor）
5.5版本以后，使用information_schema中相关表TRANSACTIONS.mht