Innodb锁机制

黄勇波

InnoDB中的锁

数据库系统使用锁是为了支持对共享资源的并发访问,提供数据的完整性和一致性。数据库中的锁主要lock和latch之分。其中latch成为闩锁(轻量级锁),latch又分为mutex(互斥量)和rwlock(读写锁),其目的是用来保证并发线程操作共享资源的正确性,通常无死锁检测机制;lock的对象是事务,用来锁定数据库中的对象,如表、页、行,一般在commit或rollback后释放,且通常有死锁机制。lock和latch的区别如下:

Lock Latch
对象 事务 线程
模式 行锁、表锁、意向锁 读写锁、互斥量
死锁 通过waits-for graph、time out等机制进行死锁检测和处理 无死锁检测与处理机制。仅通过应用程序加锁的顺序保证无死锁的情况发生
存在于 Lock Manager的哈希表中 每个数据结构的对象中

查看innoDB中的latch信息:show engine innodb mutex,输出说明:

名称 说明
count mutex被请求的次数
spin_waits spin lock(自旋锁)的次数,innoDB在不能获取锁时首先进行自旋,若自旋后仍不能获取锁,则进入等待状态
spin_rounds 自旋内部循环的总次数,每次自旋的内部循环是一个随机数。spin_rounds/ spin_waits表示平均每次自旋所需的内部循环次数
os_waits 表示os等待的次数。当spin lock通过自旋还不能获得latch时,则进入os等待状态,等待被唤醒
os_yields 进行os_thread_yield唤醒操作的次数
os_wait_times os等待的时间,单位ms

查看lock信息:show full processlist、show engine innodb status或查询information–schema下表innodb–trx、innodb–locks、innodb–lock–waits

锁的类型

行级锁

Innodb实现了两种标准的行级锁,共享(S)锁和排他(X)锁:

共享(S)锁:允许事务读一行数据。

排他(X)锁:允许事务删除或更新一行数据。

如果事务T1持有行r上的一个S锁,那么其他事务仍然可以持有行r上的S锁,但不能持有行r上的X锁;如果事务T1持有行r上的一个X锁,则其他事务不能再持有行r上的任何锁。也就是说,X锁与任何锁都不兼容,S锁与S锁兼容。S和X都是行锁,因此兼容均指对相同行(Row)锁的兼容情况。

意向锁

InnoDB存储引擎支持多粒度锁定,这种锁定允许事务在行级上的锁定和表级上的锁定同时存在。这种锁称为意向锁,意向锁将锁定的对象分为多个层次。

将加锁的对象看成一棵树,那么对最下层的对象上锁,也即最细粒度的对象,那么首先需对粗粒度的对象上锁。如:若需要对页上的记录r行上X锁,那么分别需要对数据库A、表、页上意向锁IX,最后才对记录r上X锁。其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。 alt Innodb存储引擎的意向锁设计比较简练,其意向锁为表级别的锁,因此意向锁不会阻塞除全表扫描外的任何请求。主要有两种意向锁:

意向共享锁(IS lock):事务想要获得一张表中某几行的共享(S)锁,必须先获取该表的IS锁。

意向排他锁(IX Lock):事务想要获得一张表中某几行的排他(X)锁,必须先获得该表的IX锁。

Innodb存储引擎中锁的兼容性:

IS IX S X
IS 兼容 兼容 兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S 兼容 不兼容 兼容 不兼容
X 不兼容 不兼容 不兼容 不兼容

注意:IX,IS是表级锁,不会和行级的X,S锁发生冲突,只会和表级的X,S发生冲突。

考虑如下场景:

● SessionA申请IX,同时申请行级X

● SessionB申请IX,同时申请行级X

由于IX锁与IX锁兼容,因此SessionA和SessionB都能成功申请IX锁,对于行级X锁的申请,取决于锁定的行。如果SessionA和SessionB分别对不同的行申请行级X锁,它们都会同时申请成功,也就是说,IX锁不会和行级的X锁发生冲突,这大大提高了InnoDB的并发写能力。

那么,意向锁的作用是什么呢?

首先思考一个问题,当事务A持有行R的行级S锁时,若事务B想要申请表级写锁,由于表级写锁与事务A持有的S锁是冲突的,因此,事务B只能阻塞直到事务A释放S锁。那么,数据库如何判断这个冲突呢?

● step1:判断表是否已被其他事务用表锁锁表

● step2:判断表中的每一行是否已被行锁锁住

