Mysql 事务原理简单分析

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锁。

赞(52) 打赏
未经允许不得转载:优客志 » 数据库

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