乐趣区

Mysql基础知识整理笔记事务

jiezi

PS：文章整理的知识内容及资料均来自极客时间《SQL 必知必会》专栏

MySQL 的 InnoDB 引擎支持事务，MyISAM 不支持事务；

事务基础

事务的 4 大特性：ACID

  1. A，也就是原子性（Atomicity）。原子的概念就是不可分割，可以把它理解为组成物质的基本单位，也是我们进行数据处理操作的基本单位，换句话说是：要么完全执行，要么全都不执行；
  2. C，就是一致性（Consistency）。一致性指的就是数据库在进行事务操作后，会由原来的一致状态，变成另一种一致的状态。也就是说当事务提交后，或者当事务发生回滚后，数据库的完整性约束不能被破坏；
  3. I，就是隔离性（Isolation）。它指的是每个事务都是彼此独立的，不会受到其他事务的执行影响。也就是说一个事务在提交之前，对其他事务都是不可见的；
  4. D，指的是持久性（Durability）。事务提交之后对数据的修改是持久性的，即使在系统出故障的情况下，比如系统崩溃或者存储介质发生故障，数据的修改依然是有效的。因为当事务完成，数据库的日志就会被更新，这时可以通过日志，让系统恢复到最后一次成功的更新状态。

持久性是通过事务日志来保证的。日志包括了回滚日志和重做日志。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

事务的常用操作语句

  1. START TRANSACTION 或者 BEGIN，作用是显式开启一个事务。
  2. COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。
  3. ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务。意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。
  4. SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
  5. RELEASE SAVEPOINT：删除某个保存点。
  6. SET TRANSACTION，设置事务的隔离级别。

使用事务有两种方式，分别为隐式事务和显式事务。隐式事务实际上就是自动提交，Oracle 默认不自动提交，需要手写 COMMIT 命令，而 MySQL 默认自动提交，当然我们可以配置 MySQL 的参数：

MySQL 中 completion_type 参数对于事务的作用

  • completion_type=0，这是默认情况。也就是说当我们执行 COMMIT 的时候会提交事务，在执行下一个事务时，还需要我们使用 START TRANSACTION 或者 BEGIN 来开启。
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

运行结果（1 行数据）：

  • completion_type=1，这种情况下，当我们提交事务后，相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务，即当我们提交事务之后会开启一个相同隔离级别的事务（隔离级别会在下一节中进行介绍）。
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

运行结果（2 行数据）：

  • completion_type=2，这种情况下 COMMIT=COMMIT AND RELEASE，也就是当我们提交后，会自动与服务器断开连接。

MySQL 事务隔离

  • 隔离级别能解决的异常情况如下表所示：

三种异常情况的特点：
1、脏读：读到了其他事务还没有提交的数据。（侧重于未提交的数据）
2、不可重复读：对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为有其他事务对这个数据同时进行了修改或删除。（侧重于数据修改，UPDATE 或 DELETE）
3、幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 更改或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候发现会有 N+M 条数据，产生了幻读。（侧重于数据新增，INSERT）

隔离级别越低，意味着系统吞吐量（并发程度）越大，但同时也意味着出现异常问题的可能性会更大。在实际使用过程中我们往往需要在性能和正确性上进行权衡和取舍，没有完美的解决方案，只有适合与否。

模拟异常情况就不作记录了

MySQL 事务隔离级别的实现

MySQL 中的锁

隔离级别的实现是通过锁来完成的，实际上加锁是为了保证数据的一致性，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等），我们就需要保证这个数据在任何时刻最多只有一个线程在进行访问，保证数据的完整性和一致性。

  • 乐观锁

乐观锁大多是基于数据版本记录机制实现，一般是给数据库表增加一个 ”version” 字段。读取数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

  • 悲观锁

悲观锁依靠数据库提供的锁机制实现。MySQL 中的共享锁和排它锁都是悲观锁。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

  • 共享锁（读锁，S 锁）

共享锁指的就是对于多个不同的事务，对于一个资源共享同一个锁。对某一资源加共享锁，自身可可读该资源，其他人也可以读该资源（也可以再加共享锁，即共享锁共享多个内存），但无法修改。要想修改就必须等所有共享锁都释放完之后。语法：SELECT * FROM table lock in share mode;

  • 排它锁（写锁，X 锁）

排它锁指的就是对于多个不同的事务，对同一个资源只能有一把锁。对某一资源加排它锁，自身可以进行增删改查，其他人无法进行加锁操作，更无法进行增删改操作。语法：select * from table for update。

  • 行锁

行锁就是给一行数据进行加锁，操作对象是数据表中的一行（共享锁和排他锁可能是行锁也可能是表锁，取决于对数据加锁的范围，是一行还是整个表）。是 MVCC 技术用的比较多的，但在 MYISAM 用不了，行级锁用 mysql 的储存引擎实现而不是 mysql 服务器。但行级锁对系统开销较大，处理高并发较好。

InnoDB 行锁的 3 种方式：
1、记录锁：针对单个行记录加锁；
2、间隙锁：锁住一个范围（索引之间的空隙），但不包括记录本身，可防止幻读；
3、NEXT-KEY 锁：锁住一个范围，包括记录本身，相当于间隙锁 + 记录锁，可防止幻读

  • 表锁

表锁就是对一张表进行加锁，操作对象是数据表。Mysql 大多数锁策略都支持(常见 mysql innodb)，是系统开销最低但并发性最低的一个锁策略。事务 t 对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。

  • 意向锁

