03 事务隔离

冷小贱
67 阅读10分钟

mysql的事务是在引擎层实现的，现在默认的InnoDB支持事务，而MyISAM是不支持事务的。

事务四大特性

事务的四大基本原则是： ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

  • Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
  • Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  • Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）
  • Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离性和隔离级别

为什么需要隔离性？

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read；读到其他事务未提交的数据）、不可重复读（non-repeatable read；前后读取的记录内容不一致）、幻读（phantom read；前后读取的数据数量不一致）的问题，为了解决这些问题，就有了“隔离级别”的概念。

SQL标准的事务隔离级别

首先，隔离级别越高，效率就会越低，所以事务隔离级别没有好坏，只有因地制宜。

  • 读未提交（read uncommitted）： 一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读提交（read committed）： 一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读（repeatable read）： 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化（serializable ）： 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
    • 以上图为例：在事务B执行“将1改成2”时，会被锁住。直到事务A提交，事务B才会继续执行。所以事务A在提交事务之前查询到的v都是1，v3才会是2

MVCC（多版本并发控制）在事务隔离级别中的应用

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。这里只针对事务隔离级别说明，MVCC实现原理回头再研究。

在InnoDB的实现中，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准，通过控制视图的创建时机来实现隔离级别。

  • 读未提交（read uncommitted）： RU级别没有视图的概念，直接返回记录上的最新值。
  • 读提交（read committed）： RC级别下，MVCC会在每个SQL语句执行之前创建一个视图，所以RC级别可以看到其他事务未提交的内容。
  • 可重复读（repeatable read）： 在事务启动的时候创建视图。
  • 串行化（serializable ）： 没有视图概念，直接用加锁的方式来避免并行访问。

事务隔离的实现

总的来说，在MySQL中，每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。也就是说在更新操作的时候，会记录两条日志：一条redo log是记录变更的记录，另一条undo log记录与变更记录相反的回滚操作记录。（ps: stata就用到了undo log）

可重复读的实现

以下图为例，解释可重复读隔离级别的实现：

假设一个值（c）被从1按顺序改到了4，read-view A表示的是可重复读视图创建的视图A，B和C同理。这个时候同一条记录在系统中存在多个版本，就是数据库的多版本并发控制（MVCC）。

分析过程：

  1. A 开启事务，创建视图 A；
  2. B 开启事务，创建视图 B，将 c 从 1 改为 2，同时向视图 A、B 中记录一条回滚记录（将 c 从 2 改回 1）；
  3. C 开启事务，创建视图 C，此时 c 的值为 2，将 c 从 2 改为 3，同时向视图 A、B、C 中记录一条回滚日志（将 c 从 3 改回 2）；
  4. 此时视图 A 中有两条回滚记录，事务 A 再次获取 c 时依次执行这两条回滚记录，即可得到 c 最开始的值 1。

undo log（回滚日志）日志何时删除？

当系统内没有比这个回滚日志更早read-view视图存在的时候。

长事务会导致什么问题

其一：长事务意味着系统中存在很老的事务视图，这个事务在提交之前，回滚记录都要保留，这会占用大量存储空间。

其二：长事务占用锁资源，可能会拖垮库。

事务的启动方式

一、显式启动事务语句，begin或者start transaction,提交commit，回滚rollback；

二、set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。

有几点需要注意的：

  1. START TRANSACTION 后，不管autocommit 是1还是0 。 只有当commit数据才会生效，ROLLBACK后就会回滚。
  2. 当autocommit 为 0 时，不管有没有START TRANSACTION。 只有当commit数据才会生效，ROLLBACK后就会回滚。
  3. 如果autocommit 为1 ，并且没有START TRANSACTION 。 会自动commit。调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

查询长事务可以通过information_schema库的innodb_trx表

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

