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

发布时间：2021-12-20 10:10 所属栏目：44 来源：互联网

导读：这篇文章给大家介绍如何进行MySQL INNODB存储引擎中各类型SQL加锁分析，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。 MYSQL5.6 事物隔离级别：RR innodb_locks_unsafe_for_binlog=OFF I NSERT: 当前事务记为 TX1 ① 判断是否主键或者

这篇文章给大家介绍如何进行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加锁分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

（编辑：ASP站长网）