注意step2,为了判断表是否具有行级锁,需要遍历整个表,这样的判断方法效率实在不高,于是就有了意向锁。在意向锁存在的情况下,事务A必须先申请表的意向共享锁,成功后再申请一行的行锁。再看step2,只需要判断表上是否有意向锁即可判断冲突。

意向锁由InnoDB自动添加,不需要用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,对于alter table类型操作,mysql会自动添加表锁。当然,我们通过以下语句显示给SELECT语句加共享锁或排他锁:

共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。

记录锁

记录锁是索引记录上的锁。 例如,SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止任何其他事务插入,更新或删除t.c1的值为10的行。记录锁总是锁定索引记录,当定义表时,若没有指定主键,InnoDB创建一个隐藏的主键索引,并使用此索引进行记录锁定。

间隙锁

间隙锁是在索引记录之间的间隙上的锁定,或在最后一个索引记录之前或之后的间隙上的锁定。同时,间隙锁是性能和并发性之间权衡的一部分,并且只在一些事务隔离级别中使用。当使用唯一索引来锁定行时,不会使用间隙锁,当没有使用索引或使用非唯一索引进行锁定行时,才有可能使用间隙锁。

Next-Key锁

Next-Key锁是索引记录上的记录锁和索引记录之间的间隙上的间隙锁的组合。

行锁算法

InnodB行锁主要有以下3种实现算法:

Record Lock:单个行记录上的锁,总是按索引键锁定记录;

Gap Lock:间隙锁,锁定一个范围,但不包含记录本身;

Next-Key Lock:Record Lock+ Gap Lock,锁定一个范围,并且锁定记录本身。

其中,Next-Key Lock是结合了Record Lock和Gap Lock的一种锁定算法。默认隔离级别REPEATABLE-READ下,InnoDB中行锁默认使用算法Next-Key Lock,只有当查询的索引是唯一索引或主键时,InnoDB会对Next-Key Lock进行优化,将其降级为Record Lock,即仅锁住索引本身,而不是范围。当查询的索引为辅助索引时,InnoDB则会使用Next-Key Lock进行加锁。

划分间隙

temp表中id为主键,type有辅助索引,有如下数据:
alt

辅助索引键值区间可表示为:(-∞,2),(2,4),(4,5),(5,11),(11,14),(14,16),(16,18),(18,22),(22,24),(24,+∞),只要这些区间对应的两个临界记录中间可以插入记录,就认为区间对应的记录之间有间隙。如:区间(2,4),可以插入action_type为3的记录,这就认为该区间有间隙。

示例

我们首先分析行锁算法对insert的影响,在两个Session中分别开启事务:

time Session A Session B
1 begin begin
2 select * from temp where type=4 for update;
3 --阻塞
insert into temp (id,type) values(2,3);

当在sessionA中执行select id,type from actiontemp where actiontype=4 for update时,根据Next-Key Lock的加锁规则,Innodb会使用Gap Lock锁定区间(2,4),(4,5),同时使用Record Lock锁定辅助索引键值4,最终锁定区间(2,5)。因此,在sessionB中执行以下insert语句都将导致阻塞:
1. insert into temp (id,type) values(2,2); 2. insert into temp (id,type) values(2,3); 3. insert into temp (id,type) values(2,4); 4. insert into temp (id,type) values(4,4); 5. insert into temp (id,type) values(5,4); 6. insert into temp (id,type) values(4,5); 7. insert into temp (id,type) values(5,5);
能够正常插入的insert语句:
8. insert into temp (id,type) values(7,5); 9. insert into temp (id,type) values(9,5);

辅助索引键值2,5实际并没有被锁定,为何执行语句1,6,7也会阻塞?

我们知道间隙锁会锁定辅助索引区间(2,5)内的间隙,而不会锁定边界值。但是,当我们在进行边界值的操作时,Innodb为了避免影响到间隙锁,当插入记录的辅助索引键值为下界2时,要求插入记录的主键键值必须小于下界对应的主键键值1,同时,当插入记录的辅助索引键值为上界5时,要求插入记录的主键键值必须大于上界对应的主键键值6。这就是执行语句1,6,7都会阻塞的原因,而语句8,9显然主键键值大于上界记录对应的主键键值6。

time Session A Session B
1 begin begin
2 select * from temp where type=16 for update;
3 --阻塞
insert into temp (id,type) values(22,14);
insert into temp (id,type) values(14,18);

