03 Mysql实战45讲：事务隔离

我很懒
164 阅读7分钟
  • 简单来说：事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在mysql中事务支持是在引擎层实现的。
  • 提到事务，肯定会想到ACID(Atomicity,Consistency,Isolation,Durability)即原子性，一致性、隔离性和持久性

1. 隔离性和隔离级别

  • 首先要知道，隔离级别越高，效率越低。所以在二者之间做权衡
  • SQL标准的隔离级别包括
    • 读未提交：一个事务还没提交时，做的变更就能被别的事务看到。
    • 读已提交：一个事务只有提交后，所做的变更才会被其他事务看到。
    • 可重复读：在同一个事务执行过程中看到的数据，总是跟这个事务在启动的看到的数据是一样的。
    • 串行化：读写都会加锁，当读写冲突时，后访问的事务必须等前一个事务执行完成，才能继续执行。
  • 举个例子
create table t(c int) engine=InnoDB;
insert into t(c) values(1);

image.png

  • 在实现上，数据库会创建一个试图，访问的时候以试图的逻辑结果为准：
    • 读未提交：直接返回记录的最新值，没有这个视图的概念
    • 读已提交：这个试图是在每隔SQL语句开始执行的时候创建
    • 可重复读：这个视图是在事务启动时创建，整个事务存在期间都用这个试图
    • 串行化：直接使用加锁的方式避免并行访问
  • mysql默认的事务隔离级别 REPEATABLE-READ
---查看隔离级别
show variables like 'transaction_isolation';

2. 事务隔离的实现【拿可重复读举例子】

  • 假设一个值从1被按顺序更新成2，3，4，在回滚日志里面会有类似如下回滚段记录 image.png
  • 在不同的时刻，同一条记录存在不同的版本。就是数据库里面的多版本兵法控制（MVCC）,如果要得到初始值1，就需要执行序号3，2，1的回滚操作。
  • 回滚日志删除的条件：当没有事务再需要这些回滚日志时，回滚日志会被删除（就是系统日志里没有比当前回滚日志更早视图的时候）
  • 为什么建议尽量不使用长事务
    • 长事务意味着系统里存在很多老的事务视图，所以在事务提交前，数据库里可能用到的回滚记录必须保留，会占用存储空间
    • 长事务占用锁资源，可能拖垮整个库
  • mysql5.5及以前，回滚日志和数据字典放在ibdata文件里面，即使长事务最终提交，回滚段被清理，文件也不会变小

3. 事务的启动方式

  • 显式启动事务语句，begin或者start transaction；提交语句时commit,回滚是rollback
  • set autocommit=0;这个命令会将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，且不会自动提交，事务存在直到主动commit或者rollback语句，或者断开连接（回滚事务）
  • 有些客户端连接框架默认连接成功后，先执行一个set autocommit=0;导致接下来的查询都在事务中，如果是长连接，导致了意外的长事务
  • 建议使用set autocommit=1,通过显式的语句启动事务。
    • 如果纠结会多一次begin执行，可以考虑commit work and chain语法
    • 但好处是明确知道每个语句是否在事务中
  • 查看长事务:在infomation_schema库中的innodb_trx中查询长事务
--- 查看持续时间超过60s的长事务
SELECT * 
FROM infomation_schema.innodb_trx 
WHERE TIME_TO_SEC(timediff(now(),trx_started))>60;

4. 再谈隔离

示例SQL

---建表语句
CREATE TABLE t(
    id int NOT NULL,
    k int DEFAULT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;
--DML
INSERT INTO t(id,k) values(1,1),(2,2);
  • begin/start transaction并不是一个事务的起点，在执行到他们之后的第一个操作innoDB表的语句事务才会真正启动。
  • 如果想要马上启动事务，可以使用start transaction with consistent snapshot
  • 两种启动事务方式的区别
    • 第一种启动方式，一致性视图实在执行第一个快照读语句时创建的
    • 第二种启动方式，一致性视图实在执行start transaction with consistent snapshot时创建的
  • 在mysql中有两个视图的概念，没有物理结构，作用是：事务执行期间用来定义能看到什么数据
    • view是一个用查询表语句定义的虚拟表，在调用时执行查询语句并生成结果，创建视图的语法是create view...
    • InnoDB在实现MVCC时用到的一致性视图，即consistent read view 用于支持RC(Read commited,读提交)和RR(Repeatable Read，可重复度)隔离级别的实现*
  • 在可重复读隔离级别下，事务在启动的时候就做了个基于整库的快照
  • InnoDB里面每个事务都有唯一的事务id,叫做transaction id,在事务开始的时候向InnoDB的事务系统申请的，是按照顺序严格递增的。每行数据也是有多个版本的，每次事务更新数据时都会生成一个新的数据版本，并把transaction id赋值给这个数据版本的事务id.记为row trx_id,同时旧的数据版本保留，并在新的数据版本中，能够有信息可以可以直接拿到它
  • 数据表中的一行记录，可能会有多个版本，每个版本都有自己的row trx_id
    • undo log：就是下图中的箭头，而v1~v3在物理上并不存在，需要根据undo_log计算 undo log版本链
  • InnoDB为每一个事务构造了数组，如果这个事务自己更新的数据，也是可见的
    • 数组里：事务id最小的记为低水位，当前系统里面已经创建过的事务id最大值加一记为高水位
    • 这个视图数组和高水位组成了当前事务的一致性视图
  • 数据版本的可见性规则：基于数据的row trx_id和这个一致性视图的对比结果得到的
[已经提交的事务]低水位[当前事务：未提交的事务]高水位[未开始的事务]
  • 对于当前事务的启动瞬间来说：一个read view里面的row trx_id有以下几种可能
    • 对于已提交的事务，是可见的
    • 对于未开始的事务，不可见得
    • 对于当前事务：
      • 如果row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
      • 如果row trx_id不在数组中，表示这个版本是已经提交的事务（当前事务），可见
  • 比如，如果有一个事务，低水位是104，则对于这个事务k的值为20
  • 举个例子：
    • 假设事务开始前id=1中row trx_id=100
    • 事务开始前只有一个active事务，id为110
    • 事务A,B,C的事务id为120,130,140 image.png
  • 在这个例子中：事务C没有显式调用begin/commit表示这个语句本身就是一个事务，执行完成后就会自动提交 image.png
  • 事务B能读到k=3主要是因为：更新数据都是先读后写，这个读只能读当前的值，称为当前读
  • select如果加锁也是当前读
--加读锁
SELECT k FROM t WHERE id=1 lock in share mode;
--加写锁，排它锁
SELECT k FROM t WHERE id=1 for update
  • 事务的可重复读是怎么实现的

    • 在可重复读下：在事务开始时创建一致性视图，之后事务里查询都是这个视图

    • 在读提交下，每一个语句执行前都会计算出新的视图，start transaction with consistent snapshot相当于start transaction

avatar
文章
阅读
粉丝