一, 背景
前面有说到 InnoDB 是事务型引擎, 那什么是事务? 事务的特性是什么? 它所对应的隔离级别是哪些? 是怎么实现的? 下面来详细讨论下.
二, 事务的理解
事务就是一组原子性的 SQL 查询, 或者说一个独立的工作单元. 如果数据库引擎能够成功地对数据库应用该组查询的全部语句, 那么就执行该组语句. 如果其中有任何一条语句因为崩溃或其他原因无法执行, 那么所有的语句都不会执行. 也就是说, 事务内的语句, 要么全部执行成功, 要么全部执行失败.
可以用银行转账的例子来解释事务的必要性. 如果一个银行的数据库有两张表, 支票表和储蓄表, 现在用户张三从他的支票账户转移 200 元到他的储蓄账号, 那么需要发生至少三个步骤:
检查支票账号的余额高于 200 元;
从支票账号余额中减去 200 元;
在储蓄账号余额中增加 200 元.
上述三个步骤的操作必须打包在一个事务中, 任何一个事务的失败, 则必须回滚所有的步骤, 即支票账号和储蓄账号都回到这个转账操作的最初状态.
可以用 start transaction 语句开始一个事务, 然后用 commit 提交事务将修改的数据持久保留或使用 rollback 撤销所有的修改. 事务 SQL 的样本如下:
- start transaction;
- select balance from checking where customer_id=1;
- update checking set balance=balance-200.00 where customer_id=1;
- update savings set balance=balance+200.00 where customer_id=1;
- commit;
单纯的事务不是这个故事的全部. 试想一下, 如果执行到第四条语句时服务器崩溃了, 会发生什么? 可能会损失 200 元. 再假如, 在执行到第三条语句和第四条语句之间时, 另外一个进程要删除支票账号的所有余额, 那么结果可能是银行在不知道这个逻辑的情况下白白给了张三 200 元.
因此引出了下面的概念, 事务的特性, 即除非系统通过严格的 ACID 测试, 否则空谈事务的概念是不够的. 一个良好的事务处理系统, 必须具备这些标准特性.
三, 事务的特性
1. 原子性(atomicity)
一个事务必须视为一个不可分割的最小工作单元, 整个事务中的所有操作要么全部提交成功, 要么全部失败回滚, 对于一个事务来说, 不可能只执行其中的一部分操作, 这就是事务的原子性.
2. 一致性(consistency)
数据库总是从一个一致性的状态转换到另一个一致性的状态. 在前面的例子中, 一致性确保了, 即使在执行第三, 四条语句之间时系统崩溃, 支票账号中也不会损失 200 元, 因为事务最终没有提交, 所以事务中所做的修改也不会保存到数据库中. 一致性可以理解是例子中 ---- 开始的支票账号总数加上储蓄账号总数之和, 等于转账结束后支票账号总数加上储蓄账号总数之和.
3. 隔离性(isolation)
通常来说, 一个事务所做的修改在最终提交之前, 对其他事务是不可见的. 在前面的例子中, 当执行第三条语句, 第四条语句还未开始时, 此时有另外一个账号汇款过来, 则其看到的支票账号余额并没有被减去 200 元. 这当我们后面讨论隔离级别时, 会发现为什么我们要说 "通常来说" 是不可见的.
4. 持久性(durability)
一旦事务提交, 则其所做的修改就会永久保存到数据库中. 比如写 redo log 日志.
一个实现了 ACID 的数据库, 比没有实现 ACID 的数据库, 通常会需要更强的 CPU 处理能力, 更大的内存和更多的磁盘空间. 这也是 MySQL 的存储引擎架构可以发挥优势的地方, 用户可以根据业务来判断是否需要事务处理, 来选择合适的存储引擎. 对于一些不需要事务的查询类应用, 选择一个非事务型的存储引擎, 可以获得更高的性能. 即使存储引擎不支持事务, 也可以通过 LOCK TABLES 加锁和 UNLOCK TABLES 解锁语句来提供一定程度的保护.
四, 隔离级别
1.READ UNCOMMITTED(未提交读)
在 READ UNCOMMITTED 级别, 事务中的修改, 即使没有提交, 对其他事务也都是可见的. 事务读取未提交的数据, 称为脏读(Dirty Read).
2.READ COMMITTED(提交读)
大多数数据库系统的默认隔离级别都是 READ COMMITTED(但 MySQL 不是). 一个事务开始时, 只能 "看见" 已经提交的事务所做的修改. 换句话说, 一个事务从开始直到提交之前, 所做的任何修改对其他事务都是不可见的. 这个级别有时也叫不可重复读(nonrepeatable read), 因为两次执行同样的查询, 可能会得到不一样的结果.[针对 update 更新操作]
3.REPEATABLE READ(可重复读)
REPEATABLE READ 解决了脏读的问题, 保证了在同一个事务中多次读取同样记录的结果是一致的, 但还是存在幻读的问题, 即当某个事务在读取某个范围内的记录时, 另外一个事务又在该范围内插入了新的记录, 当之前的事务再次读取该范围的记录时, 会产生幻行. InnoDB 存储引擎通过多版本并发控制 (MVCC,Multiversion Concurrency Control) 解决了幻读的问题. 该级别是 MySQL 的默认事务隔离级别.[针对 insert 插入操作]
4.SERIALIZABLE(可串行化)
通过强制事务串行执行, 避免了幻读的问题. 简单来说, SERIALIZABLE 会在读取的每一行数据上都加锁, 所以可能导致大量的超时和锁争用的问题."写" 会加 "写锁","读" 会加 "读锁", 当出现读写锁冲突时, 后访问的事务必须等前一个事务执行完成, 才能继续执行.
其中 "提交读" 和 "可重复读" 可能比较难理解, 所以下面用一个例子说明这几种隔离级别. 假设数据表 T 只有一列, 其中一行的值为 1, 下面是按照时间顺序执行两个事务的行为:
我们来看看在不同的隔离级别下, 事务 A 会有哪些不同的返回结果, 也就是图里面的 V1,V2,V3 的返回值分别是什么:
若隔离级别是 "未提交读", 则 V1 的值是 2. 这时候事务 B 虽然还没有提交, 但是结果已经被 A 看到了. 因此, V2,V3 也都是 2.
若隔离级别是 "提交读", 则 V1 是 1,V2 的值是 2. 事务 B 的更新在提交后才能被 A 看到. 所以 V3 的值也是 2.
若隔离级别是 "可重复读", 则 V1,V2 是 1,V3 是 2. 之所以 V2 还是 1, 遵循的就是: 事务在执行期间看到的数据前后必须是一致的.
若隔离级别是 "串行化", 则在事务 B 执行 "将 1 改成 2" 时, 会被锁住. 直到事务 A 提交后, 事务 B 才可以继续执行. 所以从 A 的角度看, V1,V2 的值是 1,V3 的值是 2.
在实现上, 数据库里面会创建一个视图, 访问的时候以视图的逻辑结果为准.
在 "未提交读" 隔离级别下直接返回记录上的最新值, 没有视图概念;
在 "提交读" 隔离级别下, 视图是在每个 SQL 语句开始执行的时候创建的;
在 "可重复读" 隔离级别下, 视图是在事务启动时创建的, 整个事务存在期间都用这个视图; 事务启动时的视图可以认为是静态的, 不受其他事务更新的影响;
在 "串行化" 隔离级别下直接用加锁的方式来避免并行访问.
在不同的隔离级别下, 数据库行为是有所不同的. Oracle 数据库的默认隔离级别是 "提交读", 因此如果你想一些数据从 Oracle 迁移到 MySQL, 为保证数据库隔离级别的一致, 你要将 MySQL 的隔离级别设置为 "提交读". 配置的方式是, 将启动参数 transaction-isolation 的值设置为 READ-COMMITTED. 你可以用 show variables 来查看当前的值.
- MySQL> show variables like 'transaction_isolation';
- +-----------------------+----------------+
- | Variable_name | Value |
- +-----------------------+----------------+
- | transaction_isolation | READ-COMMITTED |
- +-----------------------+----------------+
五, 事务隔离的实现
理解了事务的隔离级别, 那么事务隔离是怎么实现的呢? 这里以 "可重复读" 来展开说明.
在 MySQL 中, 实际上每条记录在更新时都会同时记录一条回滚操作. 记录上的最新值, 通过回滚操作, 都可以得到前一个状态的值.
假设一个值从 1 被按顺序改成了 2,3,4, 在回滚日志里面就有类型下面的记录:
当前值是 4, 但是在查询这条记录的时候, 不同时刻启动的事务会有不同的 read-view. 如图中看到的, 在视图 A,B,C 中, 这一个记录的值分别是 1,2,4, 同一条记录在系统中可以存在多个版本, 这就是数据库的多版本并发控制(MVCC). 对于 read-view A, 要得到 1, 就必须将当前值依次执行图中所有的回滚操作得到.
同时你会发现, 即使现在有另外一个事务正在将 4 改成 5, 这个事务跟 read-view A,B,C 对于的事务是不会冲突的.
回滚日志在不需要的时候会被删除, 即系统会判断, 当没有事务再需要用到这些回滚日志时, 回滚日志会被删除. 那什么时候才不需要了呢? 就是当系统里没有比这个回滚日志更早的 read-view 的时候.
基于上面的说明, 我们来讨论下为什么建议你尽量不要使用长事务.
长事务意味着系统里面会存在很老的事务视图, 由于这些事务随时可能访问数据库里面的任何数据, 所以这个事务提交之前, 数据库里面它可能用到的回滚记录都必须保留, 这就导致大量占用存储空间.
在 MySQL5.5 及以前的版本, 回滚日志是跟数据字典一起放在 ibdata 文件里的, 即使长事务最终提交, 回滚段被清理, 文件也不会变小. 比如数据只有 20GB, 而回滚段有 200GB 的库, 最终只好为了清理回滚段, 重建整个库. 除了对回滚段的影响, 长事务还占用锁资源, 也可能拖垮整个库.
事务的启动方式
显示启动事务语句, begin 或 start transaction. 配套的提交语句是 commit, 回滚语句是 rollback.
set autocommit=0, 这个命令会将这个线程的自动提交关闭. 意味着如果你只执行一个 select 语句, 这个事务就启动了, 而且并不会自动提交. 这个事务持续存在直到你主动执行 commit 或 rollback 语句, 或者断开连接.
有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令, 这就导致接下来的查询都在事务中, 如果是长连接, 就导致了意外的长事务. 因此建议总是使用 set autocommit=1, 通过显示语句的方式来启动事务.
对于一个需要频繁使用事务的业务, 第二种方式每个事务在开始时都不需要主动执行一次 "begin", 减少了语句的交互次数. 如果你顾虑 "多一次交互" 的问题, 建议使用 commit work and chain 语法.
在 autocommit 为 1 的情况下, 用 begin 显式启动的事务, 如果执行 commit 则提交事务, 如果执行 commit work and chain, 则是提交事务并自动启动下一个事务, 这样也省去了再次执行 begin 语句的开销. 同时带来的好处是从程序开发的角度明确每个语句是否处于事务中.
你可以在 information_schema 库的 innodb_trx 这个表查询长事务, 比如下面这个语句, 由于查找持续时间超过 60s 的事务:
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
六, 讨论
前面说了系统里面应该避免长事务, 那么有什么方案来避免出现或者处理这种情况呢?
首先, 从应用开发端来看:
确认是否使用了 set_autocommit=0, 这个确认工作可以在测试环境中开展, 把 MySQL 的 general_log 开起来, 然后随便跑一个业务逻辑, 通过 general_log 的日志来确认;
确认是否有不必要的只读事务. 有些框架会习惯不管什么语句先用 begin/commit 框起来. 把好几个 select 语句放到了事务中, 这种只读事务可以去掉;
业务连接数据库时, 根据业务本身的预估, 通过 SET MAX_EXECUTION_TIME 命令, 来控制每个语句执行的最长时间, 避免单个语句意外执行太长时间.
其次, 从数据库端来看:
监控 information_schema.Innodb_trx 表, 设置长事务阀值, 超过就预警或 kill;
Percona 的 pt-kill 工具不错, 推荐使用;
在业务功能测试阶段要求输出所有的 general_log, 分析日志行为提前发现问题;
如果使用的是 MySQL5.6 或者更新版本, 把 innodb_undo_tablespaces 设置为 2(或者更大的值). 如果真出现大事务导致回滚段过大, 这样设置后清理起来更方便.
来源: https://www.cnblogs.com/huangrenhui/p/12573981.html