最近在看一些关于消息队列和数据仓库的书,越来越发现,作为一个DBA,只涉猎某几个数据库是远远不够的,在云服务横行的年代,提升自己知识的精度和广度是必不可少的。
增删改查都会用到啥锁?
最近比较痴迷于MySQL中的锁,感觉这块儿要弄明白还是要费很多功夫的,我想着等到研究的差不多了,写一篇高质量的文章,详细说说innodb中的锁。
今天主要解决上面的问题,就是日常的操作中,增删改查都会使用什么类型的锁?其实这个问题,可以分为两个方面,一方面是读,一方面是写。
01
读(select)
我们先来看读的部分。读的操作,其实分为两种,分别是一致性读和锁定读,在之前的文章中,我们简单提过。传送门在这里:
这里我们温习一下,一致性读其实就是利用事务的MVCC机制(关于MVCC机制,请移步:MySQL之MVCC初探(1)),来读取一份数据的快照,所以有的书上也称之为快照读,一致性读是不加锁的,其他的事务是可以对表中的数据记录进行改动的。一般情况下,常见的读,例如:
select * from table;
select * from table left join table2;
这种操作,在RU,RC,RR隔离级别下都是采用一直性读,不加锁的操作。这种情况下,读的并发可以非常高。
再来看看锁定读,如果我们的表当中有索引,我们想在读取记录的时候,获取某一条记录的锁,禁止别的事务对这条记录进行修改,那么我们可以使用下面的语句来对读取的记录加锁:
select ... lock in share mode;加共享锁。(其他事务可读,不可写)
select ... for update;加排它锁。(其他事务不可读,不可写),这样,其他事务就不能对这条记录进行读取和更改了。
关于读操作的是否加锁,还有以下几点需要注意:
1、在Serializable这种事务的隔离级别下,普通的select操作会升级为select...in share mode;的模式。
2、在唯一索引上使用唯一的查询条件,会使用记录锁,而不会封锁记录之间的间隔,即不会使用间隙锁。
3、其他类型的索引使用范围的查询条件或者唯一的查询条件,innodb会自动锁定被扫描的范围,避免索引范围区间内插入新的记录。这块儿可能比较模糊,文章最后面给出各种类型下的加锁测试结果。
02
写(update、delete、insert)
关于delete
对一条数据做delete的过程实际上是要先在索引的B+树上获取该记录的位置,然后再这个记录所在的位置加X锁,也就是排它锁。
如果对某个范围内的数据做delete操作,则会在索引B+树上对范围内符合查询条件的记录以及记录之前的区间加next-key锁(本质是记录锁和间隙锁的组合,后面的文章会讲到)。
加完锁之后,再进行delete操作。这个delete操作的本质,其实是先将delete的标识为标识为1,而不是真正进行删除,如果下次这块空间可以复用,则innodb会直接进行复用。
更多详情请见:Innodb数据页简介(2)。
关于update
对一条记录做update的时候,我们知道,如果该要更新的列在更新前后的存储空间没有发生变化,则会直接在该记录上进行更新操作。而如果发生了存储空间的变化,则会现将这条记录彻底删除掉,然后再插入一条新的记录。
基本上分为一下三种情况:
1、如果update操作没有更新索引键值并且没有导致存储空间变化,则会直接在索引B+树上使用X锁来锁定update的记录。
2、如果update操作没有更新索引键值但却导致了数据的存储空间发生变化,则会现将这表数据记录删除掉,然后再插入一条新的记录,在这个过程中,先会获取索引B+树的X锁,然后insert过程会使用隐式锁来进行保护。
3、如果update修改了某条记录的索引键值,则需要先进行delete,然后再进行一次insert,加锁的规则就和delete以及insert一样了。
这里有几点需要注意:
1、如果在唯一索引上使用唯一的查询条件来进行update和delete操作,那么这个过程中只会对记录加锁。
2、除了第一种情况之外,都会加排他的next-key锁,来锁定记录和记录之前的范围。
3、如果update的是主键的记录,则对应的普通索引的记录也会被隐式加锁,这是因为innodb中的普通索引会存储主键的值,检索普通索引本质上要进行回表操作,二次扫描聚集索引。
关于insert
insert操作会用排它锁封锁被插入的索引记录,而不会封锁记录之前的范围。除此之外,会在插入区间加入插入意向锁(关于这个锁,后面会有文章专门讲述,现在只需要知道有这么个锁就可以了)
最后,今天我做了一点测试,测试的数据太多了,不方便整理,这里把测试结果放在这里,大家可以看看,和自己设想的情况一样不一样:
(注意:所有测试均在RR隔离级别下,RC隔离级别下只有记录锁,没有间隙锁,相对比较简单,大家可以自行研究)
RR隔离级别下,如果会话1锁定了一个空的记录,例如id=6的记录,表中只有id=5和id=9的值,那么会话2中不能插入id=6、7、8的值,因为这个间隙已经被锁定。其中,id可以是主键或者唯一索引。
RR隔离级别下,如果会话1锁定了一个存在记录,例如id=5的记录,表中有id=5的值,那么会话2中可以插入id=4、6、7、8的值,间隙没有锁定。其中,id可以是主键或者唯一索引。
RR隔离级别下,如果会话1锁定了一个范围记录,例如id<6的记录,表中有id=5的值和id=9的值,那么会话2中不能插入id=6、7、8的值,间隙被锁定。其中,id可以是主键或者唯一索引。
RR隔离级别下,如果会话1锁定了一个范围记录,例如id>6 and id <11的记录,表中有id=5的值和id=9的值,那么会话2中不能插入id=6、7、8的值以及id大于9的所有值,间隙被锁定。其中,id可以是主键或者唯一索引。
RR隔离级别下,如果会话1锁定了一个空的记录,例如id=6的记录,表中有id=5的值和id=9的值,那么会话2中不能插入id=5、6、7、8的值,间隙被锁定。但是可以插入9的值,其中,id是普通索引。
RR隔离级别下,如果会话1锁定了一个存在记录,例如id=5的记录,表中有id=5的值和id=9的值,那么会话2中不能插入id=4、6、7、8的值,但是可以插入9的值。间隙被锁定。其中,id是普通索引。
RR隔离级别下,如果会话1锁定了一个范围记录,例如id<6的记录,表中有id=5的值和id=9的值,那么会话2中不能插入id=4、6、7、8的值,但是可以插入9的值,间隙被锁定。其中,id是普通索引。
RR隔离级别下,如果会话1锁定了一个范围记录,例如id>6 and id<11的记录,表中有id=5的值和id=9的值,那么会话2中不能插入所有值的记录,所有间隙被锁定,类似全表锁。其中,id是普通索引。