1、行锁的3种算法

InnoDB存储引擎有3种行锁的算法,其分别是

• Record Lock:单个行记录上的锁
• Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
• Next-Key Lock:Gap Lock+ Record Lock,锁定一个范围,并且锁定记录本身

Record Lock总是会去锁住索引记录,如果 InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap lock和Record Lock的一种锁定算法,在Next-KeyLock算法下, InnoDB对于行的查询都是采用这种锁定算法。例如一个索引有10,11,13和20这四个值,那么该索引可能被Next-Key Locking的区间为:

(-∞,10]

(10,11]

(11,13]

(13,20]

(20,+∞)

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决Phantom Problem,这将在下一小节中介绍。而利用这种锁定技术,锁定的不是单个值,而是一个范围,是谓词锁(predict lock)的一种改进。除了next-key locking,还有previous-key locking技术。同样上述的索引10、11、13和20,若采用previous-key locking技术,那么可锁定的区间为:

(-∞,10)

[10,11)

[11,13)

[13,20)

[20,+∞)

若事务T1已经通过next-key locking锁定了如下范围:

(10,11]、(11,13]

然而,当查询的索引含有唯一属性时, InnoDB存储引擎会对Next-Key Lock进行优化,将其降级为 Record Lock,即仅锁住索引本身,而不是范围。看下面的例子,首先根据如下代码创建测试表t:

DROP TABLE IF EXISTS t;
CREATE TABLE t(a INT PRIMARY KEY );
INSERT INTO t SELECT 1;
INSERT INTO t SELECT 2;
INSERT INTO t SELECT 5;接着来执行下表中的SQL语句。

唯一索引的锁定示例

时间

会话A

会话B

1

BEGIN

2

SELECT * FROM t WHERE a=5 FOR UPDATE

3

BEGIN

4

INSERT INTO t SELECT 4

5

COMMIT #成功,不需要等待

6

COMMIT

表t共有1、2、5三个值。在上面的例子中,在会话A中首先对a=5进行ⅹ锁定。而由于a是主键且唯一,因此锁定的仅是5这个值,而不是(2,5)这个范围,这样在会话B中插入值4而不会阻塞,可以立即插入并返回。即锁定由 Next-Key Lock算法降级为了 Record Lock,从而提高应用的并发性。

正如前面所介绍的,Next-Key Lock降级为Record Lock仅在查询的列是唯一索引的情况下。若是辅助索引,则情况会完全不同。同样,首先根据如下代码创建测试表z:

CREATE TABLE z( a INT, b INT, PRIMARY KEY(a), KEY(b));
INSERT INTO Z SELECT 1,1;
INSERT INTO Z SELECT 3,1;
INSERT INTO Z SELECT 5,3;
INSERT INTO Z SELECT 7,6:
INSERT INTO Z SELECT 10,8;

表z的列b是辅助索引,若在会话A中执行下面的SQL语句:

SELECT FROM z WHERE b=3 FOR UPDATE

很明显,这时SQL语句通过索引列b进行查询,因此其使用传统的Next-Key Locking技术加锁,并且由于有两个索引,其需要分别进行锁定。对于聚集索引,其仅对列a等于5的索引加上Record Lock。而对于辅助索引,其加上的是Next-Key Lock,锁定的范围是(1,3),特别需要注意的是, InnoDB存储引擎还会对辅助索引下一个键值加上gap lock,即还有一个辅助索引范围为(3,6)的锁。因此,若在新会话中运行下面的SQL语句,都会被阻塞:

SELECT FROM z WHERE a = 5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,5;

第一个SQL语句不能执行,因为在会话A中执行的SQL语句已经对聚集索引中列a=5的值加上ⅹ锁,因此执行会被阻塞。第二个SQL语句,主键插人4,没有问题,但是插人的辅助索引值2在锁定的范围(1,3)中,因此执行同样会被阻塞。第三个SQL语句,插人的主键6没有被锁定,5也不在范围(1,3)之间。但插入的值5在另一个锁定的范围(3,6)中,故同样需要等待。而下面的SQL语句,不会被阻塞,可以立即执行:

INSERT INTO z SELECT 8,6;
INSERT INTO z SELECT 2,0;
INSERT INTO z SELECT 6,7;

