15.1、概述

事务具有四个特征 ACID
1：原子性（Atomicity） 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的 DML 要么全成功，要么全失败。
实现：利用 undo log 回滚日志，

  • (1)当你 delete 一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert 这条旧数据
  • (2)当你 update 一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行 update 操作
  • (3)当年 insert 一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行 delete 操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。
2：一致性（Consistency） 在事务开始之前与结束之后，数据库都保持一致状态。
实现：通过实现其他三个保证一致性
3：隔离性(Isolation) 一个事务不会影响其他事务的运行。
实现：加锁和 MVCC 机制，一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。如果一个事务读取的行正在做 DELELE 或者 UPDATE 操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。
4：持久性(Durability) 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。
实现：redo log 重做日志
采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
事务中存在一些概念：

  1. 事务（Transaction）：一批操作（一组 DML）
  2. 开启事务（Start Transaction）
  3. 回滚事务（rollback）
  4. 提交事务（commit）
  5. SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行 DML 语句是其实就是开启一个事务
关于事务的回滚需要注意：
只能回滚 insert、delete 和 update 语句，不能回滚 select（回滚 select 没有任何意义），对于 create、drop、alter 这些无法回滚.
事务只对 DML 有效果。
注意：rollback，或者 commit 后事务就结束了。

15.2、事务的提交与回滚演示

  1. 创建表
  • create table user(id int(11)) primary key not null auto_increment,username varchar(30),password varchar(30)) ENGINE = InnoDB DEFAULT CHARSET=utf-8
  1. 查询表中数据
  2. 开启事务 START TRANSACTION;
  3. 插入数据
  • insert into user (username,password) values (‘zhangsan’,’123’);
  1. 查看数据
  2. 修改数
  3. 查看数据
  4. 回滚事务
  5. 查看数据

    15.3、自动提交模式

  • 自动提交模式用于决定新事务如何及何时启动。
  • 启用自动提交模式：
    • 如果自动提交模式被启用，则单条 DML 语句将缺省地开始一个新的事务。
    • 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
    • 如果语句执行失败，事务将自动回滚，并取消该语句的结果。
    • 在自动提交模式下，仍可使用 START TRANSACTION 语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。
  • 禁用自动提交模式：
    • 如果禁用自动提交，事务可以跨越多条语句。
    • 在这种情况下，事务可以用 COMMIT 和 ROLLBACK 语句来显式地提交或回滚。
  • 自动提交模式可以通过服务器变量 AUTOCOMMIT 来控制。
  • 例如：

mysql> SET AUTOCOMMIT = OFF；
mysql> SET AUTOCOMMIT = ON；
或
mysql> SET SESSION AUTOCOMMIT = OFF；
mysql> SET SESSION AUTOCOMMIT = ON；
show variables like ‘%auto%’; — 查看变量状态

15.4、事务的隔离级别

当前读与快照读

15.4.1、一致性问题

