Mysql事务隔离级别及MVCC

  1. 云栖社区>
  2. 博客>
  3. 正文

Mysql事务隔离级别及MVCC

吟风_骋 2019-08-09 17:14:10 浏览280
展开阅读全文

mysql比较重要的事务特性与隔离级别,怎么能够错过对它们的认识和介绍。
1. 事务
事务就是一组原子操作,要么全部执行更新成功,要么全部失败。
ACID(原子性、一致性、隔离性和持久性)

原子性:一个事务必须视作一个不可分割的最小工作单元。
一致性:数据库从一致性的状态转换到另外一个一致性状态,如:用户在执行update 减钱或update 加钱的时候,系统崩溃,数据库里面的数据是没有被修改的。因为事务未被提交。
隔离性:一个事务所做的修改在最终提交之前,对其他事务是不可见的。在update 减100元执行完但未提交,另一个事务看到的余额是未被减少的数据结果。
持久性:一旦数据提交,所做的修改会永远存在数据库中。

实际中,要完全实现ACID,会非常困难,需要做额外很多工作来达到这个效果,而这些额外的工作对用户是不可感知的。

为了更好地理解ACID,以银行账户转账为例:

1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 702332;
3 UPDATE checking SET balance = balance - 100.00 WHERE customer_id = 702332;
4 UPDATE savings SET balance = balance + 100.00 WHERE customer_id = 702332;
5 COMMIT;

原子性:要么完全提交(702332的checking余额减少100,savings 的余额增加100),要么完全回滚(两个表的余额都不发生变化)
一致性:这个例子的一致性体现在 100元不会因为数据库系统运行到第3行之后,第4行之前时崩溃而不翼而飞,因为事物还没有提交。
隔离性:允许在一个事务中的操作语句会与其他事务的语句隔离开,比如事务A运行到第3行之后,第4行之前,此时事务B去查询checking余额时,它仍然能够看到在事务A中被减去的100元,因为事务A和B是彼此隔离的。在事务A提交之前,事务B观察不到数据的改变。
持久性:这个很好理解。
事务跟锁一样都会需要大量工作,因此你可以根据你自己的需要来决定是否需要事务支持,从而选择不同的存储引擎。

2. 隔离级别
在SQL 标准中定义了四种隔离级别。
较低的隔离级别通常可以执行更高的并发,系统的开销也是更低的。

READ UNCOMMITED 未提交读
事务中修改了数据,即便是未提交,也被其他事务可见,这样就造成了脏读。脏读会带来很多问题,自己也可以想象之,所以很少使用它。

READ COMMITED 提交读
这是大多数数据库的默认隔离级别,一个事务修改数据但未提交,对其他任何事务都是不可见的。也就是不可重复读

REPATABLE READ 可重复读
解决了脏读的问题,但是不能解决另外一个问题,幻读。所谓幻读,就是当某个事务读取某个范围内的记录时,另外一个事务又在该范围插入了新纪录,当之前的事务再次来读取该范围的数据时,发现还有新数据未被更新,就像产生了幻觉一样,即产生幻行。InnoDB 采取了(MVCC, Multiversion Concurrency Control)解决了幻读的问题。
可重复读是 Mysql 的默认事务隔离级别。

SERIALIZABLE 可串行化
它是最高的隔离界别。它通过强制事务串行化,避免了前面所说的幻读问题。简单的说,就是 SERIALIZABLE 会在读取的每一行数据上都加锁,这样会导致大量的超时和锁竞争的问题。实际上也是很少用它来。

查看隔离级别:SELECT @@tx_isolation
设置mysql的隔离级别:set session transaction isolation level 设置事务隔离级别

2.2 死锁
死锁就不再解释,之前有一篇文章单独介绍了死锁及死锁检测,mysql 解决死锁的方式为,死锁检测和死锁超时机制。
InnoDB 实现的方法是,将持有最少的行级排它锁的事务进行回滚。

死锁可能是正在的数据导致,还有可能是存储引擎的实现方式所致。
一般只需要重新执行因死锁而回滚的事务即可。

2.3 事务日志
修改数据,是修改内存拷贝,再把该修改行为记录到持久化事务日志中,而不是每次将修改的数据本身持久到磁盘中。

2.4 mysql 中的事务
Mysql 提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster。也支持第三方引擎

自动提交 AUTOCOMMIT
Mysql 默认采用自动提交模式,如每个select 语句都被当作一个事务执行提交操作。

对MyISAM 自动提交对此类非事务型引擎不起作用。

3. 多版本并发控制
先来谈谈redo log 和 undo log
1.redo log通常是物理日志,记录的是数据页的物理修改,它用来恢复提交后的物理数据页(有且只能恢复到最后一次提交的位置)。
2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志,记录每行数据。

MVCC (Multiversion Concurrency Control),即多版本并发控制技术,它使得大部分支持行锁的事务引擎,不再单纯的使用行锁来进行数据库的并发控制,取而代之的是把数据库的行锁与行的多个版本结合起来,只需要很小的开销,就可以实现非锁定读,从而大大提高数据库系统的并发性能

