如何进行MySQL INNODB存储引擎中各类型SQL加锁分析

这篇文章给大家介绍如何进行MySQL INNODB存储引擎中各类型SQL加锁分析，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

MYSQL5.6
事物隔离级别：RR
innodb_locks_unsafe_for_binlog=OFF

I
NSERT:

当前事务记为 TX1 
① 判断是否主键或者唯一建冲突

  YES ， 则请求 隐式锁->显式锁   X record lock ->  S record lock
  1> 记录已经存在于表中，返回 Duplicate 错误(持有 S record lock 待事务(TX1)commit 或者 rollback之后释放)
  2> 记录还在其他的事务(TX2)中处于未提交状态(请求 S record lock)，此时称之为 主键或者唯一建冲突的不确定状态(之后update也会出现这种情况)
  1. 若事务(TX2)提交，则 TX1 返回 Duplicate 错误(持有 S record lock 待事务(TX1)commit 或者 rollback之后释放)
  2. 若事务(TX2)回滚，则 TX1 获取 S record lock 成功，进而 跟 以下情况一致

  NO ， 请求 Itention INSERT LOCK( gap ) ， 其中 next_key lock ,gap lock , record lock(S X都会) 会阻塞之
  1> 若未被阻塞，获取到 Itention INSERT LOCK( gap )，进而获取到 X record lock
  2> 若被阻塞 ，则等待

PS : 其中 INSERT 语句会产生的锁类型有  INSERT Itention LOCK(gap) , X record lock , S record lock(主键冲突会将 X 转为 S的申请) , IX(意向写锁 表层面的，这里分析都会忽略掉) , autoinc lock (自增锁，表级，之后会有分析)

UPDATE：

PK—–主键
UK—–唯一建
NK—–非唯一索引
OTHER–无索引字段

1. 条件为 PK
① update tb set pk=new where pk=old;
② update tb set uk=new where pk=old;
③ update tb set nk=xxx where pk=old;
④ update tb set other=xxx where pk=old;

pk 加上 X record lock 以及对应的 uk 加上 X record lock

对①操作而言， pk=old pk=new uk=tb(pk) 共3个 X record lock
对②操作而言， pk=old uk=new uk=tb(pk) 共3个 X record lock
对③操作而言， pk=old uk=tb(pk)  共2个 X record lock
对④操作而言， pk=old uk=tb(pk)  共2个 X record lock

2. 条件为 UK
① update tb set pk=new where uk=old;
② update tb set uk=new where uk=old;
③ update tb set nk=new where uk=old;
④ update tb set other=new where uk=old;
同上

3. 条件为 NK
① update tb set pk=new where nk=old;
② update tb set uk=new where nk=old;
③ update tb set nk=new where nk=old;
④ update tb set other=new where nk=old;

对①操作而言
1> nk 未匹配到记录，nk 加一个 gap lock , 开区间
2> nk 匹配到记录
1. 主键冲突(分确定状态和不确定状态) ， nk 加 X record LOCK ， 对应主键加 X record lock , 主键 S record lock ， 不对 唯一键加锁
2. 无主键冲突 ， nk 加 X record LOCK ，pk=old pk=new 加 X record lock ,nk 两侧加 x gap lock , uk X record lock , uk S record lock , uk S gap lock
对②操作而言
1> nk 未匹配到记录 ， nk 加一个 gap lock ， 开区间
2> nk 匹配到记录 
1. 唯一建冲突(分确定状态和不确定状态) ， nk 加 X record LOCK ， 对应主键加 X record lock , 唯一键 S record lock
2. 无唯一建冲突 ， nk 加 X record lock ， 对应主键加 X record lock , uk=new 和 uk=old 加 X record lock
对③操作而言，
1> nk 未匹配到记录 ， nk 加一个 gap lock ， 开区间
2> nk 匹配到记录 ， nk加一个 X record lock , nk 两侧加 gap lock 开区间， 对应主键 加 X record lock
对④操作而言
1> nk未匹配到记录 , nk 加一个 gap lock ，开区间
2> nk 匹配到记录 ,  nk加一个 X record lock ， nk两侧加 gap lock 开区间 ，对应主键 加 X record lock  

对于相同gap , X gap lock 直接是不冲突的

4. 条件为 OTHER
① update tb set pk=new where other=old;
② update tb set uk=new where other=old;
③ update tb set nk=new where other=old;
④ update tb set other=new where other=old;

所有主键记录加 X record lock 以及 所有gap 加 X gap lock。

关于如何进行MySQL INNODB存储引擎中各类型SQL加锁分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。