xiaocainiaoya's blog

日常积累

0%

mysql45讲学习总结（三）---事务

发表于 更新于 分类于

mysql45讲学习总结（三）—事务

​ 事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。MySQL是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。

概念

提到事务，你肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

当数据库上有多个事务同时执行的时候，就可能出现：

  • 脏读（dirty read）：无效数据的读出，假设事务T1将某一值修改，这是事务T2读取到这一新值，但是事务T1由于某种原因回滚了新值，这就导致事务T2所读到的新值是无效的。
  • 不可重复读（non-repeatable read）：在一个事务内多次读取同一个数据，在这个数据还没有结束时，另一个事务也访问了同一数据，那么第一个事务在两次读取数据之间由于第二个事务的修改，导致前后读取到的数据不一样，因此称为不可重复读，即原始读取不可重复。
  • 幻读（phantom read）：事务T1对表中的数据进行了条件判断修改，比如set k=1 where k=2，同时事务T2向表中插入一条k=1的数据，那么事务T1再次查询时会发现，存在未修改行。

对应的SQL标准事务隔离级别包括：

  • 读未提交（read uncommitted）：一个事务还没有提交时，它做的变更可以被其他事务看到。
  • 读提交（read committed）：一个事务提交之后，它做的变更才能被其他事务看到。
  • 可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的；未提交的变更对其他事务也是不可见。
  • 串行化（serializable ）：对同一行记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写冲突时，后访问的事务需等待前访问事务释放锁，才可执行。

例子

create table T(c int) engine=InnoDB;

insert into T(c) values(1);

事务A 事务B
启动事务查询得到值1 启动事务
查询得到值1
将1改成2
查询得到值V1
提交事务B
查询得到值V2
提交事务
查询得到值V3

不同隔离级别下V1，V2，V3的返回值：

  • 读未提交：V1=V2=V3=2（V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2）
  • 读提交：V1=1，V2=V3=2（V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2）
  • 可重复读：V1=V2=1，V3=2（V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的）
  • 序列化：V1=V2=1，V3=2（在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2）

在实现上，MYSQL引入视图的概念，访问的时候以视图的逻辑结果为准。

  • 读未提交：可以直接看到未提交事务的变更，不存在视图。
  • 读提交：在每个SQL语句开始执行的时候创建视图。
  • 可重复读：在事务启动时创建，整个事务存在期间都使用这个视图。
  • 序列化：直接使用锁来控制并行访问，不存在视图。

不同的隔离级别，数据库的行为有所不同，Oracle数据库的默认隔离级别是”读提交“，MySQL默认的隔离级别是可重复读，如果是将Oracle迁移到MySql的引用，为保证数据库隔离级别一致，可以将MySQL的隔离级别设置为”度提交“

show variables like ‘transaction_isolation’

将参数transaction-isolation 的值设置成 READ-COMMITTED

使用”可重复读“的场景

​ 假设你在管理一个个人银行账户表。一个表存了每个月月底的余额，一个表存了账单明细。这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

事务隔离的实现

​ 在MYSQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作，记录上的最新值通过一个个回滚操作，可以回到之前的旧值。

假设一个值从1→2→3→4，最后新值为4，那么在回滚日志和视图如下：

事务隔离回滚.png

最新值为4，但是不同时刻启动的事务会产生不同的视图，在在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。 对于视图A来说，要想等到值为1，只需要将最新值依次执行回滚动作。所以即使现在有新事务将4改成5，这个事务对视图A，B，C对应的事务不会存在冲突。当系统中没有比回滚日志更早的视图时，这些回滚日志就会被删除。

所以尽量不要使用长事务，长事务意味着系统里会存在很老的事务视图，导致在这个事务提交之前，这个事务可能用到的回滚记录都必须被保留，也就造成了占用了大量的存储空间。

事务的启动

  1. 显示启动事务语句，begin或者start transaction，结束事务使用commit，回滚使用rollback。
  2. set autocommit=0，关闭这个线程的自动提交，意味着只要执行一个select事务都会启动，并且不会自动提交，只有到显示commit或者rollback或者断开连接。

查询持续时间超过60s的事务：

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

MVCC多版本并发控制

