212_mysql_innodb_6_lock1

1  解决并发事物带来问题的两种基本方式

1.1读 – 读情况 本身读操作，对记录无影响

1.2 写 – 写情况: 多个事物对同一条记录修改，不能有脏写，就得排队，一种锁结构（内存中）

    Trx 信息：表示这个锁结构与哪个事物关联

     Is_waiting : 表示当前是否是否在等待

 T1事物修改该记录，生成一个锁结构与记录关联，之前没有别的事物给这个记录加锁，is_waiting false 表示获取锁成功, 如果是 true 表示获取锁失败

1.3 读写/写读情况

 通过隔离级别进行限制 避免 脏读/幻读/不可重复读，也通过以下两种方式

1. MVCC
2. 读写操作通过锁方式

 1.4 一致性读（consistent read）

事物利用MVCC进行的读取操作称为一致性读（快照读/一致性无锁读），所有的select在（RC/RR模式下）都是一致性读，不会对表中记录加锁，其它事物可以对记录进行改动

1.5 锁定读  

有时候读取记录时就希望获得X锁，避免其他事物的读写 称之为 锁定读

     1.5.1 共享锁&独占锁

1. a) 共享锁（shared lock） S 锁，事物读取一条记录时候，先获取该记录的S锁 对事物加 S锁select xxx lock in share mode;
2. b) 独占锁 (exclusive lock) X 锁，事物改动一条记录， 先获取该记录的 X锁   对事物加 X 锁select xxx  for update;

1.6 写操作 (delete / update / insert)

        Delete： 1 先在B+树中定位到记录 2 获取该记录X锁 3 执行delete mark 操作

        Update：

a 存储空间不变：1先定位 2 再获取X锁，

b 存储空间改变：1 先定位B+位置 2获取X锁， 3删除该记录（加入垃圾链表） 4 插入新记录

          c 修改了键值： 1 先进行delete操作 2 再进行 insert操作

insert 新插入一条记录受隐式锁保护 不在内存中生成内存锁结果

2 多粒度锁

   行锁/表锁  &  S / X 锁

   意向锁（intention lock）

        意向共享锁（intention shared lock）当事物为某条记录加S锁，优先加IS锁  属于表级锁

        意向独占锁（intention exclusive lock）当事物为某条记录加X锁，优先IX 锁属于表级锁

3 mysql中的行锁和表锁

   3.1Myisam memory merge 只有表锁

   Innodb 表级别 S/X锁，如果dml(select/insert/delete/update)不会有表锁（S/X）， 如果DML &DDL 会有锁，且是MDL锁

某个事物对某个表 执行 select insert delete update时候，其它回话执行 ddl语句会发生阻塞，是在server层通过 MDL(Metadata lock) 实现的，不会用innodb的 S/X锁

3.2 Innodb表级锁比较鸡肋，但是提供了手动方式

 a LOCK TABLES t READ   # innodb会对t表加 S 锁

 b LOCK TBALES t WRITE  # innodb 会对 t 表加X 锁

3.3 表级别AUTO_INC锁 （针对auto_increment修饰的列递增实现的两种方式）

  A 采用 AUTO-INC锁  插入表级锁 AUTO-INC锁，然后给每天待插入AUTO_INCREMENT修饰的列分配递增的值，语句执行后，释放AUTO-INC锁 （未释放，其它事物等待）

  B 采用轻量级的锁 未插入语句生成 AUTO_INCREMENT 修饰的列的值时获取这个轻量级锁（这个自增列更新完就释放锁）

参数  show VARIABLES like "%innodb_autoinc_lock_mode%"

innodb_autoinc_lock_mode 0  采用AUTO_INC锁
innodb_autoinc_lock_mode 2 采用轻量锁
innodb_autoinc_lock_mode 1 混着用

3.4 innodb中的行锁

A Record lock 记录锁,仅仅把一条记录锁上(LOCK_REC_NOT_GAP) S/X 两种record lock

B Gap lock   仅仅是为了防止插入幻读记录而设计的锁，记录加了GAP锁不会阻碍其它事物对该记录加recordlock或者继续加gap