从上面的例子中可以看到, Gap Lock的作用是为了阻止多个事务将记录插入到同范围内,而这会导致Phantom Problem问题的产。例如在上面的例子中,会话A中用户已经锁定了b=3的记录。若此时没有 Gap Lock锁定(3,6),那么用户可以插人索引b列为3的记录,这会导致会话A中的用户再次执行同样查询时会返回不同的记录,即导致Phantom problem问题的产生。

用户可以通过以下两种方式来显式地关闭Gap Lock:

• 将事务的隔离级别设置为 READ COMMITTED
• 将参数 innodb_locks_unsafe_for_binlog设置为1

在上述的配置下,除了外键约束和唯一性检查依然需要的 Gap Lock,其余情况仅使用 Record Lock进行锁定。但需要牢记的是,上述设置破坏了事务的隔离性,并且对于replication,可能会导致主从数据的不一致。此外,从性能上来看, READ COMMITTED也不会优于默认的事务隔离级别 READ REPEATABLE。

在 InnoDB存储引擎中,对于INSERT的操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许查询。对于上面的例子,会话A已经锁定了表z中b=3的记录,即已经锁定了(1,3)的范围,这时若在其他会话中进行如下的插入同样会导致阻塞:

INSERT INTO z SELECT 2,2;

因为在辅助索引列b上插入值为2的记录时,会监测到下一个记录3已经被索而将插入修改为如下的值,可以立即执行:

INSERT INTO z SELECT 2,0;

最后需再次提醒的是,对于唯一键值的锁定, Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成,而查询仅是查找多个唯一索引列中的其中一个,那么查询其实是range类型查询,而不是 point类型查询,故InnoDB存储引擎依然使用 Next-Key Lock进行锁定。

2、解决Phantom Problem

在默认的事务隔离级别下,即 REPEATABLE READ下, InnoDB存储引擎采用Next-Key Locking机制来避免 Phantom Problem(幻像问题)。这点可能不同于与其他的数据库,如 Oracle数据库,因为其可能需要在 SERIALIZABLE的事务隔离级别下才能解决 Phantom Problem。

Phantom Problen是指在同一事务下,连续执行两次同样的SQL语句可能导致不同的结果,第二次的SQL语句可能会返回之前不存在的行。下面将演示这个例子,使用前小节所创建的表t。表t由1、2、5这三个值组成,若这时事务T1执行如下的SQL语句:

select * from t where a> 2 FOR UPDATE;

注意这时事务T1并没有进行提交操作,上述应该返回5这个结果。若与此同时,另一个事务T2插入了4这个值,并且数据库允许该操作,那么事务T1再次执行上述SQL语句会得到结果4和5。这与第一次得到的结果不同,违反了事务的隔离性,即当前事务能够看到其他事务的结果。

InnoDB存储引擎采用 Next-Key Locking的算法避免 Phantom Problem。对于上述的SQL语句 SELECT * FROM t WHERE a>2 FOR UPDATE,其锁住的不是5这单个值,而是对(2,+∞)这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的,从而避免 Phantom Problem。

InnoDB存储引擎默认的事务隔离级别是 REPEATABLE READ,在该隔离级别下,其采用 Next-Key Locking的方式来加锁。而在事务隔离级别 READ COMMITTED下,其仅采用 Record Lock,因此在上述的示例中,会话A需要将事务的隔离级别设置为READ COMMITTED。

此外,用户可以通过 InnoDB存储引擎的 Next-Key Locking机制在应用层面实现唯一性的检查。例如

如果用户通过索引查询一个值,并对该行加上一个SLock,那么即使查询的值不在,其锁定的也是一个范围,因此若没有返回任何行,那么新插入的值一定是唯一的。也许有读者会有疑问,如果在进行第一步 SELECT… LOCK IN SHARE MODE操作时,有多个事务并发操作,那么这种唯一性检查机制是否存在问题。其实并不会,因为这时会导致死锁,只有一个事务的插人操作会成功,而其余的事务会抛出死锁的错误,如下表所示。

通过Next-Key Locking实现应用程序的唯一性检查

时间

会话A

会话B

1

BEGIN

2

SELECT * FROM z WHERE b=4 LOCK IN SHARE MODE

3

SELECT * FROM z WHERE b=4 LOCK IN SHARE MODE

4

INSERT INTO z SELECT 4, 4 #阻塞

5

INSERT INTO Z SELECT 4, 4

ERROR 1213(40001): Deadlock found when trying to get lock; try restarting transaction #抛出死锁异常

6

# INSERT插入成功