根据Next-Key Lock的加锁规则,Innodb会使用Gap Lock锁定区间(14,16),(16,18),同时使用Record Lock锁定辅助索引键值16,最终锁定区间(14,18)。SessionB中,当type为下界键值14时,若主键值大于21,insert语句就会阻塞,当type为上界键值18时,若主键值小于15,insert语句就会阻塞。

下面我们看看执行update语句的锁定情况:

由于Innodb使用Record Lock锁定了actiontype=4的记录,因此在SessionB中执行带where条件actiontype=4的update语句都会阻塞。除此之外,还会阻塞的update语句为:
update temp set type=3 where type=2; update temp set type=3 where type=5; update temp set type=5 where type=2; update temp set type=4 where type=2; update temp set type=4 where type=5; update temp set type=2 where type=5;

总结:InnoDB使用Next-Key Lock对辅助索引进行加锁后,除了阻塞where条件type=4的update语句外,当where条件type的值为锁定区间的上下界时,set字句中若包含type,则type的值不能包含被锁定区间的任意值,包含上下界;当where条件type的值为间隙内的值,无论set字句是否包含type,都不会阻塞,因为间隙内的键值对应的记录根本不存在,即使成功执行,也不会影响间隙锁的锁定。

对delete语句的锁定情况:delete from temp where type=4 ;执行delete语句时,只会阻塞被Record Lock锁定的action_type=4的记录。

当辅助索引键值不存在时,InnoDB将如何锁定呢?

time Session A Session B
1 begin begin
2 select * from temp where type=3 for update;
3 --阻塞
insert into temp (id,type) values(2,2);
insert into temp (id,type) values(2,3);
insert into temp (id,type) values(2,4);

从上面示例即可知,当键值不存在时,InnoDB将以第一个比给定键值小的值为下界,以第一个比给定键值大的值为上界,锁住整个区间内的数据,但不包含上下界,即sessionA锁定的区间为(2,4)。

当在辅助索引上使用范围条件查询时,InnoDB将如何锁定?

time Session A Session B
1 begin begin
2 select * from temp where type>4 for update;
3 --阻塞
insert into temp (id,type) values(7,5);
......

此时,InnoDB同样会扫描以第一个比键值比给定键值小的值为下界 ,通时将type>4的所有记录(无论是否存在)锁定,任何insert type>2的语句都会阻塞,即sessionA锁定的范围为(2,+∞)

一般地,当InnoDB对辅助索引加行锁时,默认隔离级别REPEATABLE-READ下,使用Next-Key Lock算法,InnoDB将以第一个比给定键值小的值为下界(不包含),以第一个比给定键值大的值为上界(不包含),锁住整个区间内的数据。

对于Insert语句:

1.若Insert语句包含该区间内键值(无论是否存在)都将导致阻塞

2.若Insert语句包含区间下界值时,待插入记录的主键必须小于下界记录对应的主键值

3.若Insert语句包含区间上界值时,待插入记录的主键必须大于上界记录对应的主键值

对于update语句:

1.若update语句where条件包含给定键值,将阻塞

2.若update语句where条件包含上下界键值,set字句不能包含被锁定区间的任意值,包含上下界

对于delete语句:delete操作只会阻塞在键值上。

注意:对于多列的唯一索引,当查询仅包含索引列中部分列,InnoDB同样使用使用Next-Key Lock进行加锁,而不会降级为Record Lock。

间隙锁的作用

InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,另外一方面,是为了满足其恢复和复制的需要。

很显然,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。当然,用户可使用下面两种方式显示地关闭间隙锁(Gap Lock):

1.将事务的隔离级别设为READ COMMITTED

2.将参数innodb-locks-unsafe-for-binlog设置为1(目前已被弃用)

多版本并发控制(MVCC)

一致性非锁定读是指InnoDB存储引擎通过多版本控制的方法来读取当前执行时间数据库中行的数据。如果读取的行正在执行delete或update操作,这时读取操作不会因此去等待行上锁的释放。InnoDB会选择去读取一个快照数据。快照数据其实就是当前行数据之前的历史版本,每行记录可能有多个版本,一般称为行多版本技术,由此带来的并发控制,称之为多版本并发控制(MVCC)。

