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

107次阅读

共计 2226 个字符，预计需要花费 6 分钟才能阅读完成。

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

正文完