【MySQL笔记】事务

简介

事务是一组操作的集合，它是一个不可分割的工作单元，事务会把所有操作作为一个整体向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务的四大特性(ACID)

原子性（Atomicity）

　　　　事务是不可分隔的最小单元，多个操作要么全部成功，要么全部失败。原子性只能保证单个事务的一致性

一致性（Consistency）

　　　　事务结束时，必须所有数据都保持一致，所有内部结构（如B树索引或双向链表）都必须正确

隔离性（Isolation）

　　　　保证事务在不受外部并发操作影响的独立环境下运行。并发执行的事务不会相互影响。由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

持久性（Durability）

　　　　事务一旦提交，对数据库的更新就是持久的

事务的最终目的就是为了保证数据的一致性，所以一致性是事务最重要的特性
原子性是通过undo log保障的
持久性是通过redo log保障的
事务的隔离性是通过锁、MVCC多版本并发控制的方式实现

事务原理

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性
该日志文件由两部分组成：重做日志缓冲、重做日志文件。前者存在内存中，后者存在磁盘中。当事务提交之后会把所有修改信息都存到日志文件中，用于在刷新脏页到磁盘，发生错误时恢复数据使用。

为什么不在事务提交的时候直接持久化数据页，而是持久化redo log日志？因为持久化数据页涉及大量随机磁盘IO，性能低。如果将redo log刷新到磁盘，日志文件是追加的，属于顺序磁盘IO，性能高于随机磁盘IO。这种机制叫WAL（Write-Ahead-Logging）

undo log

回滚日志，用于记录数据被修改（增删改）之前的信息，作用有两个：提供回滚和MVCC（多版本并发控制）
undo log和 redo log记录物理日志不一样，它是逻辑日志。比如当执行delete语句时，undo log中会记录一条insert记录，反之依然。当执行一条update语句时，undo log记录一条反向update语句
undo log销毁：undo log在事务执行时产生，事务提交时并不会立即删除undo log日志，因为这些日志可能还用于MVCC

当insert的时候，产生的undo log日志只在回滚的时候需要，在事务提交后，可被立即删除
而update、delete的时候，产生的undo log日志不仅在回滚的时候需要，在快照读时也需要，不会立即删除

MVCC

多版本并发控制。指维护一个数据的多个版本，读取数据时通过一种类似快照的方式将数据保存下来，这样读写操作就没有冲突了，不同事务session会看到自己特定版本的数据
MVCC提供了一个非阻塞功能。MVCC的具体实现还需要依赖数据库记录中的三个隐式字段、undo log日志、readView
多版本并发控制，在数据库管理系统中实现对数据库的并发访问，它在不同的数据库引擎中有不同的实现。
MySQL中MVCC只能在Repeatable Read、Read Committed这两个隔离级别下工作。Read Uncommited总读取最新数据行，而Serializable则会对所有读取的行加锁。
MVCC通过快照读实现普通读取不加锁，所以读写不会冲突，避免读操作加锁可以大大提高性能
每一个写操作都会创建一个新版本的数据，读操作会从多个版本的数据中挑选一个合适的结果直接返回。因此读写操作之间的冲突就不再需要被关注，而管理和快速挑选数据的版本就成了MVCC需要解决的问题
undo log中的行就是MVCC中的多版本

隐式字段

DB_TRX_ID：最近修改的事务ID，记录插入这条记录或最后一次修改记录的事务ID
DB_ROW_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

基本概念

当前读
在一个事务执行的过程中，如果我们这个时候使用了DML语句，也就是我们平时所说的insert、update、delete语句，此时DML会执行当前读，它们会在操作数据库内容之前，去读取数据库中当前时间点以及提交的最新的数据，基于最新的数据的基础上，再去做这个DML语句自己的SQL逻辑。此时的这个读取数据库中最新已提交的数据的这个动作，就是当前读。
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录加锁，如下操作属于当前读：
select .. lock in share mode(共享锁)，select .. fro update，update，delete都是当前读

快照读
简单的select（不加锁），就是快照读，读取的是记录的可见版本，有可能是历史数据，不加锁，是非阻塞读。
说到快照读，就得说说MVCC。快照是属于MVCC中的一个概念。在RR级别下，MySQL通过MVCC的技术会给每一个事务在启动的时候，创建一个一致性的快照视图，后续数据库中的数据再怎么变化，这个快照的数据内容都不受它们的影响。在这个事务运行过程中的，所有的普通查询都会从这个快照中去获取数据，事务中的这些普通的查询就属于快照读。

• Read Committed：每次select都会生成一个快照读
• Repeatable Read：开启事务后第一个select语句才是快照读的地方
• Serializable：快照读会退化为当前读

幻读
幻读是基于插入的操作而言的。更新、删除操作不属于幻读的范畴，属于不可重复读的范畴。
当前事务在运行的过程中，一开始的时候没有读取到其他事务插入的行，但是后来读取到了其他事务插入数据，这才是幻读。读取到其他事务更新、删除的操作内容，不是幻读，而是不可重复读。

并发事务问题

• 脏读
  在一个事务里读取了另一个未提交的事务中的数据。
  

• 不可重复读
  在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，数据被另一个事务修改并提交了。
  

• 幻读
  幻读和不可重复读类似，幻读强调的是集合的增减，而不是数据的更新。一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这条数据已经存在，就好象发生了幻影。
  

事务的隔离级别

事务的隔离级别就时为了解决并发中存在的问题，事务的隔离级别是通过锁、MVCC的方式实现
MySQL中事务的默认隔离级别是REPEATABLE-READ