意向锁（Intent Lock），简单来说就是给更大一级别的空间示意里面是否已经上过锁。举个例子，如果我们给某一行数据加上了锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过锁了，这样当其他人想要获取数据表的锁的时候，只需要了解是否有人已经获取了这个数据表的意向锁即可，而不需要逐条记录去判断是否有锁。

MySQL 的 MVCC（多版本并发控制）

  1. 通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
  2. 降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
  3. 解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

快照读和当前读

  • 快照读

不加锁的简单的 SELECT 都属于快照读：

SELECT * FROM table WHERE ...
  • 当前读

当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读：

SELECT * FROM table LOCK IN SHARE MODE;
SELECT * FROM table FOR UPDATE;
INSERT INTO table values ...;
DELETE FROM table WHERE ...;
UPDATE table SET ...;

MVCC 的核心：Undo Log（MV）+ Read View（CC）

InnoDB 中 MVCC 的数据包括 事务版本号 、 行记录中的隐藏列 和Undo Log。

  • 事务版本号

每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号），这个事务 ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。

  • 行记录中的隐藏列
  1. db_row_id：隐藏的行 ID，用来生成默认聚集索引。如果我们创建数据表的时候没有指定聚集索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率。
  2. db_trx_id：操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。
  3. db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。

  • Undo Log

InnoDB 将行记录快照保存在了 Undo Log 里，我们可以在回滚段中找到它们，如下图所示：

从图中能看到回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。

  • Read View

在 MVCC 机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log 里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢？这时就需要用到 Read View 了，它帮我们解决了行的可见性问题。Read View 保存了当前事务开启时所有活跃（还没有提交）的事务列表，换个角度你可以理解为 Read View 保存了不应该让这个事务看到的其他的事务 ID 列表。

Read VIew 几个重要的属性：

  1. trx_ids，系统当前正在活跃的事务 ID 集合。
  2. low_limit_id，活跃的事务中最大的事务 ID。
  3. up_limit_id，活跃的事务中最小的事务 ID。
  4. creator_trx_id，创建这个 Read View 的事务 ID。

如图所示，trx_ids 为 trx2、trx3、trx5 和 trx8 的集合，活跃的最大事务 ID（low_limit_id）为 trx8，活跃的最小事务 ID（up_limit_id）为 trx2。

假设当前的事务 creator_trx_id 想要读取某个行记录，这个行记录的事务 ID 为 trx_id，那么会出现以下几种情况：

  1. 如果 trx_id < 活跃的最小事务 ID（up_limit_id），也就是说这个行记录在这些活跃的事务创建之前就已经提交了，那么这个行记录对该事务是可见的。
  2. 如果 trx_id > 活跃的最大事务 ID（low_limit_id），这说明该行记录在这些活跃的事务创建之后才创建，那么这个行记录对当前事务不可见。
  3. 如果 up_limit_id < trx_id < low_limit_id，说明该行记录所在的事务 trx_id 在目前 creator_trx_id 这个事务创建的时候，可能还处于活跃的状态，因此我们需要在 trx_ids 集合中进行遍历，如果 trx_id 存在于 trx_ids 集合中，证明这个事务 trx_id 还处于活跃状态，不可见。否则，如果 trx_id 不存在于 trx_ids 集合中，证明事务 trx_id 已经提交了，该行记录可见。

当查询一条记录的时候，使用多版本并发控制技术找到对应记录的过程：

  1. 首先获取事务自己的版本号，也就是事务 ID（creator_trx_id）；
  2. 使用 creator_trx_id 获取 Read View；
  3. 查询得到的数据，然后与 Read View 中的事务版本号进行比较；
  4. 如果不符合 Read View 规则，就需要从 Undo Log 中获取历史快照；
  5. 最后返回符合规则的数据。

InnoDB 中，MVCC 是通过 Undo Log + Read View 进行数据读取，Undo Log 保存了历史快照，而 Read View 规则帮我们判断当前版本的数据是否可见。

  • 在隔离级别为读已提交（Read Commit）时，一个事务中的每一次 SELECT 查询都会获取一次 Read View。如表所示：

在读已提交的隔离级别下，同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况。

  • 当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

InnoDB 解决幻读的方法：NEXT-KEY 锁 + MVCC

  • 在读已提交的情况下，即使采用了 MVCC 方式也会出现幻读。

如果我们同时开启事务 A 和事务 B，先在事务 A 中进行某个条件范围的查询，读取的时候采用排它锁，在事务 B 中增加一条符合该条件范围的数据，并进行提交，然后我们在事务 A 中再次查询该条件范围的数据，就会发现结果集中多出一个符合条件的数据，这样就出现了幻读。出现幻读的原因是在读已提交的情况下，InnoDB 只采用了记录锁（Record Locking：即只锁定对应的行记录）。

  • 在隔离级别为可重复读时，InnoDB 会采用 Next-Key 锁的机制，帮我们解决幻读问题。

我们能看到当我们想要插入球员艾利克斯·伦（身高 2.16 米）的时候，事务 B 会超时，无法插入该数据。这是因为采用了 Next-Key 锁，会将 height>2.08 的范围都进行锁定，就无法插入符合这个范围的数据了。然后事务 A 重新进行条件范围的查询，就不会出现幻读的情况。

退出移动版