、唯一索引,mysql会做优化,因此使用code
这个非唯一键的二级索引来举例说明。
对于code
,可能的next-key锁的范围是:
开启第一个事务,在code=5
的索引上请求更新:
之前在gap锁的章节中介绍了,code=5 for update
会在code=5
的索引上加一个record锁,还会在1<gap<5的间隙上加gap锁。现在不再验证,直接插入一条(8,8):
insert
处于等待执行的状态,这就是next-key锁
生效而导致的结果。第一个事务,锁定了区间(1,5],由于RR的隔离级别下next-key锁
处于开启生效状态,又锁定了(5,10]区间。所以插入SQL语句的执行被阻塞。
如果我们在第一个事务中,执行了code>8 for update
,在扫描过程中,找到了code=10
,此时就会锁住10之前的间隙(5到10之间的gap),10本身(record),和10之后的间隙(next-key)。此时另一个事务插入(6,6),(9,9)和(11,11)都是不被允许的,只有在前一个索引5及5之前的索引和间隙才能执行插入(更新和删除也会被阻塞)。
插入意向锁在行插入之前由INSERT设置一种间隙锁,是意向排它锁的一种。
在多事务同时写入不同数据至同一索引间隙的时,不会发生锁等待,事务之间互相不影响其他事务的完成,这和间隙锁的定义是一致的。
假设一个记录索引包含4和7,其他不同的事务分别插入5和6,此时只要行不冲突,插入意向锁不会互相等待,可以直接获取。参照锁兼容/冲突矩阵。
插入意向锁的例子不再列举,可以查看gap锁的第一个例子。
自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。最简单的一种情况:如果一个事务正在向表中插入值,则任何其他事务必须等待,以便第一个事务插入的行接收连续的主键值。
我们一般把主键设置为AUTO_INCREMENT
的列,默认情况下这个字段的值为0,InnoDB会在AUTO_INCREMENT
修饰下的数据列所关联的索引末尾设置独占锁。在访问自增计数器时,InnoDB使用自增锁,但是锁定仅仅持续到当前SQL语句的末尾,而不是整个事务的结束,毕竟自增锁是表级别的锁,如果长期锁定会大大降低数据库的性能。由于是表锁,在使用期间,其他会话无法插入表中。
这一章节,我们通过幻读,逐步展开对InnoDB锁的探究。
解释了不同概念的锁的作用域,我们来看一下幻读到底是什么。幻读在RR条件下是不会出现的。因为RR是Repeatable Read,它是一种事务的隔离级别,直译过来也就是“在同一个事务中,同样的查询语句的读取是可重复”,也就是说他不会读到”幻影行”(其他事务已经提交的变更),它读到的只能是重复的(无论在第一次查询之后其他事务做了什么操作,第二次查询结果与第一次相同)。
上面的例子都是使用for update
,这种读取操作叫做当前读,对于普通的select
语句均为快照读。
官方文档对于幻读的定义是这样的:
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.
,这句话看起来应该是不可重复读的定义,同样的查询得到了不同的结果(两次结果不是重复的),但是后面的举例给出了幻读真正的定义,第二次比第一次多出了一行。也就是说,幻读的出现有这样一个前提,第二次查询前其他事务提交了一个INSERT
插入语句。而不可重复读出现的前提是第二次查询前其他事务提交了UPDATE
或者DELETE
操作。
有些文章中提到“RR也不能完全避免幻读”,实际上官方文档实际要表达的意义是“在同一个事务内,多次连续查询的结果是一样的,不会因其他事务的修改而导致不同的查询结果”,这里先给出实验结论:
RC情况下会出现幻读。
首先设置隔离级别为RC,SET SESSION tx_isolation='READ-COMMITTED';
RC(Read Commit)隔离级别可以避免脏读,事务内无法获取其他事务未提交的变更,但是由于能够读到已经提交的事务,因此会出现幻读和不重复读。
也就是说,RC的快照读是读取最新版本数据,而RR的快照读是读取被next-key锁作用区域的副本
我们先来模拟一下RR隔离级别下没有出现幻读的情况:
开启第一个事务并执行一次快照查询。
这两个事务的执行,有两个问题:
1.为什么之前的例子中,在第二个事务的INSERT
被阻塞了,而这次却执行成功了。
这是因为原来的语句中带有for update
,这种读取是当前读,会加锁。而本次第一个事务中的SELECT
仅仅是快照读,没有加任何锁。所以不会阻塞其他的插入。
2.数据库中的数据已经改变,为什么会读不到?
这个就是之前提到的next-key lock锁定的副本。RC及以下级别才会读到已经提交的事务。更多的业务逻辑是希望在某段时间内或者某个特定的逻辑区间中,前后查询到的数据是一致的,当前事务是和其他事务隔离的。这也是数据库在设计实现时遵循的ACID原则。
再给出RR条件下出现幻读的情形,这种情形不需要两个事务,一个事务就已经可以说明,
至于RR隔离级别下到底会不会出现幻读,就需要看幻读的定义中的查询到底是连续的查询还是不连续的查询。如果认为RR级别下可能会出现幻读,那该级别下也会出现不重复读。
RR隔离级别下,虽然不会出现幻读,但是会因此产生其他的问题。
前提:当前数据表中只存在(1,1),(5,5),(10,10)三组数据。
如果数据库隔离级别不是默认,可以执行SET SESSION tx_isolation='REPEATABLE-READ';
(该语句不是全局设置)更新为RR。
然后执行下列操作:
除了上述这类问题外,RR还会有丢失更新的问题。
如下表给出的操作:
这个例子里,事务一的更新是无效的,尽管在这个事务里程序认为还存在(10,10)记录。
事务一中更新之前的SELECT
操作是快照读,所以读到了快照里的(10,10),而UPDATE
中的WHERE
子句是当前读,取得是最新版本的数据,所以matched: 0 Changed: 0
。
如果上述例子中的操作是对同一条记录做修改,就会引起更新丢失。例如,事务一和二同时开启,事务一先执行update test set code=100 where id=10;
,事务二再执行update test set code=200 where id=10;
,事务一的更新就会被覆盖。
这种情况下,引入我们常见的两种方式来解决该问题
无论是乐观锁还是悲观锁,使用的思想都是一致的,那就是当前读。乐观锁利用当前读
判断是否是最新版本,悲观锁利用当前读
锁定行。
但是使用乐观锁时仍然需要非常谨慎,因为RR是可重复读的,一定不能在UPDATE之前先把版本号读取出来。
如果一个SQL语句要对二级索引(非主键索引)设置X模式的Record锁,InnoDB还会检索出相应的聚簇索引(主键索引)并对它们设置锁定。
SELECT ... FROM
是快照读取,除了SERIALIZABLE
的事务隔离级别,该SQL语句执行时不会加任何锁。
SERIALIZABLE
级别下,SELECT
语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引,只锁定索引记录,而