本文目录:
1. 事务特性
2. 事务分类
2.1 扁平事务
2.2 带保存点的扁平事务
2.3 链式事务
2.4 嵌套事务
2.5 分布式事务
3. 事务控制语句
4. 显式事务的次数统计
5. 一致性非锁定读(快照查询)
6. 一致性锁定读
7. 事务隔离级别
7.1 设置和查看事务隔离级别
- 7.2 read uncommitted
- 7.3 read committed
- 7.4 repeatable read
- 7.5 serializable
1. 事务特性
事务具有 ACID 特性: 原子性(A,atomicity), 一致性(C,consistency), 隔离性(I,isolation), 持久性(D,durabulity).
原子性: 事务内的所有操作要么都执行, 要么都不执行.
一致性: 事务开始和结束前后, 数据都满足数据一致性约束, 而不是经过事务控制之后数据变得不满足条件或业务规则.
隔离性: 事务之间不能互影响, 它们必须完全的各行其道, 互不可见.
持久性: 事务完成后, 该事务内涉及的数据必须持久性的写入磁盘保证其持久性. 当然, 这是从事务的角度来考虑的的持久性, 从操作系统故障或硬件故障来说, 这是不一定的.
2. 事务分类
扁平事务
带保存点的扁平事务
链事务
嵌套事务
分布式事务
2.1 扁平事务
即最常见的事务. 由 begin 开始, commit 或 rollback 结束, 中间的所有操作要么都回滚要么都提交. 扁平事务在生产环境中占绝大多数使用情况. 因此每一种数据库产品都支持扁平事务.
扁平事务的缺点在于无法回滚或提交一部分, 只能全部回滚或全部提交, 所以就有了 "带有保存点" 的扁平事务.
2.2 带有保存点的扁平事务
通过在事务内部的某个位置使用 savepoint(SQL Server 中称为检查点, 即 checkpoint), 将来可以在事务中回滚到此位置.
MariaDB/MySQL 中设置保存点的命令为:
savepoint [savepoint_name]
回滚到指定保存点的命令为:
rollback to savepoint_name
删除一个保存点的命令为:
release savepoint savepoint_name
实际上, 扁平事务也是有保存点的, 只不过它只有一个隐式的保存点, 且自动建立在事务开始的位置, 因此扁平事务只能回滚到事务开始处.
2.3 链式事务
链式事务是保存点扁平事务的变种. 它在一个事务提交的时候自动隐式的将上下文传给下一个事务, 也就是说一个事务的提交和下一个事务的开始是原子性的, 下一个事务可以看到上一个事务的处理结果. 通俗地说, 就是事务的提交和事务的开始是链接式下去的.
这样的事务类型, 在提交事务的时候, 会释放要提交事务内所有的锁和要提交事务内所有的保存点. 因此链式事务只能回滚到当前所在事务的保存点, 而不能回滚到已提交的事务中的保存点.
2.4 嵌套事务
嵌套事务由一个顶层事务控制所有的子事务. 子事务的提交完成后不会真的提交, 而是等到顶层事务提交才真正的提交.
关于嵌套事务的机制, 主要有以下 3 个结论:
回滚内部事务的同时会回滚到外部事务的起始点.
事务提交时从内向外依次提交.
回滚外部事务的同时会回滚所有事务, 包括已提交的内部事务. 因为只提交内部事务时没有真的提交.
不管怎么样, 最好少用嵌套事务. 且 MariaDB/MySQL 不原生态支持嵌套事务(SQL Server 支持).
2.5 分布式事务
将多个服务器上的事务 (节点) 组合形成一个遵循事务特性 (acid) 的分布式事务.
例如在工行 atm 机转账到建行用户. 工行 atm 机所在数据库是一个事务节点 A, 建行数据库是一个事务节点 B, 仅靠工行 atm 机是无法完成转账工作的, 因为它控制不了建行的事务. 所以它们组成一个分布式事务:
1.atm 机发出转账口令.
2.atm 机从工行用户减少 N 元.
3. 在建行用户增加 N 元.
4. 在 atm 机上返回转账成功或失败.
上面涉及了两个事务节点, 这些事务节点之间的事务必须同时具有 acid 属性, 要么所有的事务都成功, 要么所有的事务都失败, 不能只成功 atm 机的事务, 而建行的事务失败.
MariaDB/MySQL 的分布式事务使用两段式提交协议 (2-phase commit,2PC). 最重要的是, MySQL 5.7.7 之前, MySQL 对分布式事务的支持一直都不完善(第一阶段提交后不会写 binlog, 导致宕机丢失日志), 这个问题持续时间长达数十年, 直到 MySQL 5.7.7, 才完美支持分布式事务. 相关内容可参考网上一篇文章: https://www.linuxidc.com/Linux/2016-02/128053.htm . 遗憾的是, MariaDB 至今(MariaDB 10.3.6) 都没有解决这个问题.
3. 事务控制语句
begin 和 start transaction
表示显式开启一个事务. 它们之间并没有什么区别, 但是在存储过程中, begin 会被识别成 begin...end 的语句块, 所以存储过程只能使用 start transaction 来显式开启一个事务.
commit 和 commit work
用于提交一个事务.
rollbac 和 rollback work
用于回滚一个事务.
savepoint identifier
表示在事务中创建一个保存点. 一个事务中允许存在多个保存点.
release savepoint identifier
表示删除一个保存点. 当要删除的保存点不存在的时候会抛出异常.
rollback to savepoint
表示回滚到指定的保存点, 回滚到保存点后, 该保存点之后的所有操纵都被回滚. 注意, rollback to 不会结束事务, 只是回到某一个保存点的状态.
set transaction 用来设置事务的隔离级别. 可设置的隔离级别有 read uncommitted/read committed/repeatable read/serializable.
commit 与 commit work 以及 rollback 与 rollback work 作用是一样的. 但是他们的作用却和变量 completion_type 的值有关.
例如将 completion_type 设置为 1, 进行测试.
- mysql> set completion_type=1;
- mysql> begin;
- mysql> insert into ttt values(1000);
- mysql> commit work;
- mysql> insert into ttt values(2000);
- mysql> rollback;
- mysql> select * from ttt where id>=1000;
- +------+
- | id |
- +------+
- | 1000 |
- +------+
- 1 row in set (0.00 sec)
begin 开始事务后, 插入了值为 1000 的记录, commit work 了一次, 然后再插入了值为 2000 的记录后 rollback, 查询结果结果中只显示了 1000, 而没有 2000, 因为 commit work 提交后自动又开启了一个事务, 使用 rollback 会回滚该事务.
将 completion_type 设置为 2, 进行测试.
- mysql> set completion_type=2;
- mysql> begin;
- mysql> insert into ttt select 1000;
- mysql> commit;
提交后, 再查询或者进行其他操作, 结果提示已经和 MariaDB/MySQL 服务器断开连接了.
- mysql> select * from ttt;
- ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
4. 显式事务的次数统计
通过全局状态变量 com_commit 和 com_rollback 可以查看当前已经显式提交和显式回滚事务的次数. 还可以看到回滚到保存点的次数.
- mysql> show global status like "%com_commit%";
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | Com_commit | 14 |
- +---------------+-------+
- mysql> show global status like "%com_rollback%";
- +---------------------------+-------+
- | Variable_name | Value |
- +---------------------------+-------+
- | Com_rollback | 24 |
- | Com_rollback_to_savepoint | 0 |
- +---------------------------+-------+
5. 一致性非锁定读(快照查询)
在 innodb 存储引擎中, 存在一种数据查询方式: 快照查询. 因为查询的是快照数据, 所以查询时不申请共享锁.
当进行一致性非锁定读查询的时候, 查询操作不会去等待记录上的独占锁释放, 而是直接去读取快照数据. 快照数据是通过 undo 段来实现的, 因此它基本不会产生开销. 显然, 通过这种方式, 可以极大的提高读并发性.
快照数据其实是行版本数据, 一个行记录可能会存在多个行版本, 并发时这种读取行版本的方式称为多版本并发控制(MVCC). 在隔离级别为 read committed 和 repeatable read 时, 采取的查询方式就是一致性非锁定读方式. 但是, 不同的隔离级别下, 读取行版本的方式是不一样的. 在后面介绍对应的隔离级别时会作出说明.
下面是在 innodb 默认的隔离级别是 repeatable read 下的实验, 该隔离级别下, 事务总是在开启的时候获取最新的行版本, 并一直持有该版本直到事务结束. 更多的 "一致性非锁定读" 见后文说明 read committed 和 repeatable read 部分.
当前示例表 ttt 的记录如下:
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
在会话 1 执行:
- mysql> begin;
- mysql> update ttt set id=100 where id=1
在会话 2 中执行:
- mysql> begin;
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
查询的结果和预期的一样, 来自开启事务前最新提交的行版本数据.
回到会话 1 提交事务:
mysql> commit;
再回到会话 2 中查询:
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
再次去会话 1 更新该记录:
- mysql> begin;
- mysql> update ttt set id=1000 where id=100;
- mysql> commit;
再回到会话 2 执行查询:
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
这就是 repeatable read 隔离级别下的一致性非锁定读的特性.
当然, MySQL 也支持一致性锁定读的方式.
6. 一致性锁定读
在隔离级别为 read committed 和 repeatable read 时, 采取的查询方式就是一致性非锁定读方式. 但是在某些情况下, 需要人为的对读操作进行加锁. MySQL 中对这种方式的支持是通过在 select 语句后加上 lock in share mode 或者 for update.
select ... from ... where ... lock in share mode;
select ...from ... where ... for update;
使用 lock in share mode 会对 select 语句要查询的记录加上一个共享锁(S), 使用 for update 语句会对 select 语句要查询的记录加上独占锁(X).
另外, 对于一致性非锁定读操作, 即使要查询的记录已经被 for update 加上了独占锁, 也一样可以读取, 就和纯粹的 update 加的锁一样, 只不过此时读取的是快照数据而已.
7. 事务隔离级别
SQL 标准定义了 4 中隔离级别: read uncommitted,read committed,repeatable read,serializable.
MariaDB/MySQL 也支持这 4 种隔离级别. 但是要注意的是, MySQL 中实现的隔离级别和 SQL Server 实现的隔离级别在同级别上有些差别. 在后面有必要说明地方会给出它们的差异之处.
MariaDB/MySQL 中默认的隔离级别是 repeatable read,SQL Server 和 oracle 的默认隔离级别都是 read committed.
事务特性 (ACID) 中的隔离性 (I,isolation) 就是隔离级别, 它通过锁来实现. 也就是说, 设置不同的隔离级别, 其本质只是控制不同的锁行为. 例如操作是否申请锁, 什么时候申请锁, 申请的锁是立刻释放还是持久持有直到事务结束才释放等.
7.1 设置和查看事务隔离级别
隔离级别是基于会话设置的, 当然也可以基于全局进行设置, 设置为全局时, 不会影响当前会话的级别. 设置的方法是:
- set [global | session] transaction isolation level {type}
- type:
read uncommitted | read committed | repeatable read | serializable
或者直接修改变量值也可以:
- set @@global.tx_isolation = 'read-uncommitted' | 'read-committed' | 'repeatable-read' | 'serializable'
- set @@session.tx_isolation = 'read-uncommitted' | 'read-committed' | 'repeatable-read' | 'serializable'
查看当前会话的隔离级别方法如下:
- mysql> select @@tx_isolation;
- mysql> select @@global.tx_isolation;
- mysql> select @@tx_isolation;select @@global.tx_isolation;
- +-----------------+
- | @@tx_isolation |
- +-----------------+
- | REPEATABLE-READ |
- +-----------------+
- +-----------------------+
- | @@global.tx_isolation |
- +-----------------------+
- | REPEATABLE-READ |
- +-----------------------+
注意, 事务隔离级别的设置只需在需要的一端设置, 不用在两边会话都设置. 例如想要让会话 2 的查询加锁, 则只需在会话 2 上设置 serializable, 在会话 1 设置的 serializable 对会话 2 是没有影响的, 这和 SQL Server 中一样. 但是, MariaDB/MySQL 除了 serializable 隔离级别, 其他的隔离级别都默认会读取旧的行版本, 所以查询永远不会造成阻塞. 而 SQL Server 中只有基于快照的两种隔离级别才会读取行版本, 所以在 4 种标准的隔离级别下, 如果查询加的 S 锁被阻塞, 查询会进入锁等待.
在 MariaDB/MySQL 中不会出现更新丢失的问题, 因为独占锁一直持有直到事务结束. 当 1 个会话开启事务 A 修改某记录, 另一个会话也开启事务 B 修改该记录, 该修改被阻塞, 当事务 A 提交后, 事务 B 中的更新立刻执行成功, 但是执行成功后查询却发现数据并没有随着事务 B 的想法而改变, 因为这时候事务 B 更新的那条记录已经不是原来的记录了. 但是事务 A 回滚的话, 事务 B 是可以正常更新的, 但这没有丢失更新.
7.2 read uncommitted
该级别称为未提交读, 即允许读取未提交的数据.
在该隔离级别下, 读数据的时候不会申请读锁, 所以也不会出现查询被阻塞的情况.
在会话 1 执行:
- create table ttt(id int);
- insert into ttt select 1;
- insert into ttt select 2;
- begin;
- update ttt set id=10 where id=1;
如果会话 1 的隔离级别不是默认的, 那么在执行 update 的过程中, 可能会遇到以下错误:
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
这是 read committed 和 read uncommitted 两个隔离级别只允许 row 格式的二进制日志记录格式. 而当前的二进制日志格式记录方式为 statement 时就会报错. 要解决这个问题, 只要将格式设置为 row 或者 mixed 即可.
set @@session.binlog_format=row;
在会话 2 执行:
- set transaction isolation level read uncommitted;
- select * from ttt;
- +------+
- | id |
- +------+
- | 10 |
- | 2 |
- +------+
发现查询的结果是 update 后的数据, 但是这个数据是会话 1 未提交的数据. 这是脏读的问题, 即读取了未提交的脏数据.
如果此时会话 1 进行了回滚操作, 那么会话 2 上查询的结果又变成了 id=1.
在会话 1 上执行:
rollback;
在会话 2 上查询:
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
这是读不一致问题. 即同一个会话中对同一条记录的读取结果不一致.
read uncommitted 一般不会在生产环境中使用, 因为问题太多, 会导致脏读, 丢失的更新, 幻影读, 读不一致的问题. 但由于不申请读锁, 从理论上来说, 它的并发性是最佳的. 所以在某些特殊情况下还是会考虑使用该级别.
要解决脏读, 读不一致问题, 只需在查询记录的时候加上共享锁即可. 这样在其他事务更新数据的时候就无法查询到更新前的记录. 这就是 read commmitted 隔离级别.
7.3 read committed
对于熟悉 SQL Server 的人来说, 在说明这个隔离级别之前, 必须先给个提醒: MariaDB/MySQL 中的提交读和 SQL Server 中的提交读完全不一样, MariaDB/MySQL 中该级别基本类似于 SQL Server 中基于快照的提交读.
在 SQL Server 中, 提交读的查询会申请共享锁, 并且在查询结束的一刻立即释放共享锁, 如果要查询的记录正好被独占锁锁住, 则会进入锁等待, 而没有被独占锁锁住的记录则可以正常查询. SQL Server 中基于快照的提交读实现的是语句级的事务一致性, 每执行一次操作事务序列号加 1, 并且每次查询的结果都是最新提交的行版本快照.
也就是说, MariaDB/MySQL 中 read committed 级别总是会读取最新提交的行版本. 这在 MySQL 的 innodb 中算是一个术语:"一致性非锁定读", 即只读取快照数据, 不加共享锁. 这在前文已经说明过.
MariaDB/MySQL 中的 read committed 隔离级别下, 除非是要检查外键约束或者唯一性约束需要用到 gap lock 算法, 其他时候都不会用到. 也就是说在此隔离级别下, 一般来说只会对行进行锁定, 不会锁定范围, 所以会导致幻影读问题.
这里要演示的就是在该级别下, 会不断的读取最新提交的行版本数据.
当前示例表 ttt 的记录如下:
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
在会话 1 中执行:
begin;update ttt set id=100 where id=1;
在会话 2 中执行:
- set @@session.tx_isolation='read-committed';
- begin;
- select * from ttt;
会话 2 中查询得到的结果为 id=1, 因为查询的是最新提交的快照数据, 而最新提交的快照数据就是 id=1.
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
现在将会话 1 中的事务提交.
在会话 1 中执行:
commit;
在会话 2 中查询记录:
- select * from ttt;
- +------+
- | id |
- +------+
- | 100 |
- | 2 |
- +------+
结果为 id=100, 因为这个值是最新提交的.
再次在会话 1 中修改该值并提交事务.
在会话 1 中执行:
begin;update ttt set id=1000 where id=100;commit;
在会话 2 中执行:
- select * from ttt;
- +------+
- | id |
- +------+
- | 1000 |
- | 2 |
- +------+
发现结果变成了 1000, 因为 1000 是最新提交的数据.
read committed 隔离级别的行版本读取特性, 在和 repeatable read 隔离级别比较后就很容易理解.
7.4 repeatable read
同样是和上面一样的废话, 对于熟悉 SQL Server 的人来说, 在说明这个隔离级别之前, 必须先给个提醒: MariaDB/MySQL 中的重复读和 SQL Server 中的重复读完全不一样, MariaDB/MySQL 中该级别基本类似于 SQL Server 中快照隔离级别.
在 SQL Server 中, 重复读的查询会申请共享锁, 并且在查询结束的一刻不释放共享锁, 而是持有到事务结束. 所以会造成比较严重的读写并发问题. SQL Server 中快照隔离级别实现的是事务级的事务一致性, 每次事务开启的时候获取最新的已提交行版本, 只要事务不结束, 读取的记录将一直是该行版本中的数据, 不管其他事务是否已经提交过对应的数据了. 但是 SQL Server 中的快照隔离会有更新冲突: 当检测到两边都想要更新同一记录时, 会检测出更新冲突, 这样会提前结束事务 (进行的是回滚操作) 而不用再显式地 commit 或者 rollback.
也就是说, MariaDB/MySQL 中 repeatable read 级别总是会在事务开启的时候读取最新提交的行版本, 并将该行版本一直持有到事务结束. 但是 MySQL 中的 repeatable read 级别下不会像 SQL Server 一样出现更新冲突的问题.
前文说过 read committed 隔离级别下, 读取数据时总是会去获取最新已提交的行版本. 这是这两个隔离级别在 "一致性非锁定读" 上的区别.
另外, MariaDB/MySQL 中的 repeatable read 的加锁方式是 next-key lock 算法, 它会进行范围锁定. 这就避免了幻影读的问题 (官方手册上说无法避免). 在标准 SQL 中定义的隔离级别中, 需要达到 serializable 级别才能避免幻影读问题, 也就是说 MariaDB/MySQL 中的 repeatable read 隔离级别已经达到了其他数据库产品(如 SQL Server) 的 serializable 级别, 而且 SQL Server 中的 serializable 加范围锁时, 在有索引的时候式锁范围比较不可控(你不知道范围锁锁住哪些具体的范围), 而在 MySQL 中是可以判断锁定范围的(见 innodb 锁算法).
这里要演示的就是在该级别下, 读取的行版本数据是不随提交而改变的.
当前示例表 ttt 的记录如下:
- mysql> select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
在会话 1 执行:
begin;update ttt set id=100 where id=1
在会话 2 中执行:
- set @@session.tx_isolation='repeatable-read';
- begin;select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
查询的结果和预期的一样, 来自开启事务前最新提交的行版本数据.
回到会话 1 提交事务:
commit;
再回到会话 2 中查询:
- select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
再次去会话 1 更新该记录:
begin;update ttt set id=1000 where id=100;commit;
再回到会话 2 执行查询:
- select * from ttt;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- +------+
发现结果根本就不会改变, 因为会话 2 开启事务时获取的行版本的 id=1, 所以之后读取的一直都是 id=1 所在的行版本.
7.5 serializable
在 SQL Server 中, serializable 隔离级别会将查询申请的共享锁持有到事务结束, 且申请的锁是范围锁, 范围锁的情况根据表有无索引而不同: 无索引时锁定整个表, 有索引时锁定某些范围, 至于锁定哪些具体的范围我发现是不可控的(至少我无法推测和计算). 这样就避免了幻影读的问题.
这种问题在 MariaDB/MySQL 中的 repeatable read 级别就已经实现了, MariaDB/MySQL 中的 next-key 锁算法在加范围锁时也分有无索引: 无索引时加锁整个表(实际上不是表而是无穷大区间的行记录), 有索引时加锁部分可控的范围.
MariaDB/MySQL 中的 serializable 其实类似于 repeatable read, 只不过所有的 select 语句会自动在后面加上 lock in share mode. 也就是说会对所有的读进行加锁, 而不是读取行版本的快照数据, 也就不再支持 "一致性非锁定读". 这样就实现了串行化的事务隔离: 每一个事务必须等待前一个事务 (哪怕是只有查询的事务) 结束后才能进行哪怕只是查询的操作.
来源: https://www.cnblogs.com/f-ck-need-u/p/8997814.html