事务的隔离级别决定了事务之间可见的级别。
当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
（1）脏读取（Dirty Read）：事务 A 读到了事务 B 未提交的数据。
（2）不可重复读（Non-repeatable Read）
事务 A 第一次查询得到一行记录 row1，事务 B 提交修改后，事务 A 第二次查询得到 row1，但列内容发生了变化。
（3）幻像读（Phantom Read）
事务 A 第一次查询得到一行记录 row1，事务 B 提交修改后，事务 A 第二次查询得到两行记录 row1 和 row2。
InnoDB 引擎，可重复读隔离级别，，使用当前读时。
表现：
一个事务(同一个 read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。
幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。
注意：
1、在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，幻读只在当前读下才会出现。 幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。
影响：
会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行。
产生数据一致性问题，在一个事务中，先对符合条件的目标行做变更，而在事务提交前有新的符合目标条件的行加入。这样通过 binlog 恢复的数据是会将所有符合条件的目标行都进行变更的。
原因：
行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。
解决：

  • 将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
  • 间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。

当前读情况下加间隙锁，快照读情况下 mysql 会自动使用 MVCC 机制解决幻读。

15.4.2、四个隔离级别

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：

  • 读未提交（READ UMCOMMITTED）

允许一个事务可以看到其他事务未提交的修改。
原理：写数据时加上排他锁，直到事务结束， 读的时候不加锁。

  • 读已提交（READ COMMITTED）

允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。（Oracle 默认）
原理：写数据的时候加上排他锁， 直到事务结束， 读的时候加上共享锁， 读完数据立刻释放。(共享锁规则 1)

  • 可重复读（REPEATABLE READ）

确保如果在一个事务中执行两次相同的 SELECT 语句，都能得到相同的结果，不管其他事务是否提交这些修改。 （银行总账）该隔离级别为 InnoDB 的缺省设置。
原理：写数据的时候加上排他锁， 直到事务结束， 读数据的时候加共享锁， 也是直到事务结束。(共享锁规则 2)

  • 串行化（SERIALIZABLE） 【序列化】

将一个事务与其他事务完全地隔离。
原理：严格有序执行，事务不能并发执行。
例:A 可以开启事物,B 也可以开启事物
A 在事物中执行 DML 语句时,未提交
B 不以执行 DML,DQL 语句

15.4.3、隔离级别与一致性问题的关系

四、事务 - 图1

15.4.4、设置服务器缺省隔离级别

通过修改配置文件设置

  • 可以在 my.ini 文件中使用 transaction-isolation 选项来设置服务器的缺省事务隔离级别。
  • 该选项值可以是：
    • READ-UNCOMMITTED
    • READ-COMMITTED
    • REPEATABLE-READ
    • SERIALIZABLE
  • 例如：

[mysqld]
transaction-isolation = READ-COMMITTED
通过命令动态设置隔离级别

  • 隔离级别也可以在运行的服务器中动态设置，应使用 SET TRANSACTION ISOLATION LEVEL 语句。
  • 其语法模式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
其中的可以是：

  • READ UNCOMMITTED
    • READ COMMITTED
    • REPEATABLE READ
    • SERIALIZABLE

  • 例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    15.4.5、隔离级别的作用范围

  • 事务隔离级别的作用范围分为两种：

    • 全局级：对所有的会话有效
    • 会话级：只对当前的会话有效
  • 例如，设置会话级隔离级别为 READ COMMITTED ：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

  • 设置全局级隔离级别为 READ COMMITTED ：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

15.4.6、查看隔离级别

  • 服务器变量 tx_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。
  • 为了查看当前隔离级别，可访问 tx_isolation 变量：
  • 1.查看当前会话隔离级别
  • select @@tx_isolation;
  • 2.查看系统当前隔离级别
  • select @@global.tx_isolation;
  • 3.设置当前会话隔离级别
  • set session transaction isolatin level repeatable read;
  • 4.设置系统当前隔离级别
  • set global transaction isolation level repeatable read;

对比 Oracle 查询隔离级别：
1）：

declare
trans_id Varchar2(100);
begin
trans_id := dbms_transaction.local_transaction_id( TRUE );
end;

2）：

SELECT s.sid, s.serial#,CASE BITAND(t.flag, POWER(2, 28))
WHEN 0 THEN ‘READ COMMITTED’
ELSE ‘SERIALIZABLE’ END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context(‘USERENV’, ‘SID’);

15.4.7、并发事务与隔离级别示例

read uncommitted(未提交读) —脏读(Drity Read)：
会话一 会话二
mysql> prompt s1> mysql> use bjpowernode
s1>use bjpowernode mysql> prompt s2>
s1>create table tx ( id int(11), num int (10) );
s1>set global transaction isolation level read uncommitted;
s1>start transaction;
s2>start transaction;
s1>insert into tx values (1,10);
s2>select * from tx;
s1>rollback;
s2>select * from tx;

read committed(已提交读)
会话一 会话二
s1> set global transaction isolation level read committed;
s1>start transaction;
s2>start transaction;
s1>insert into tx values (1,10);
s1>select * from tx;
s2>select * from tx;
s1>commit;
s2>select * from tx;

repeatable read(可重复读)
会话一 会话二
s1> set global transaction isolation level repeatable read;
s1>start transaction; s2>start transaction;
s1>select * from tx;
s1>insert into tx values (1,10);
s2>select * from tx;
s1>commit;
s2>select * from tx;

底层实现

redolog：重做日志，保证了事务的持久性。事务开启后，只要开始改变数据信息就会持续写入 redo buffer 中，具体落盘可以指定不同的策略。在数据库发生意外故障时，尚有修改的数据未写入磁盘，在重启 mysql 服务的时候，根据 redo log 恢复事务修改后的新数据。

脏页：当内存数据页跟磁盘数据页内容不一样的时候，称内存也为脏页。几种情况下会刷脏页：

  • redolog写满了，整个系统不能再接受更新了。
  • 内存不足，需要先将脏页写到磁盘
  • mysql空闲的时候或正常关闭的时候。

Redo buffer 持久化到 Redo log 的策略有三种： 取值 0 每秒一次进行提交持久化[可能丢失一秒内 的事务数据] 取值 1 默认值，每次事务提交执行 Redo buffer —> Redo log OS cache —>flush cache to disk [最安全，性能最差的方式] 取值 2 每次事务提交到系统缓存 OS cache，再每一秒从系统缓存中执行持久化 操作
undolog：回滚日志，用于记录数据被修改前的信息，实现事务的原子性。update 操作会将当前数据加入到 undolog 中，然后使用行中的隐藏字段 DB_ROLL——PTR 回滚字段执行的前一个版本的数据。
binglog 是一个二进制的日志文件，会记录 mysql 的数据更新或潜在个跟新 （delete from table where id =xxx）
主从复制就是依靠 binglog

MVCC

Multi-Version Concurrency Control，多版本并发控制，MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
当前读：读取记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的几率进行加锁。
快照读：不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

实现原理

它的实现原理主要是依赖记录中的 3 个隐式字段，undo 日志 ，Read View 来实现的。
隐式字段：
DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID 等字段

  • DB_TRX_ID 6byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID
  • DB_ROLL_PTR 7byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
  • DB_ROW_ID 6byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以 DB_ROW_ID 产生一个聚簇索引
  • 实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

undo 日志
ReadView 读视图
就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID(当每个事务开启时，都会被分配一个 ID, 这个 ID 是递增的，所以最新的事务，ID 值越大)
如何实现读已提交和可重复读呢？就是生成 ReadView 的时机不同。
对读已提交来说，事务中的每次读操作都会生成一个新的 ReadView，也就是说，如果这期间某个事务提交了，那么它就会从 ReadView 中移除。这样确保事务每次读操作都能读到相对比较新的数据
而对可重复读来说，事务只有在第一次进行读操作时才会生成一个 ReadView，后续的读操作都会重复使用这个 ReadView。也就是说，如果在此期间有其他事务提交了，那么对于可重复读来说也是不可见的，因为对它来说，事务活跃状态在第一次进行读操作时就已经确定下来，后面不会修改了。
通过在每行记录的后边保存两个隐藏的列来实现。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间， 当然存储的并不是实际的时间值，而是系统版本号。

  • undo log :undo log 中记录某行数据的多个版本的数据。
  • read view :用来判断当前版本数据的可见性

MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。不同存储引擎的 MVCC 实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。
新增：会给行数据添加两个隐藏列，数据版本号和删除版本号。数据版本号值为插入时的事务 id，删除版本号默认为 null。
删除：会给行数据的删除版本号设一个当前事务 id 值。
修改：会先拷贝一份行数据，再把原先行数据的删除版本号设值，再修改拷贝的数据，并改变数据版本号值。
查询：必须保证当前事务 ID 大于等于该行数据的数据版本号，并且删除版本号必须为 null 或者大于当前事务 ID 值。
大多数情况下可以代替行级锁，降低系统开销
InnoDB 使用的是行锁。而 InnoDB 的事务分为四个隔离级别，其中默认的隔离级别 REPEATABLE READ 需要两个不同的事务相互之间不能影响，而且还能支持并发，这点悲观锁是达不到的，所以 REPEATABLE READ 采用的就是乐观锁，而乐观锁的实现采用的就是 MVCC。正是因为有了 MVCC，才造就了 InnoDB 强大的事务处理能力。
因为有行 id，事务 id
redo 日志和 undo 日志
用于回滚，先 redo 在 undo
undo 日志记录操作之前的数据
redo 是
mysql 的删除并不是物理上的删除，而是标记……
四、事务 - 图2

当执行查询 SQL 时会生成一致性视图 read-view，他由执行查询时所有未提交事务 id 数组（数组里最小的 id 为 min_id)和已创建的最大事务 id（max_id)组成，查询的数据结果需要跟 read-view 做对比从而得到快照结果：
对比规则：
如果落在绿色部分（trx_is 如果落在红色部分(trx_id > max_id)，表示这个版本是由将来启动的事务生成的，是肯定不可见的，
如果落在黄色部分，那就包括两种情况：

  • 若 row 的 trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见，当前自己的事务是可见的
  • 若 row 的 trx_id 不在数组中，表示这个版本已经提交了事务生成的，可见。

转载 https://www.yuque.com/jykss/jykss/mds7qx#izCti