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

mysql的索引,为什么不用二叉树,会有什么样的问题,红黑树呢,为什么也不用?

二叉树深度越高,I/O开销越大。查询速率较慢。 红黑树(特殊平衡二叉树)即其子节点的高度差不能大于1,会发生自旋 B树由于是多路平衡查找树,可以将每个磁块的数据放入4KB的数据,充分利用了磁盘的空间,再加上B树的高度降低,这样就能快速访问到数据 B+树,左闭合B+树,B+树非叶子节点不保存数据相关信息,只保存关键字和子节点的引用,B+树叶子节点是顺序排序的。

索引引擎,myisam是什么样的,innodb呢,这里面有用到聚集索引吗?

myisam是索引和数据分开,innodb的数据是索引和数据一起的,myisam没有聚集索引,这里也可以看出谁查询更快,谁写入。 innodb由聚集索引。 innodb的行锁通过临界锁(gap+recored)来实现机锁。

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值