• Read uncommitted（读未提交）
  最低隔离级别，以上并发问题都有可能发生
  实现机制：在前文有说到所有写操作都会加排它锁，那还怎么读未提交呢？因为排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是对不加锁的读就不起作用了。READ UNCOMMITTED隔离级别下, 读不会加任何锁。而写会加排他锁，并到事务结束之后释放。
• Read committed（读已提交）
  可防止数据脏读
  实现机制：事务中的修改操作会加排他锁，直到事务提交时才释放锁。读取数据不加锁而是使用了MVCC机制。因此在读已提交的级别下，都会通过MVCC获取当前数据的最新快照，不加任何锁，也无视任何锁(因为历史数据是构造出来的，身上不可能有锁)。
  为什么遗留了不可重复读和幻读问题：MVCC版本的生成时机: 是每次select时。这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读，即：重复读时，会出现数据不一致问题。
• Repeatable read
  实现机制：READ COMMITTED级别不同的是MVCC版本的生成时机，即：一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读。
• Serializable
  可以解决全部事务并发问题
  实现机制：所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。采用的是范围锁RangeS RangeS_S模式，锁定检索范围为只读，这样就避免了幻影读问题。
  Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

隔离级别命令

查看当前数据库隔离级别：

show global variables like '%isolation%';

设置事务隔离级别：

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {Read uncommitted|Read committed|Repeatable read|Serializable}

如下：

set session transaction isolation level read uncommitted;            -- 设置read uncommitted级别
set session transaction isolation level read committed;            -- 设置read committed级别
set session transaction isolation level repeatable read;            -- 设置repeatable read级别
set session transaction isolation level serializable;            -- 设置serializable级别

ADO.NET设置事务隔离级别

var tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted)

事务操作

默认每一条sql语句都是一个事务，事务自动提交，查看事务的提交方式：

select @@autocommit;  -- 1

结果是1，代表自动提交，设置事务不自动提交：

set @@autocommit=0;

提交事务：

COMMIT;

回滚事务：

ROLLBACK;

测试数据：

CREATE TABLE account(
ID int(11) primary key auto_increment comment '主键ID',
name varchar(20) comment '姓名',
money int(11) comment '金额'
)

insert into account(name,money) values('张三',2000);
insert into account(name,money) values('李四',2000);

测试事务的两种方式

在一个会话中，设置事务为不自动提交，执行多条sql属于同一个事务，如下：

set @@autocommit=0;
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';
commit;

第二种方式,显式开启事务，不需要将@@autocommit设置为0：

start transaction;    -- 或 BEGIN
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';
commit;

测试几种隔离级别：

read uncommitted：
打开两个会话，先后执行会话1、会话2中的代码
会话1：

set autocommit=0;                               -- 设置不自动提交
update account set name='fan' where id=1;       -- 将姓名修改为fan，不提交

会话2：

set session transaction isolation level read uncommitted;      -- 将当前会话隔离级别设置为read uncommitted
select * from account where id=1;                          -- 可以读取到会话1修改后的数据

read committed：
会话1：

set autocommit=0;                                          
update account set name='fan' where id=1;   

会话2：

set session transaction isolation level read committed;  -- 将当前会话隔离级别设置为read committed
select * from account where id=1;                        -- 会话2读取到的还是原来的数据，直到会话1提交后，会话2才可以读到修改后的数据

再测试一下是否可以重复读：
会话1：

set session transaction isolation level read committed;  
set autocommit=0;      
select * from account where id=1;                    -- 先执行这个sql，查看结果。然后再执行会话2
select * from account where id=1;                    -- 执行完会话2后再执行一次查询，对比两次结果是否一致

会话2：

update account set name='fan' where id=1;         -- 将姓名修改为fan

结果是会话1中两次查询结果不一致

repeatable read：
会话1：

set session transaction isolation level repeatable read;  
set autocommit=0;      
select * from account where id=1;                          -- 先执行这个sql，查看结果。然后再执行会话2
select * from account where id=1;                          -- 执行完会话2后再执行一次查询，对比两次结果是否一致

会话2：

update account set name='fan' where id=1;         -- 将姓名修改为fan

结果是会话1中两次查询结果一致
再测试一下幻读：
会话1：

set session transaction isolation level repeatable read;  
begin;
select * from account;                          -- 先执行这个sql，查看结果。然后再执行会话2
update account set money=money+100;		-- 修改记录，将会话2中的新增记录也修改了
select * from account;				-- 查询，会话2中新增的记录也查询出来了

会话2：

insert into account(name,money) values('fan',10000);        

出现了幻读，

如果会话1事务中只有快照读，不会发生幻读
也就是说，如果在事务执行过程中，全部都是使用的一致性快照读，他们读取的数据都是从快照视图中读取的数据，此时的数据就是在事务开始的时候创建好的，在事务执行的过程中，任何时候只要是从快照中去读取，那么数据永远都是一样的，不会发生变化。所以说，在快照读的情况下，不会发生幻读

会话1改成如下，可防止幻读：

set session transaction isolation level repeatable read;  
begin;
select * from account lock in share mode;                          -- 先执行这个sql，查看结果。然后再执行会话2
update account set money=money+100;				-- 修改记录，将会话2中的新增记录也修改了
select * from account;						-- 查询，会话2中新增的记录也查询出来了

serializable：
会话1：

set session transaction isolation level serializable; 
set autocommit=0;
select * from account where id=1;

会话2：

update account set name='fan' where id=1; 

执行完会话1，此时会话1未提交，id=1的记录加了读锁，会话2执行update会被阻塞。直到会话1执行了commit;,会话2才会update成功