MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁

如果在搜索中使用了辅助索引(secondary index)，并且在辅助索引上设置了行锁，则，InnoDB还会在 相应的 聚集索引 上设置锁;表未定义聚集索引时，InnoDB自动创建隐藏的聚集索引(索引名字是GEN_CLUST_INDEX)，当需要在聚集索引上设置锁时，就设置到此自动创建的索引上。

（二）、RR时，如果使用了唯一索引的唯一搜索条件，InnoDB只在满足条件的索引记录上设置index record lock，不锁定索引记录前面的间隙;如果用唯一索引作范围搜索，依然会锁定每一条被扫描的索引记录前面的间隙，并且再在聚集索引上设置锁。

（三）、RR时，在第一个不满足搜索条件的索引记录上设置gap lock或next-key lock。

一般，等值条件时设置gap lock，范围条件时设置next-key lock。此gap lock或next-key lock锁住第一个不满足搜索条件的记录前面的间隙。

（四）、RR时，INSERT在插入新行之前，必须首先为表上的每个索引设置insert intention lock。

每个insert intention lock的范围都是(待插入行的某索引列的值, 此索引上从待插入行给定的值向下的第一个索引值)。只有当insert intention lock与某个gap lock或next-key lock冲突时，才能在performance_schema.data_locks看到insert intention lock。

（五）、RC时，InnoDB只在完全满足WHERE条件的行上设置index record lock。

（六）、RC时，禁用了gap lock。

正因为此，RC时不存在gap lock或next-key lock。这是为什么呢?我们想一想啊，gap lock是用来解决phantom row问题的，gap lock封锁的区间内不能插入新的行，因为插入时的insert intention lock会和gap lock冲突，从而阻止了新行的插入。但，隔离级别RC是允许phantom row的，因此RC时gap lock是被禁用的。

（七）、RR或RC时，对于主键或唯一索引，当有重复键错误(duplicate-key error)时，会在 重复的索引记录上 设置 shared next-key lock或shared index record lock。这可能会导致死锁。

假设T1, T2, T3三个事务，T1已经持有了X锁，T2和T3发生了重复键错误，因此T2和T3都在等待获取S锁，这个时候，当T1回滚或提交释放掉了X锁，则T2和T3就都获取到了S锁，并且，T2和T3都请求X锁，“T2和T3同时持有S锁，且都在请求X锁”，于是死锁就产生了。

好了，规则都列出来了，是时候实践一把了。下面在展示锁时，我们同时指出了当前所使用的隔离级别，表上的索引以及事务的SQL语句。

实践一：搜索时无法使用索引，即全表扫描时，InnoDB在表的全部行上都加锁

上图演示了：搜索条件无法使用索引时，InnoDB不得不在表的全部行上都加锁。所以，索引实在太重要了，查询时，它能加快查询速度;更新时，除了快速找到指定行，它还能减少被锁定行的范围，提高插入时的并发性。

实践二：唯一索引和非唯一索引、等值查询和范围查询加锁的不同

搜索时使用 唯一索引 作等值查询时，InnoDB只需要加index record lock;搜索时使用 唯一索引作范围查询时 或 使用非唯一索引作任何查询时 ，InnoDB需要加next-key lock或gap lock。

示例1演示了：使用非唯一索引 idx_c 搜索或扫描时，InnoDB要锁住索引本身，还要锁住索引记录前面的间隙，即next-key lock: X 和 gap lock: X,GAP。next-key lock既锁住索引记录本身，还锁住该索引记录前面的间隙，gap lock只锁住索引记录前面的间隙。等值条件时，在最后一个不满足条件的索引记录上设置gap lock。

示例2演示了：使用唯一索引 iux_b 的唯一搜索条件，即，使用唯一索引执行等值查找时，InnoDB只需锁住索引本身，即index record lock: X, REC_NOT_GAP，并不锁索引前面的间隙。

示例3演示了：使用唯一索引 iux_b 进行范围扫描时，依然需要锁定扫描过的每一个索引记录，并且锁住每一条索引记录前面的间隙，即next-key lock: X。范围条件时，在最后一个不满足条件的索引记录上设置next-key lock。

实践三：不同隔离级别加锁的不同

无论何种隔离级别，SQL语句执行时，都是先由InnoDB执行索引扫描，然后，返回结果集给MySQL服务器，MySQL服务器再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集。

（编辑：好传媒网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!