总结题

  1. 事务的概念是什么?
  • 事务是对数据库中数据操作的保证数据逻辑一致的最小操作单位。 简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
  1. mysql的事务隔离级别读未提交, 读已提交, 可重复读, 串行各是什么意思?
  • 读未提交：可以读取其他事物未提交的内容
  • 读已提交：可以读取其他事物已提交的内容
  • 可重复度：读取其他事务已提交的内容，并且在本事务提交之前读取的内容都是一样的
  • 串行化：事务排队执行，不存在并发情况。读有读锁，写有写锁。读、读之间不影响，读、写和写、写之间互相排斥，当遇到排斥的情况后，后发生的事务需要等待先发生的事务执行完成后才可以执行。
  1. 读已提交, 可重复读是怎么通过视图构建实现的?
  • 读已提交：在每个sql语句执行之前创建一个视图
  • 可重复图：在事务启动的时候为事务创建一个视图，且此视图会伴随整个事务执行。
  1. 可重复读的使用场景举例? 对账的时候应该很有用?
  • 核对数据场景都能用到，例如对账，盘货等
  1. 事务隔离是怎么通过read-view(读视图)实现的?
  • 每一行数有多个版本，当我们要去读取数据的时候，要判断这个数据的版本号，对当前事务而言，是否可见，如果不可见，则要根据undolog计算得到上一个版本。如果上一个版本也不符合要求，则要找到再上一个版本， 直到找到对应正确的数据版本。
  1. 并发版本控制(MCVV)的概念是什么, 是怎么实现的?
  • 略
  1. 使用长事务的弊病? 为什么使用常事务可能拖垮整个库?
  • 表空间持续增加，占用存储空间大
  • 存在导致锁发生冲突或等待的几率大大增加
  • 如果某个应用有发生锁等待后尝试重新建立连接的机制，那么在发生锁等待或冲突的时候，应用就会不断地发起新的连接，导致MySQL的连接数被占用爆满。MySQL不能在提供连接服务，就挂掉了。
  1. 事务的启动方式有哪几种?
  • begin;--一致性读的视图不会马上创建，而是在执行begin后面的第一个操作innodb表的SQL语句时生成。这个SQL可以是select,update,delete,insert。事务ID也是此时被分配的
  • start transaction;--和begin的功能效果一样。
  • start transaciton with consistent snapshot;--该语句执行后，会马上创建一致性读的视图。这个是它和begin的区别。事务ID也是此时被分配的。
  1. commit work and chain的语法是做什么用的?
  • 提交上一个事务，并且再开启一个新的事务。它的功能等效于：commit + begin。
  1. 怎么查询各个表中的长事务?
  • select * from information_schema.innodb_trx; 这个表中记录了所有正在运行的事务信息，里面有事务的开始时间。可以从这里看出哪些事务运行的时间比较长。
  1. 如何避免长事务的出现?

  • 从数据库方面：

    • 设置autocommit=1，不要设置为0。
    • 写脚本监控information_schemal.innodb_trx表中数据内容，发现长事务，kill掉它。
    • 配置SQL语句所能执行的最大运行时间，如果查过最大运行时间后，中断这个运行事情长的SQL语句。
    • 设置回滚表空单独存放，便于回收表空间。

  • 从业务代码方面：

    • 确认是否使用了autocommit=0的配置，如果有关闭它，然后再业务代码中手动的使用begin;commit来操作。
    • 检查业务逻辑代码，能拆分为小事务的不要用大事务。
    • 检查代码，把没有必要的select语句被事务包裹的情况去掉。

MySQL的general_log日志开启

一、查询日志开启

方法一：

mysql>set global general_log_file='/tmp/general.lg'; -- 设置路径 
mysql>set global general_log=on; -- 开启general log模式 
mysql>set global general_log=off; -- 关闭general log模式 
-- 命令行设置即可,无需重启 在general log模式开启过程中，所有对数据库的操作都将被记录 general.log 文件

方法二： 也可以将日志记录在表中 set global log_output='table' 运行后,可以在mysql数据库下查找 general_log表

二、查询日志关闭 查看是否是开启状态：

mysql> show global variables like '%general%'; -- 关闭 
mysql> set global general_log = off; -- 关闭查询日志
avatar
文章
阅读
粉丝