InnoDB是一个多版本存储引擎:它保存有关已更改行的旧版本的信息,以支持并发和回滚等事务功能。此信息存储在表空间中称rollback segment的数据结构中。 InnoDB使用rollback segment中的信息来执行事务回滚中所需的撤消操作。它还使用该信息构建行的早期版本以进行一致读取。

在内部,InnoDB为存储在数据库中的每一行添加三个字段
●6字节的DB_TRX_ID字段指示插入或更新该行的最后一个事务的事务标识符。此外,删除在内部被视为更新,其中行中的special bit被设置为将其标记为已删除。
●每行还包含一个7字节的DB_ROLL_PTR字段,称为滚动指针。 roll指针指向写入回滚段的undo log记录。如果更新了行,则undo log记录了能重建更新之前数据的所有必要信息。
●6字节的DB_ROW_ID字段包含在插入新行时单调增加的行ID。如果InnoDB自动生成聚簇索引,则索引包含行ID值。否则,DB_ROW_ID列不会出现在任何索引中。

rollback段中的undo logs分为插入和更新undo log。只在事务回滚中才需要插入undo log,并且可以在事务提交后立即丢弃。更新undo log也用于一致性读取,但只有在InnoDB没有分配快照的事务之后才能丢弃它们,在一致读取中可能需要更新undo log中的信息来构建早期版本的数据库行。

在InnoDB多版本控制方案中,当您使用SQL语句删除行时,不会立即从数据库中物理删除该行。 InnoDB在丢弃为删除写入的更新撤消日志记录时,仅物理删除相应的行及其索引记录。此删除操作称为清除,并且速度非常快,通常与执行删除的SQL语句的时间顺序相同。

如果你在表中以大约相同的速率插入和删除少量批次的行,则清除线程可能开始落后,并且由于所有“死”行,表可以变得越来越大,使得所有磁盘都受到限制慢。在这种情况下,通过调整innodb_max_purge_lag系统变量来限制新行操作,并为清除线程分配更多资源。有关更多信息。

多版本控制和二级索引
InnoDB多版本并发控制(MVCC)以不同于聚簇索引的方式处理二级索引。聚集索引中的记录就地更新,其隐藏的系统列指向可以重建早期版本记录的撤消日志条目。与聚簇索引记录不同,二级索引记录不包含隐藏的系统列,也不会就地更新。

更新二级索引列时,旧的二级索引记录将被删除标记,插入新记录,最终清除delete-marked的记录。当二级索引记录被delete-marked或二级索引页面由较新的事务更新时,InnoDB在聚簇索引中查找数据库记录。在聚簇索引中,将检查记录的DB_TRX_ID,如果在启动读取事务后修改了记录,则会从undo log中检索正确的记录版本。

如果二级索引记录被标记为删除或二级索引页面由较新的事务更新,则不使用覆盖索引技术。 InnoDB不是从索引结构返回值,而是在聚簇索引中查找记录。

进行一些实例操作,执行reset master 命令清理所有binlog
新建user表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入几条数据

INSERT INTO `user` (`name`) VALUES ('zhangsan');
INSERT INTO `user` (`name`) VALUES ('lisi');
INSERT INTO `user` (`name`) VALUES ('wangwu');

对于insert:
begin->用排他锁锁定该行->记录redo log->记录undo log->插入当前行的新值,写事务编号。若回滚,回滚时把insert undo log丢弃
对于update:
begin->用排他锁锁定该行->记录redo log->记录undo log->修改当前行的值,写事务编号。若回滚,回滚指针指向undo log中的修改前的行

SELECT
Innodb检查每行数据,确保他们符合两个标准:
1、InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本),这确保当前事务读取的行都是事务之前已经存在的,或者是由当前事务创建或修改的行
2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号,确定了当前事务开始之前,行没有被删除
符合了以上两点则返回查询结果。

INSERT
InnoDB为每个新增行记录当前系统版本号(事务id)。
DELETE
InnoDB为删除的每一行,保存当前系统版本号作为删除标识。
UPDATE
InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本到原来的行作为行删除标识(special bit)。

执行

update user set name='zhangsan22' where id=1;

Binlog的记录
image

执行

delete from user where id=1;

Binlog的记录
image

对应行插入、更新、删除的过程如下图所示
image

从官方文档可知,删除时先标记为delete-marked, 之后再通过purge的方式进行删除。
事务提交的时候,也会将该事务对应的undo log放入到删除列表中,未来通过purge来删除。
update分为两种情况:update的列是否是主键列
①如果不是主键列,在undo log中直接反向记录是如何update的。即update是直接进行的。
②如果是主键列,update分两步执行:先删除该行,再插入一行目标行。记住,sql每一事务操作都会产生新的版本(事务id)。

网友评论

登录后评论
0/500
评论
吟风_骋
+ 关注