非锁定读机制大大地提高了数据库的并发性。在InnoDB存储引擎默认设置下,这是默认的读取方式,即读取不会占用和等待表上的锁。但在不同的事务隔离级别下,读取的方式不同。READ COMMITTED和REPEATABLE READ都使用非锁定读机制。区别在于,READ COMMITTED下,对于快照数据,总是读取被锁定行的最新一份快照数据,因此会发生不可重复读;而在REPEATABLE READ下,总是读取事务开始时的行数据版本,因此可能会发生幻读。

若需要显示地对数据库的读取操作进行加锁以保证数据逻辑的一致性,可使用select ... for update对读取的行记录加一个X锁,或使用select ... lock in share mode对读取的行记录加一个S锁。

锁带来的问题

InnoDB使用锁机制来实现事务的隔离性要求,使得事务可以并发地工作。锁提高了并发,但也带来了几种问题:脏读,不可重复读,幻读,丢失更新。

脏读

脏数据:指事务对缓冲池中行记录的修改,并且没有被提交。

脏页:指在缓冲池中已经被修改的页,但还没有被刷到磁盘中,即数据库实例内存中的页和磁盘中的页数据不一致。脏页并不影响数据的一致性。

脏读指在不同的事务下,当前事务可以读到另外事务未提交的数据,即读到脏数据。脏读的发生条件是事务的隔离级别为未提交读(read uncommitted)。

不可重复读

不可重复读指在不同的事务下,当前事务可以读到另外事务已经提交的数据,但是这违反了数据库事务的一致性要求。

举例说明,首先需要修改事务隔离级别:

time Session A Session B
1 set session transaction isolation level READ COMMITTED;
2 set session transaction isolation level READ COMMITTED;
3 begin begin
4 select * from temp
5 insert into temp(id,type) values(5,1);
commit;
6 select * from temp;
commit;

在会话A开始一个事务,第一次读取到的记录不包含id为5的记录;在会话B执行插入一条记录,未提交时A读到的数据仍然不包含id为5的记录。当会话B提交事务后,在会话A中再次查询就能读取到id为5的记录。

幻读

幻读是指当事务不是独立执行时发生的一种现象。幻读保证了同一个事务里,查询的结果都是事务开始时的状态(一致性),但是,如果另一个事务同时更新了数据,当前事务再进行更新时,发现数据已存在。

举例说明:

time Session A Session B
1 begin begin
2 select * from temp where type=2;
-- 1 row in set
3 insert into temp(id,type) values(5,2);
4 select * from temp where type=2;
-- 1 row in set
5 commit;
6 select * from temp where type=2;
-- 1 row in set
7 Update temp set type=10 where type=2;
--Query OK, 2 rows affected
Rows matched:2 Changed: 2 Warnings: 0
8 commit;

在会话A开始一个事务,查询type为2的记录,返回一行;开始事务B,插入id为5,type为2的新纪录,事务B提交前后在事务A中执行查询type为2的记录,都只返回一行,也就是说避免了不可重复读的问题。但是当事务B提交后,事务A修改type为2的记录,结果返回受影响的行为2,也就是说更新时实际读到了事务B的提交,发生了幻读。

疑问:Innodb使用了Next-Key Lock行锁算法来避免幻读,为什么Session A中还是发生了幻读?

Innodb提供的Next-Key Lock算法确实是为了避免幻读,而Session A中也确实发生了幻读。主要原因在于,Innodb使用了MVCC技术来避免读取时加锁,即Session A中select是非加锁读取,Next-Key Lock算法自然也没有生效,因此发生了幻读。若想要避免发生幻读,可以显示地对select加锁,使得Next-Key Lock算法生效。

总结:InnoDB的可重复读并不保证避免幻读,需要应用使用加锁读来保证。

丢失更新

丢失更新指一个事务的更新操作会被另一个事务的更新操作所覆盖,从而导致数据不一致。在当前数据库的任何隔离级别下,都不会导致数据库层面的丢失更新问题。因为DML操作,都会对行或更粗粒度的对象加X锁。导致丢失更新更多表现在应用代码业务逻辑中,如出现下面情况将会导致丢失更新问题:

1.事务T1查询一行数据,并展示给终端用户User1;

2.事务T2也查询该数据,并展示给终端用户User2;

3.User1修改这行记录,更新数据库并提交;

4.User2修改这行记录,更新数据库并提交。

很显然,User2提交后,将会导致User1的更新丢失。主要原因在于User2修改的记录是过期的数据。当然,要解决该问题的方案很多,如:使用乐观锁,为每行记录加版本号,每次修改,都将比较版本号,并将版本号+1;也可以使用select ... for update对查询显示加X锁。