在 id=30 上加了gap 锁，意味着 （1,30）区间内不允许立即插入新记录，直到gap锁释放,

MVCC+GAP_LOCK 很大程度上解决了RR模式下的幻读, 同时注意，为了防止（100, +无穷）在supreMum上加gap锁

C Next-key Lock （LOCK_ORDINARY）record + gap lock，既锁住某条记录，同时锁住前面的间隙 , id=30 上加了NKL 锁，意味着(1,30]上锁

D Insert Intention Lock (lock_insert_intention插入意向锁)

事物A 插入记录时候，需要判断 gap 和 next-key-lock 锁，如果有需要等待到释放，处于等待状态也需要一种锁（insert intention lock）

当T1 提交后，T2 /T3 就能获得插入意向锁 （本质就是获得把 插入意向锁对应的锁结构的 is_waiting 属性改为false）

E 隐式锁 一般正常情况下 insert不会产生锁

为了避免脏写现象，trx_id发挥作用

1 聚簇索引： 如果A事物插入一条记录，该记录的trx_id就是A事物id, 如果B事物想对该记录加S/X锁
A 先看该记录的trx_id 是否是活跃事物
B 如果是活跃事物，就给当前A事物创建锁结构（is_waiting 属性为false）, 然后为自己B创建一个锁结构（is_waiting属性为false）进入等待状态

2 二级索引： 无trx_id,但二级索引页 page_header有 PAGE_MAX_TRI_ID属性 功能同trx_id
A 如果PAGE_MAX_TRI_ID < B事物id ,说明该页面已经提交
B 如果 PAGE_MAX_TRI_ID > B 事物ID，说明活跃事物,需要定位到具体聚簇索引后回表根据聚簇索引的 trx_id情况进行后续操作

综上 insert不会主动生成锁，但是由于trx_id存在，如果针对某条记录其它事物要做修改，其它事物B 替 A 生成了锁，B自己进入锁等待状态

隐式锁起到了延迟生成锁的作用，且对用户透明

4 innodb 锁的内存结构

  符合下面条件的，这些记录的锁可以放在同一个锁结构中

A 在同一个事物中进行加锁操作
B 被加锁的记录在同一个页面中
C 加锁的类型是一样的
D 等待状态是一样的

1）    锁所在事物信息： 无论行锁/标锁，一个锁都属于一个事物
2）    索引信息： 对于行级锁，记录锁的记录属于哪个索引
3）    表/行锁信息： a) 行锁记载：表空间+页号+比特（一条记录对应一个bit位），  b) 表锁 记录具体的表+其它信息
4）    Type_mode 32比特： lock_mode + lock_type + rec_lock_type

例： T1事物想给 表空间77号，13页面上，id =115加S锁，

• 事物信息 就是T1 TRX_ID
• 在聚簇索引加锁，索引信息就是 primary索引
• 行锁 （spaceid 77， pageNo 113, n_bit 具体某条记录） n_bits = (1 + (n_rec + LOCK_PAGE_BITMAP_MARGIN 64 ) /8 ) * 8
• Type mode: lock_mode S锁， lock_type: LOCK_REC; rec_log_type: LOCK_REC_NOT_GAP, IS_WAITING: FALSE
• 比特位：记录具体某条记录位置

5 语句加锁分析

   不同隔离级别加锁行为

 RC: 大部分是记录锁,在有外键时可能有所出入。

 RR : 加锁的粒度是NKL

在RR级别下的加锁细节

原则 1：加锁的基本单位是 next-key lock。并且next-key lock 是前开后闭区间。(5,10]

原则 2：查找过程中访问到的索引才会加锁

原则 3：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁(前提是这个数据在表中有)

原则 4：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁(GAP)

8019之前bug: 唯一索引上的范围查询会访问到不满足条件的第一个值为止

5.1 普通select语句

A RUC/RC 不加锁
 		B RR 不加锁，第一次select生成 readview 很大程度上避免，有一类情况特殊； readview 不能阻止A事物执行update/delete 来改动这个B事物插入的记录
