mySQL数据库间隙锁(mysql是如何解决幻读的) |
您所在的位置:网站首页 › 读是怎么读 › mySQL数据库间隙锁(mysql是如何解决幻读的) |
概念
MySQL InnoDB支持三种行锁定方式:行锁(Record Lock):锁直接加在索引记录上面。间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。Next-Key Lock:行锁与间隙锁组合起来用就叫做Next-Key Lock。 当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。 举例来说,假如user表中只有101条记录,其empid的值分别是 1,2,...,100,101,下面的SQL: select * from user where user_id > 100 for update;是一个范围条件的检索,InnoDB不仅会对符合条件的user_id值为101的记录加锁,也会对user_id大于101(这些记录并不存在)的“间隙”加锁。 产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。 InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求,对于上面的例子,要是不使用间隙锁,如果其他事务插入了user_id大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读;另外一方面,是为了满足其恢复和复制的需要 快照读和当前读快照读历史数据-mvcc innodb的默认事务隔离级别是rr(可重复读)。它的实现技术是mvcc(MVCC只在读提交可重复读两种隔离级别下工作)。基于版本的控制协议。该技术不仅可以保证innodb的可重复读,而且可以防止幻读。但是它防止的是快照读,也就是读取的数据虽然是一致的,但是数据是历史数据。(http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html这个帖子里面就有一个实例) 当前读最新数据-next-key lock 如何做到保证数据是一致的(也就是一个事务,其内部读取对应某一个数据的时候,数据都是一样的),同时读取的数据是最新的数据。innodb提供了next-key lock,也就是结合gap锁与行锁,达到最终目的。 实现: 1. 快照读(snapshot read) 简单的select操作(不包括 select ... lock in share mode, select ... for update) 2.当前读(current read) select ... lock in share mode、select ... for update insert、update、delete 在RR级别下,快照读是通过MVCC(多版本控制)和undo log来实现的,当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。 测试建表innodb_lock: DROP TABLE IF EXISTS `innodb_lock`; CREATE TABLE `innodb_lock` ( `a` int(10) NOT NULL, `b` varchar(255) NOT NULL DEFAULT '', KEY `index_a` (`a`), KEY `index_b` (`b`) ) ENGINE=InnoDB;插入数据,注意这里边没有a为2的数据: INSERT INTO `innodb_lock` VALUES ('1', 'b2'); INSERT INTO `innodb_lock` VALUES ('3', '3'); INSERT INTO `innodb_lock` VALUES ('4', '4000'); INSERT INTO `innodb_lock` VALUES ('5', '5000'); INSERT INTO `innodb_lock` VALUES ('6', '6000'); INSERT INTO `innodb_lock` VALUES ('7', '7000'); INSERT INTO `innodb_lock` VALUES ('8', '8000'); INSERT INTO `innodb_lock` VALUES ('9', '9000');(1)开启两个客户端,修改事务隔离级别为可重复读 (2)开启事务,在左侧客户端批量修改a为1~6范围内的数据。在右侧客户端插入a为2的数据。右侧操作被阻塞。说明有间隙锁。 (3)重复(2),事务隔离级别依然是repeatable read,只不过变成在右侧客户端插入a为10的数据,成功。 (4)事务隔离级别设置为read committed,重复步骤(2),发现右侧客户端的操作成功。说明该隔离级别无间隙锁。 (5)还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!左侧客户端给不存在的记录加锁,右侧客户端的增加操作阻塞。 但是,如果a是唯一索引,不会升级全表锁。先添加唯一索引: (6)重复步骤(5),发现右侧客户端不会被阻塞,数据插入成功 很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件;当然,对一条不存在的记录加锁,也会有间隙锁的问题。 间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作,以此来达到防止幻读的发生,所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话,那InnoDB只会采用行锁方式来加锁,而不会使用Next-Key Lock的方式,也就是说不会对索引之间的间隙加锁,仔细想想的话,这个并不难理解,大家也可以自己测试一下。 要禁止间隙锁的话,可以把隔离级别降为读已提交,或者开启参数innodb_locks_unsafe_for_binlog。 补充:MySQL InnoDB的可重复读并不保证避免幻读,需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。 本事务中第一次读取出一行,做了一次更新后,另一个事务里提交的数据就出现了。也可以看做是一种幻读: t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM innodb_lock; | +------+-------+ | | a | b | | +------+-------+ | | 1 | a | | +------+-------+ | INSERT INTO innodb_lock | VALUES (2, 'b'); | | SELECT * FROM innodb_lock; | +------+-------+ | | a | b | | +------+-------+ | | 1 | a | | +------+-------+ | COMMIT; | | SELECT * FROM innodb_lock; | +------+-------+ | | a | b | | +------+-------+ | | 1 | a | | +------+-------+ | | UPDATE innodb_lock SET b='z'; | Rows matched: 2 Changed: 2 Warnings: 0 | (怎么多出来一行) | | SELECT * FROM innodb_lock; | +------+-------+ | | a | b | | +------+-------+ | | 1 | z | | | 2 | z | | +------+-------+ |
|
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |