Mysql Innodb中的事务隔离级别
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
未提交读(Read uncommitted) | 可能 | 可能 | 可能 |
已提交读(Read committed) | 不可能 | 可能 | 可能 |
可重复读(Repeatable read) | 不可能 | 不可能 | Innodb中不可能,后面解释 |
可串行化(Serializable ) | 不可能 | 不可能 | 不可能 |
脏读
不可重复读
幻读
不可重复读和幻读的区别
这两者有些相似。但不可重复读重点在于update和delete,而幻读的重点在于insert。如果使用锁机制来实现这两种隔离级别,在可重复读中,该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。 刚才提到锁机制,那么我们最常见的就是悲观锁和乐观锁。
悲观锁和乐观锁
悲观锁为了保证事务的隔离性,就需要一致性锁定读,就是每次操作数据时都去锁住数据,不管哪种操作(增、删、改、查)都加锁,以至于其他事务操作这些数据 乐观锁一般都是给表新增version字段,然后先通过查询到该数据的version版本,之后数据修改时都将vsersion字段当作where条件去操作数据,并且将version字段修改成version+1,若修改行数大于1表示修改成功,反之则修改失败。
锁的原理
不使用索引
CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
-- 手动开启一个事务,并在id = 1这条数据上加上排它锁
SELECT * from test1 WHERE id = 1 for UPDATE;
BEGIN;
-- 手动开启另外一个事务,此时给id=2的这条数据进行加排它锁,结果会如何?
SELECT * from test1 WHERE id = 2 for UPDATE
发现此时居然查询id=2的数据事务被卡住了。这是为什么呢?当表没有创建索引时或者查询语句没有命中索引时,锁住的是整个表的数据,因为没有命中索引故其会去扫描全表数据。 当一张表没有索引时,innoDB会创建一个隐藏主键索引,当通过隐藏的主键索引去检索时,将该表中所有的隐藏索引检索一遍 例子:如果手动开始事务,并在id=1的数据上手动加上排它锁.如果此时再去查询id=2的数据时,发现此语句卡住了。 故得出没有建立索引的表,一旦锁住数据及为锁住整张表。
主键索引
CREATE TABLE `test2` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
-- 主键索引
SELECT * from test2 WHERE id = 1 FOR UPDATE;
BEGIN;
-- 手动开启其他事务
SELECT * from test2 WHERE id = 5 for update;
此时说明,主键索引时只会锁住匹配到的索引项,而不会影响其他事务操作其他索引
唯一索引
CREATE TABLE `test3` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
-- 唯一索引
SELECT * from test3 where name = '李四' FOR update;
BEGIN;
-- 唯一索引
SELECT * from test3 where id= 5 FOR update;
注意:此时SELECT * from test3 where id= 5 FOR update为什么执行卡住了?唯一索引锁定时,先通过唯一索引然后找到对应主键索引,也就是辅助索引--->主键索引的一个过程,所以查询id = 5的数据时也被锁住了。 通过上面几个例子发现mysql innodb是通过锁住索引来实现行锁的
mysql innodb 为什么不会出现幻读?
详见下面InnoDB的行锁,如下图 临界锁的操作
临界锁(Next-key Lock)锁定范围加记录
BEGIN;
-- 临界锁,锁住对应的范围,防止幻读。
-- 按道理此时应该锁住,6,7,8(已存在),9,10
SELECT * from test2 WHERE id > 5 and id <11 FOR UPDATE;
BEGIN;
-- 此时测试插入id=7 的值,按道理应该插入不进去,因为锁住的范围是(5,8]和(8,12]
insert into test2 VALUES(7,'试试');
BEGIN;
SELECT * from test2 WHERE id = 12 FOR UPDATE;
刚才不是id>5 and id <11的么?此时为什么id =12也被锁了呢?因为此时锁住的范围是(5,8]和(8,12]
Gap Lock(间隙锁)
BEGIN;
-- 间隙锁,因为id =7 这条数据不存在,故锁退化成了间隙锁,那么此时id=7 落在了(5,8)这个区间
SELECT * from test2 where id = 7 for UPDATE;
BEGIN;
-- 因为锁退化成了间隙锁,那么此时id=7 落在了(5,8)这个区间,故id =6 也一起被锁住了
INSERT into test2 VALUES(6,'卡卡');
Record Lock(记录锁)
注意: 测试在test2表中,也就是主键索引。
BEGIN;
-- 在事务1中在id =5 的主键项锁定
SELECT * from test2 where id = 5 for update;
在RR级别中,通过MVCC机制,虽然让数据变得可重复读(这就是上面为什么事务2也能读取数据),但我们读到的数据可能是历史数据,是不及时的数据,不是数据库当前的数据!这在一些对于数据的时效特别敏感的业务中,就很可能出问题。
对于这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库当前版本数据的方式,叫当前读 (current read)。很显然,在MVCC中:
快照读:就是select select * from table ....; 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。 select * from table where ? lock in share mode; select * from table where ? for update; insert; update ; delete; 事务的隔离级别实际上都是定义了当前读的级别,MySQL为了减少锁处理(包括等待其它锁)的时间,提升并发能力,引入了快照读的概念,使得select不用加锁。而update、insert这些“当前读”,就需要另外的模块来解决了
写("当前读") 事务的隔离级别中虽然只定义了读数据的要求,实际上这也可以说是写数据的要求。上文的“读”,实际是讲的快照读;而这里说的“写”就是当前读了。 为了解决当前读中的幻读问题,MySQL事务使用了Next-Key锁。