mysql innodb 的锁机制_MySQL InnoDB事务，锁机制

最新推荐文章于 2022-06-09 13:03:12 发布

weixin_29454359

最新推荐文章于 2022-06-09 13:03:12 发布

阅读量168

点赞数

文章标签： mysql innodb 的锁机制

本文链接：https://blog.csdn.net/weixin_29454359/article/details/113429677

版权

InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。下面我们先介绍一点背景知识，然后详细讨论InnoDB的锁问题。

InnoDB实现了以下两种类型的行锁。

l 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

l 排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

值得注意的是：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

判断一条SQL会对哪些记录上锁之前需要确认几个条件：

前提一：id列是不是主键？

前提二：当前系统的隔离级别是什么？

前提三：id列如果不是主键，那么id列上有索引吗？

前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？

前提五：两个SQL的执行计划是什么？索引扫描？全表扫描

RC隔离级别下针对delete from t1 where id = 10 语句：

主键索引，只在对应的主键索引上加排他锁即可

唯一索引，在对应的唯一索引及主键索引上都加上排他锁，防止通过主键定位到该条数据的SQL拿到锁修改数据；一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

非唯一索引，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

如果id列上无索引，那么将会根据聚簇索引搜索全表，筛选出记录，表中所有行都将被锁住；这是因为在Mysql中如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

RR隔离级别的加锁规则基本和RC级别一致，只不过在非主键索引下，会新增间隙锁，防止幻读；特别注意：如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

复合索引与锁

复合索引是根据最左匹配原则来筛选数据的：

下面看一个简单的复合索引，key index_name_cid ('name','cid'),

以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

那么什么时候才能用到呢?

当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。

这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)。

再看一条复杂的SQL在RR级别下的加锁情况：

根据复合索引筛选规则，idx_t1_pu只会用到puptime上的索引(index-key)，所以InnoDB通过索引只能过滤出pubtime3,5,10这3条记录，条件userid = 'hdc'则由索引过滤器(index-filter)过滤，何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在存储引擎上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而comment is not NULL这个Table Filter对应的过滤条件，则在聚簇索引中读取数据后，在MySQL Server层面过滤，同时聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

最后总结，由于InnoDB的锁都是加在索引上，所以所有加锁的过程均是在存储引擎这一层实现的；如果有些数据无法在存储引擎这一层过滤掉(Index-Filter在哪一层过滤由MySQL版本觉得)，那么InnoDB就会把这些数据统一全加上锁丢给Mysql Server，最后由MySql Server完成过滤；所以在高并发的场景下，为了减少锁的开销，需要尽可能在存储引擎这一层过滤条所有条件得到对应数据。

参考资料：

http://hedengcheng.com/?p=771

http://blog.csdn.net/xifeijian/article/details/20313977