CREATE TABLE t (

id int(11) NOT NULL,

k int(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

insert into t(id, k) values(1,1),(2,2);

事务A 事务B 事务C
start transaction with consistent snapshot;
start transaction with consistent snapshot;
update t set k=k+1 where id = 1;
update t set k=k+1 where id = 1;
select k from t where id =1;
select k from t where id =1;
commit;
commit;

注意：begin/start transaction命令并不是一个事务的起点，在执行到他们之后的第一个操作InnoDB表的语句，事务才真正启动，若想马上启动则使用start transaction with consistent snapshot命令。

事务C没有显示的使用begin/commit，表示这个update语句本身是一个事务，语句在执行完成后会自动提交。

在上述三个事务中：事务A查到的k=1，事务B查到的k=3。

快照

​ 在可重复读隔离级别下，事务在启动的时候就对整个数据库“拍了个快照”。当然了，这个“快照”不是简单理解为拷贝数据，而是通过逻辑手段进行处理。

​ 在InnoDB里面每个事务都有一个唯一的事务ID(transaction id)，在事务开始时向事务组件申请，是严格按照申请顺序递增。

​ 每一行数据都是有多个版本，每次事务更新数据的时候，都会生成一个新的数据版本，并把这个数据版本上的row trx_id标记为这个事务ID，同时旧数据版本，通过undo log来拿到具体旧数据版本。

综上：每一行数据，其实可能有多个版本row，每个版本有自己的row_trx_id。

MVCC例子.png

上图就是一个记录被多个事务更新后的状态，虚线框中是一行数据的4个版本，V4是最新的版本；图中的U1，U2，U3就是undo log，但需要获取到前置版本数据时，就是通过undo log计算出来，比如需要V2的时候，就是通过V4依次执行U3，U2计算得出。(上图中的数据版本所对应的事务都是提交状态，避免有人理解为还能对事务id=15的事务进行回滚。)

所以，某一个事务在启动的时候，会声明：“在我启动时刻为准，如果一个数据版本是在我启动之前生成，我可见；如果是在我启动之后才生成，我不可见，我必须找它之前的我可见的版本；如果是自身事务版本，我也是可见”。

InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃中”(启动还未提交)的所有事务ID。当一个事务启动时，这个数组中的元素就是“活跃中”事务和自身事务(顺序排列)。当判断数据版本可见性时，就通过这个数组来进行判断，比如一个事务的数组为[7,8, 10]（自身事务id=9）：

  • 查询某个数据版本的row trx_id=10，这就表示这个数据版本是在当前事务之后生成，故向前获取版本。
  • 查询某个数据版本的row trx_id=6，比数组中最小的还小，表示是在事务启动前生成的，可见。
  • 查询某个数据版本的row trx_id=7，落在这个数组中，表示这个版本事务还未提交，不可见。
  • 查询某个数据版本的row trx_id=8，比数组中最小的大，但是不落在这个数组中，表示这个版本事务已提交，可见。

根据上述结论，分析之前事务A的查询情况：

  1. 事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
  2. 事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
  3. 三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。

这样，事务 A 的视图数组就是 [99,100], 事务 B 的视图数组是 [99,100,101], 事务 C 的视图数组是 [99,100,101,102]。

MVCC事务A分析.png

从图中可以看到，第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。

第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。

在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成脏读了。

当事务 A 读事务时，由于它的视图数组是 [99,100]。读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：

  • 找到 (1,3) 的时候，判断出 row trx_id=101，比自身数组的最大值大，不可见
  • 找到 (1,2) 的时候，判断出 row trx_id=102，比自身数组的最大值大，不可见
  • 找到 (1,1) 的时候，它的 row trx_id=90，比自身数组的最小值小，可见

综上：

  1. 版本未提交，不可见；
  2. 版本已提交，但是是在视图创建后提交的，不可见；
  3. 版本已提交，而且是在视图创建前提交的，可见。

但是上述实际上只是查询数据时的逻辑，可以将上述分析结果代入事务B，会发现按这种规则代入，事务B中的k值应该为1，而不是2，问题出在事务B在查询之前执行了一次更新操作。

这是因为事务B要去更新数据时，就不能是历史版本上更新了，否则事务C的更新就丢失了，所以事务B此时的更新操作是在事务C的基础上操作的。

当前读：更新数据都是先读后写，这个读，只能读当前的值。

实际上除了update语句，select语句如果加锁，也是当前读。

select k from t where id=1 lock in share mode; // 读锁（S 锁，共享锁）

select k from t where id=1 for update; // 写锁（X 锁，排他锁）

事务A 事务B 事务C
start transaction with consistent snapshot;
start transaction with consistent snapshot;
start transaction with consistent snapshot;
update t set k=k+1 where id = 1;
update t set k=k+1 where id = 1;
select k from t where id =1;
select k from t where id =1;
commit;		 commit
commit;

如果事务C并不是马上提交，而是在事务B执行更新动作之后提交，这时在事务C在执行更新操作时，是获取了这行数据的行锁，事务C在更新之后，事务B的更新操作需要去获取行锁，但是被事务C锁住，故事务B进入等待，直到事务C提交事务后，事务B获取到锁继续往下执行。

综上：可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

  • 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
  • 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。
-------------本文结束感谢您的阅读-------------