A 事物
set autocommit = 0;
SELECT * from t_name where id = 3; 
UPDATE t_name set name = "sumi2" where id =3;
SELECT * FROM t_name where id =3 ; 
commit;

B 事物 insert into t_name values (3, “sumi”)
         
C SERIALIZABLE  
a）autocommit=0时 普通select 会被转成 select xxx lock in share Mode 语句，读取前加S锁，
b）autocommit =1 普通select仅使用MVCC生成readview来读取记录, 因为自动提交以原则，一个事物只有一条SQL

5.2 锁定读的语句 

	Sql1 & sql2 是传统锁定读的SQL语句， sql3 & sql4 首先定位到要修改的记录，也是一种锁定读的模式
		Sql1 select xxx lock in share mode;
		Sql2 select xxx for update;
		Sql3 update xxx
		Sql4 delete xxx

    匹配模式  &  唯一性搜索

A 匹配模式 (精确匹配 & 非精确/区间匹配)
B 唯一性搜索 扫描区间最多只包含一条记录（精确匹配, 主键/二级唯一索引（搜索条件不能是 索引列 is null）， 联合索引（每个列都要用到 uk（a,b） where a=xx and b=xx））
		
给记录加锁很多限制（隔离级别，涉及的索引，是否精确匹配，是否唯一性搜索， 具体SQL（select insert，update delete等））具体流程

1）	B+树中 定位到扫描区间中第一条记录（可能是二级索引/ 聚簇索引等）
2）	为该记录加锁 小等于RC 会给记录加 record-lock , 大等于RR 会加 next-key lock 
3）	判断索引下推条件是否成立（索引下推/减少IO）只有select 涉及索引下推且仅限二级索引, 如果符合ICP(INDEX CONDITION PUSHDOWN)就下推，不符合继续链表下一个寻找
4）	执行回表操作，获取聚簇索引记录并给聚簇索引添加 record-lock
5）	判断边界条件是否成立，如果成立执行 6 ，否则 在小等于RC时，立即释放该记录上的record-lock, 大等于RR 不释放record-lock, 
6）	Server层判断其余条件是否满足条件，如果满足 返回客户端，否则 在小等于RC时，立即释放该记录上的record-lock, 大等于RR 不释放record-lock
7）	获取链表下一条记录，重复步骤2

Select * from t_name where id >1 and id <100 lock in share mode;

如果涉及二级索引

a)	Rc:二级索引+ S 锁， 聚簇索引+S锁（不符合条件的均先加锁/后释放，对于边界条件不符合下推的条件的二级索引，不会释放锁， innodb 无权利自动解锁，只有server层有权利）
b)	RR: 二级索引 + S next-key锁， 聚簇索引+ S record-lock (二级索引不符合条件的记录均未释放 next-key锁)

Update/delete SQL 加锁方式 与  SELECT XXX FOR UPDATE 语句类似，如果更新了二级索引，二级索引需要X锁

Udpate t_name set name =”sumi” where id > 1 and id <=15 and name =25(name是索引)

扫描聚簇索引（1 ,15] 加 next-keylock，对应的二级索引 加 record lock

备注：

如果精准匹配，小等于rc不会为扫描区间后面的下一条记录加锁, 大等于rr 会为扫描区间后面下一条记录加gap锁

如果是非精准匹配，大于等rr级别 会给下一条+ next-key锁

5.3 半一致读的语句（Semi-consistent Read）夹在一致性读和锁定读之间的读取方式

        当 小等于RC 且执行update 将使用半一致性读, 减少阻塞，当A 事物持有 id=8的X锁，B事物需要update id=8, innodb会将A事物 id =8 最新版本返回给server判断，如果不符合条件，就放弃为该行上X锁

    5.4 INSERT语句

     Insert 一般不会产生锁，特殊情况：

1 遇到重复键（duplicate key） 会有报错 ，报错前还得加锁 小等于RC 加S record lock, 大等于RR 加 next-lock

2 外键检查 (检查成功，给父表加 S record-lock， 检查失败 rc 不加锁，rr 加gap锁)