我要投搞

标签云

收藏小站

爱尚经典语录、名言、句子、散文、日志、唯美图片

当前位置:金算盘高手论坛799222 > 排它锁 >

抱歉没早点把这么全面的InnoDB锁机制发给你

归档日期:05-25       文本归类:排它锁      文章编辑:爱尚语录

  数据事务设计遵循ACID的原则: 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。一个支持事务(Transaction)的数据库,必须要具有这四种特性,否则在事务过程(Transaction processing)当中无法保证数据的正确性。

  S锁允许当前持有该锁的事务读取行。X锁允许当前持有该锁的事务更新或删除行。

  S锁:如果事务T1持有了行r上的S锁,则其他事务可以同时持有行r的S锁,但是不能对行r加X锁。

  X锁:如果事务T1持有了行r上的X锁,则其他任何事务不能持有行r的X锁,必须等待T1在行r上的X锁释放。

  如果事务T1在行r上保持S锁,则另一个事务T2对行r的锁的请求按如下方式处理:

  InnoDB支持多种粒度的锁,允许行级锁和表级锁的共存。例如LOCK TABLES ... WRITE等语句可以在指定的表上加上独占锁。InnoBD使用意向锁来实现多个粒度级别的锁定。意向锁是表级锁,表示table中的row所需要的锁(S锁或X锁)的类型。

  意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)。IS锁表示当前事务意图在表中的行上设置共享锁,下面语句执行时会首先获取IS锁,因为这个操作在获取S锁:

  IX锁表示当前事务意图在表中的行上设置排它锁。下面语句执行时会首先获取IX锁,因为这个操作在获取X锁:

  事务要获取某个表上的S锁和X锁之前,必须先分别获取对应的IS锁和IX锁。

  按照上面的兼容性,如果不同事务之间的锁兼容,则当前加锁事务可以持有锁,如果有冲突则会等待其他事务的锁释放。

  如果一个事务请求锁时,请求的锁与已经持有的锁冲突而无法获取时,互相等待就可能会产生死锁。

  意向锁不会阻止除了全表锁定请求之外的任何锁请求。 意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行。

  常见的锁有Record锁、gap锁、next-key锁、插入意向锁、自增锁等。下面会对每一种锁给出一个查看锁的示例:

  数据表test只有两列,id是主键索引,code是普通的索引(注意,一定不要是唯一索引),并初始化了两条记录,分别是(1,1),(10,10)。这样,我们验证唯一键索引就可以使用id列,验证普通索引(非唯一键二级索引)时就使用code列。

  要看到锁的情况,必须手动开启多个事务,其中一些锁的状态的查看则必须使锁处于waiting状态,这样才能在mysql的引擎状态日志中看到。

  这条命令能显示最近几个事务的状态、查询和写入情况等信息。当出现死锁时,命令能给出最近的死锁明细。

  记录锁始终只锁定索引。即使表没有建立索引,InnoDB也会创建一个隐藏的聚簇索引(隐藏的递增主键索引),并使用此索引进行记录锁定。

  可以看到有一行被加了锁。由之前对锁的描述可以推测出,update语句给id=1这一行上加了一个X锁。

  注意:X锁广义上是一种抽象意义的排它锁,即锁一般分为X模式和S模式,狭义上指row或者index上的锁,而Record锁是索引上的锁。 为了不修改数据,可以用select ... for update语句,加锁行为和update、delete是一样的,insert加锁机制较为复杂,后面的章节会提到。

  喜闻乐见,我们看到了这个锁的状态。状态标题是事务正在等待获取锁,描述中的lock_mode X locks rec but not gap就是本章节中的record记录锁,直译一下X锁模式锁住了记录。后面还有一句but not gap意思是只对record本身加锁,并不对间隙加锁,间隙锁的叙述见下面的内容。

  间隙锁作用在索引记录之间的间隔,又或者作用在第一个索引之前,最后一个索引之后的间隙。不包括索引本身。

  唯一索引可以锁定一行,所以不需要间隙锁锁定。如果列没有索引或者具有非唯一索引,该语句会锁定当前索引前的间隙。

  在同一个间隙上,不同的事务可以持有上述兼容/冲突表中冲突的两个锁。例如,事务T1现在持有一个间隙S锁,T2可以同时在同一个间隙上持有间隙X锁。

  允许冲突的锁在间隙上锁定的原因是,如果从索引中清除一条记录,则由不同事务在这条索引记录上的加间隙锁的动作必须被合并。

  InnoDB中的间隙锁的唯一目的是防止其他事务插入间隙。间隙锁是可以共存的,一个事务占用的间隙锁不会阻止另一个事务获取同一个间隙上的间隙锁。

  按照官方文档,where子句查询条件是唯一键且指定了值时,只有record锁,没有gap锁。

  这里只测试验证一下当指定非唯一键索引的时候,gap锁的位置,按照文档的说法,会锁定当前索引及索引之前的间隙。(指定了非唯一键索引,例如code=10,间隙锁仍然存在)

  由于预存了两条数据,row(1,1)和row(10,10),此时这个间隙应该是1

  开启第二个事务,在code=10之前的间隙中插入一条数据,看下这条数据是否能够插入:

  插入语句被阻塞了,lock_mode X locks gap before rec,由于第一个事务锁住了1到10之间的gap,需要等待获取锁之后才能插入。

  可以看到:指定的非唯一建索引的gap锁的边界是当前索引到上一个索引之间的gap。

  执行到这里,如果第一个事务不提交或者回滚的话,第二个事务一直等待直至mysql中设定的超时时间。

  Next-key锁实际上是Record锁和gap锁的组合。Next-key锁是在下一个索引记录本身和索引之前的gap加上S锁或是X锁(如果是读就加上S锁,如果是写就加X锁)。

  幻读是指在同一事务下,连续执行两次同样的SQL语句,第二次的SQL语句可能会返回之前不存在的行。

  当查询的索引是唯一索引时,Next-key lock会进行优化,降级为Record Lock,此时Next-key lock仅仅作用在索引本身,而不会作用于gap和下一个索引上。

  由于id是主键、唯一索引,mysql会做优化,因此使用code这个非唯一键的二级索引来举例说明。对于code,可能的next-key锁的范围是:

  insert处于等待执行的状态,这就是next-key锁生效而导致的结果。第一个事务,锁定了区间(1,5],由于RR的隔离级别下next-key锁处于开启生效状态,又锁定了(5,10]区间。所以插入SQL语句的执行被阻塞。

  解释:在这种情况下,被锁定的区域是code=5前一个索引到它的间隙,以及next-key的区域。code=5 for update对索引的锁定用区间表示,gap锁锁定了(1,5),record锁锁定了{5}索引记录,next-key锁锁住了(5,10],也就是说整个(1,10]的区间被锁定了。由于是for update,所以这里的锁都是X锁,因此阻止了其他事务中带有冲突锁定的操作执行。

  如果我们在第一个事务中,执行了code8 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,此时只要行不冲突,插入意向锁不会互相等待,可以直接获取。参照锁兼容/冲突矩阵。

  自增锁(AUTO-INC Locks)是事务插入时自增列上特殊的表级别的锁。最简单的一种情况:如果一个事务正在向表中插入值,则任何其他事务必须等待,以便第一个事务插入的行接收连续的主键值。

  我们一般把主键设置为AUTO_INCREMENT的列,默认情况下这个字段的值为0,InnoDB会在AUTO_INCREMENT修饰下的数据列所关联的索引末尾设置独占锁。

  在访问自增计数器时,InnoDB使用自增锁,但是锁定仅仅持续到当前SQL语句的末尾,而不是整个事务的结束,毕竟自增锁是表级别的锁,如果长期锁定会大大降低数据库的性能。由于是表锁,在使用期间,其他会话无法插入表中。

  幻读在RR条件下是不会出现的。因为RR是Repeatable Read,它是一种事务的隔离级别,直译过来也就是“在同一个事务中,同样的查询语句的读取是可重复”,也就是说他不会读到”幻影行”(其他事务已经提交的变更),它读到的只能是重复的(无论在第一次查询之后其他事务做了什么操作,第二次查询结果与第一次相同)。

  上面的例子都是使用for update,这种读取操作叫做当前读,对于普通的select语句均为快照读。

  当前读,又叫加锁读,或者阻塞读。这种读取操作不再是读取快照,而是读取最新版本并且加锁。快照读不会添加任何锁。

  手动无脑翻译:所谓的幻影行问题是指,在同一个事务中,同样的查询语句执行多次,得到了不同的结果,这就是幻读。例如:如果同一个SELECT语句执行了两次,第二次执行的时候比第一次执行时多出一行,则该行就是所谓的幻影行。

  这看起来应该是不可重复读的定义,同样的查询得到了不同的结果(两次结果不是重复的),但是后面的举例给出了幻读真正的定义,第二次比第一次多出了一行。

  也就是说,幻读的出现有这样一个前提,第二次查询前其他事务提交了一个INSERT插入语句。而不可重复读出现的前提是第二次查询前其他事务提交了UPDATE或者DELETE操作。

  mysql的快照读,使得在RR的隔离级别上在next-Key的作用区间内,制造了一个快照副本,这个副本是隔离的,无论副本对应的区间里的数据被其他事务如何修改,在当前事务中,取到的数据永远是副本中的数据。

  有些文章中提到“RR也不能完全避免幻读”,实际上官方文档实际要表达的意义是“在同一个事务内,多次连续查询的结果是一样的,不会因其他事务的修改而导致不同的查询结果”,这里先给出实验结论:

  RC(Read Commit)隔离级别可以避免脏读,事务内无法获取其他事务未提交的变更,但是由于能够读到已经提交的事务,因此会出现幻读和不重复读。也就是说,RC的快照读是读取最新版本数据,而RR的快照读是读取被next-key锁作用区域的副本。

  为什么之前的例子中,在第二个事务的INSERT被阻塞了,而这次却执行成功了。

  这是因为原来的语句中带有for update,这种读取是当前读,会加锁。而本次第一个事务中的SELECT仅仅是快照读,没有加任何锁。所以不会阻塞其他的插入。

  这个就是之前提到的next-key lock锁定的副本。RC及以下级别才会读到已经提交的事务。更多的业务逻辑是希望在某段时间内或者某个特定的逻辑区间中,前后查询到的数据是一致的,当前事务是和其他事务隔离的。这也是数据库在设计实现时遵循的ACID原则。

  再给出RR条件下出现幻读的情形,这种情形不需要两个事务,一个事务就已经可以说明:

  至于RR隔离级别下到底会不会出现幻读,就需要看幻读的定义中的查询到底是连续的查询还是不连续的查询。如果认为RR级别下可能会出现幻读,那该级别下也会出现不重复读。

  这个例子里,事务一的更新是无效的,尽管在这个事务里程序认为还存在(10,10)记录。事务一中更新之前的SELECT操作是快照读,所以读到了快照里的(10,10),而UPDATE中的WHERE子句是当前读,取得是最新版本的数据,所以matched: 0 Changed: 0

  这就是经典的丢失更新问题,英文叫Lost Update,又叫提交覆盖,因为是最后执行更新的事务提交导致的覆盖。还有一种更新丢失叫做回滚覆盖,即一个事务的回滚把另一个事务提交的数据给回滚覆盖了,但是目前市面上所有的数据库都不支持这种stupid的操作,因此不再详述。

  无论是乐观锁还是悲观锁,使用的思想都是一致的,那就是当前读。乐观锁利用当前读判断是否是最新版本,悲观锁利用当前读锁定行。

  但是使用乐观锁时仍然需要非常谨慎,因为RR是可重复读的,一定不能在UPDATE之前先把版本号使用快照读获取出来。

  如果一个SQL语句要对二级索引(非主键索引)设置X模式的Record锁,InnoDB还会检索出相应的聚簇索引(主键索引)并对它们设置锁定。

  SERIALIZABLE级别下,SELECT语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引,只锁定索引记录,而不会锁定gap。

  UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个独占的next-key锁,如果是唯一索引只锁定记录。

  当UPDATE修改聚簇索引时,将对受影响的二级索引采用隐式锁,隐式锁是在索引中对二级索引的记录逻辑加锁,实际上不产生锁对象,不占用内存空间。

  例如update test set code=100 where id=10;执行的时候code=10的索引(code是二级索引,见文中给出的建表语句)会被加隐式锁,只有隐式锁产生冲突时才会变成显式锁(如S锁、X锁)。即此时另一个事务也去更新id=10这条记录,隐式锁就会升级为显示锁。

  UPDATE可能会导致新的普通索引的插入。当新的索引插入之前,会首先执行一次重复索引检查。在重复检查和插入时,更新操作会对受影响的二级索引记录采用共享锁定(S锁)。

  INSERT区别于UPDATE系列单独列出,是因为它的处理方式较为特别。

  插入行之前,会设置一种插入意向锁,插入意向锁表示插入的意图。如果其它事务在要插入的位置上设置了X锁,则无法获取插入意向锁,插入操作也因此阻塞。

  INSERT在插入的行上设置X锁。该锁是一个Record锁,并不是next-key锁,即只锁定记录本身,不锁定间隙,因此不会阻止其他会话在这行记录前的间隙中插入新的记录。具体的加锁过程见下文。

  并发条件下,唯一键索引冲突可能会导致死锁,这种死锁一般分为两种:一种是rollback引发,另一种是commit引发。

  当事务一执行回滚时,事务二和事务三发生了死锁。InnoDB的死锁检测一旦检测到死锁发生,会自动失败其中一个事务,因此看到的结果是一个失败另一个成功。

  死锁产生的原因是事务一插入记录时,对(2,2)记录加X锁,此时事务二和事务三插入数据时检测到了重复键错误,事务二和事务三要在这条索引记录上设置S锁,由于X锁的存在,S锁的获取被阻塞。

  事务一回滚,由于S锁和S锁是可以兼容的,因此事务二和事务三都获得了这条记录的S锁。此时其中一个事务希望插入,则该事务期望在这条记录上加上X锁,然而另一个事务持有S锁,S锁和X锁互相是不兼容的,两个事务就开始互相等待对方的锁释放,造成了死锁。

  事务一的insert语句加的是隐式锁(隐式的Record锁、X锁),但是其他事务插入同一行记录时,出现了唯一键冲突,事务一的隐式锁升级为显示锁。

  事务二和事务三在插入之前判断到了唯一键冲突,是因为插入前的重复索引检查,这次检查必须进行一次当前读,于是非唯一索引就会被加上S模式的next-key锁,唯一索引就被加上了S模式的Record锁。

  因为插入和更新之前都要进行重复索引检查而执行当前读操作,所以RR隔离级别下,同一个事务内不连续的查询,可能也会出现幻读的效果(但个人并不认为RR级别下也会出现幻读,幻读的定义应该是连续的读取)。而连续的查询由于都是读取快照,中间没有当前读的操作,所以不会出现幻读。

  事务开启,尝试获取插入意向锁。例如,事务一执行了select * from test where id8 for update,事务二要插入(9,9),此时先要获取插入意向锁,由于事务一已经在对应的记录和间隙上加了X锁,因此事务二被阻塞,并且阻塞的原因是获取插入意向锁时被事务一的X锁阻塞。

  获取意向锁之后,插入之前进行重复索引检查。重复索引检查为当前读,需要添加S锁。

  如果是已经存在唯一索引,且索引未加锁。直接抛出Duplicate key的错误。如果存在唯一索引,且索引加锁,等待锁释放。

  code=5时,首先会获取code=5的索引记录锁(Record锁),根据之前gap锁的介绍,会在前一个索引和当前索引之间的间隙加锁,于是区间(1,5)之间被加上了X模式的gap锁。除此之外RR模式下,还会加next-key锁,于是区间(5,10]被加了next-key锁。

  由gap锁的特性,兼容矩阵中冲突的锁也可以被不同的事务同时加在一个间隙上。上述两个select ... for update语句出现了间隙锁的交集,code=5的next-key锁和code=10的gap锁有重叠的区域——(5,10)。

  当事务一执行插入语句时,会先加X模式的插入意向锁,即兼容矩阵中的IX锁。但是由于插入意向锁要锁定的位置存在X模式的gap锁。兼容矩阵中IX和X锁是不兼容的,因此事务一的IX锁会等待事务二的gap锁释放。

  事务二也执行插入语句,与事务一同样,事务二的插入意向锁IX锁会等待事务一的gap锁释放。

  无论是哪种场景,万变不离其宗,都是由于某个区间上或者某一个记录上可以同时持有锁,例如不同事务在同一个间隙gap上的锁不冲突;不同事务中,S锁可以阻塞X锁的获取,但是不会阻塞另一个事务获取该S锁。这样才会出现两个事务同时持有锁,并互相等待,最终导致死锁。

  其中需要注意的点是,增、删、改的操作都会进行一次当前读操作,以此获取最新版本的数据,并检测是否有重复的索引。这个过程除了会导致RR隔离级别下出现死锁之外还会导致其他两个问题:

本文链接:http://jdockfish.com/paitasuo/84.html