MySQL学习笔记-事务

香香编程喵喵喵
290 阅读8分钟

本文为极客时间《Mysql实战45讲》的学习笔记，并结合《高性能Mysql》，梳理了索引相关的知识点，总结了一些常见问题，并记录了一些比较实用的方法。

p58.png

隔离级别与存在的问题

隔离级别

在MySQL中有四个标准隔离级别：

  1. 读未提交（read uncommitted）
  2. 读提交（read committed）
  3. 可重复读（repeatable read）
  4. 串行化（serializable ）

隔离级别越高，隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。

  1. 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  2. 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
  3. 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  4. 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

存在的问题

脏读

当数据库中一个事务A正在修改一个数据但是还未提交或者回滚时，另一个事务B 来读取了修改后的内容并且使用了，然后事务A进行了提交，此时就引起了脏读。

此情况仅会发生在： 读未提交的的隔离级别。

幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。详细的讲：事务A第一次读取了一个区间数据，一共有100条。这是事务B又写入了一条，并且提交了。事务A再次同一个区间时，此时一共有101行数据。就发生了幻读。

p59.png

此情况会发生在：读未提交、读提交、可重复读的隔离级别。（有办法解决）

特别说明：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  2. 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”

不可重复读

在一个事务A中读取一行数据，在事务操作过程中(未提交)，事务B对该行数据处理更新，该行数据发生了改变，然后事务A再读取这行数据时，发现数据与第一次不一样了。

此情况仅会发生在：读未提交、读提交的隔离级别。

当前读和快照读

我们首先来了解下‘当前读’和‘快照读’，这两个概念我们后续要用到很多次。

常见的并发情况

  • 并发读，不存在并发安全问题只有性能问题。
  • 并发读写，有并发安全问题，存在脏读，幻读，不可重复读等数据不一致问题。
  • 并发写，存在并发安全问题，也存在性能问题。

快照读

一般情况下，不加锁的select操作，就是快照读。快照的前提是隔离级别不是串行化，在串行化下快照读会退化为当前读。快照读是一种不加锁的非阻塞读， 用以提高并发。它的实现是基于MVCC（多版本并发控制），他的读取到的数据不一定是最新的数据，很有可能是之前的旧数据。

快照读可以做到读取数据时不加锁，这样读写并发就不会有并发安全问题，也不会因为加锁存在性能问题。代价就是增加逻辑上的复杂度。

当前读

  • select for update
  • select lock in share mode
  • update
  • insert
  • delete 这几类操作都是当前读，即读取到当前最新的有效的数据版本，读取时加锁，保证其他事务不会操作当前的数据。

MVCC（多版本并发控制）

MVCC 用到的相关技术

隐式字段

  • trx_id 6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

p61.png

这个字段会在事务开始的时候向 ”InnoDB 的事务系统“申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

引申 trx_id 自增满了，怎么办？

Undo Log

三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。具体的这里不展开讲。

Consistent Read View

事务进行快照读操作的时候生成一个的读视图(Read View)。在该事务开始执行的时候，记录并维护系统当前的trx_id。这个视图记录着对于当前事务，哪些数据版本是可见的，哪些是不可见的。具体而言：

  • 以事务启动的时刻为准，如果一个数据版本是在启动之前生成的，就认；如果是在启动以后才生成的，就不认，必须要找到它的上一个版本
  • 如果上个版本不满足要求，继续往前找。
  • 自己更新的版本，自己还是要认的 在实现上，InnoDB为当前启动的事务维护着一个数组，这个数组里放的是当前已经启动的，但是没有提交的事务ID。这里面最大的ID+1记做高水位，这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

p62.png

MVCC 工作原理概述

MVCC的视图主要是在‘可重复读’和‘读提交’这两种隔离级别下工作的。详细的讲，当我们开启一个事务时，数据库里面会创建一个一致性视图（consistent read view）访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

特别说明： 我们需要注意的是事务的启动时机。通常情况下，begin/start transaction 命令并不是一个事务的起点，在执行到之后的第一个操作 InnoDB 表的SQL语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

第一种启动方式，一致性视图是在执行第一个快照读语句时创建的； 第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

关于幻读问题的详细说明

幻读发生的条件

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  2. 更新操作引发的问题，不能称为幻读。幻读仅专指“新插入的行” 更新一行数据时，会给该行数据加上行锁，但是新插入数据的时候，是在多行数据之间，此时就需要用到间隙锁(Gap Lock)。

引入间隙锁可以有效解决幻读的问题，但是会影响到数据库的效率

总结与引申

事务的启动方式

MySQL 的事务启动方式有以下几种：

  1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
  2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。

#这个SQL 可以获取执行时间超过1分的长事务 
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

trx_id自增满了怎么办？

理论上，只要mysql 跑的足够的久，这个trx_id 一定会出现满了的情况。只不过这个时间非常非常的久。

trx_id 的上限是2的48次方-1 另外，对于一个只读的事务，是不会分配trx_id 的，这样可以降低增长。

avatar
文章
阅读
粉丝