Oosten Studio

这世界没有一件事情是虚空而生的。站在光里,背后就会有阴影,这深夜一片寂静,是因为你还没有听见声音。

Mysql

分类

事务隔离级别实现原理

1. 前言

数据库隔离级别以及Mysql实操 一文中,我描述了为了解决并发事务间的冲突,实现事务的隔离性,SQL标椎定义了四种隔离级别,今天就通过这篇文章来看下SQL标准中每种隔离级别的实现原理以及InnoDB引擎又是如何实现的。

2. 标准SQL事务隔离级别实现原理

解决并发问题最直觉的方法就是加锁了,而标准SQL事务隔离级别的实现就是依赖于锁的。

隔离级别 实现
未提交读 事务对当前读取到的数据不加锁;事务在更新的瞬间对其加行级共享锁(读锁),直到事务结束才释放。 更新时加共享锁,会阻塞其他事务的更新,但是不会阻塞读。 由于在更新时没有加排他锁(写锁)并且其他事务读的时候也没有尝试加锁,导致其他事务是可以读到修改的,即脏读。
提交读 事务对当前读到的数据加行级共享锁,一旦读完该行就释放锁;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 由于更新时加了排他锁,所以当前事务提交前,其他事务是读不到修改的,这就解决了脏读。 由于读完数据后就释放了锁,所以之后另外一个事务还能修改该行,修改后再读到就是修改之后的数据,这就造成一个事务内读取两次读到的数据是不同的了,即不可重复读。
可重复读 事务开始读取时,对其加行级共享锁,事务结束后才释放;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 由于直到事务结束后才释放读锁,所以在事务结束前,其他事务无法修改该行,所以一个事务多次读取到的数据肯定是相同的,就不会存在不可重复读的问题了。 但是这个隔离级别下,由于只能锁住已存在的行,对insert进来的新数据,还是能读到的,即幻读。
串行化 事务在读取时,加表级共享锁,事务结束后才释放;事务在修改数据时,加表级排他锁。 这个级别下由于加了表锁,所以事务提交前就写不进来新数据,就不存在幻读的问题了。

3. MVCC(Multi-Version Concurrency Control)

通过锁虽然能实现事务间的隔离,但是开销还是太大了,系统性能肯定是扛不起高并发的,为了优化这个问题,尽量避免使用锁,提出了MVCC方式来解决事务并发问题。

3.1 InnoDB的MVCC实现

MVCC在InnoDB中是通过两个隐式字段undo logRead View实现的。

3.1.1 隐式字段

InnoDB会在每一行加上两个隐式字段:

  • DB_TRX_ID: 6bytes,最近修改事务的ID,记录这行记录最后一次修改的事务的ID
  • DB_ROLL_PTR: 7bytes,回滚指针,指向这条记录的上一个版本(存储于rollback segment中)

实际上还有两个字段,但是与MVCC无关。

  • DB_ROW_ID: 隐藏的自增ID(隐藏主键),如果没有主键,则InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  • 一个隐藏的删除flag字段

image.png

3.1.2 undo log

undo log分为两种:

  • insert undo log: 事务在insert时产生,事务提交后可以立即丢弃
  • update undo log:事务在update/delete时产生,不仅在回滚时需要,快照读时也需要,不能随便删除,只有在快照读或者事务不涉及的时候才由purge线程去清除。

purge:为了实现MVCC,删除只是设置下记录的deleted_bit,并不真正删除,InnoDB 有专门的purge线程来回收标记删除的记录,为了不影响MVCC的工作,purge线程也维护一个自己的read view,如果某个记录的DB_TRX_ID相对于purge线程read view可见,那么这条记录就能被安全的删除。

执行流程如下:

1> 比如数据库中当前有一条记录:

| name | age | DB_ROW_ID | DB_TRX_ID | DB_ROLL_PTR | | —- | —- | —- | —- | —- | | n1 | 11 | 1 | 1 | null |

2> 新来一个事务 2修改了记录:update name=n2 where age = 11,流程如下:

  • 事务1修改改行记录时,InnoDB先对改行加排他锁
  • 把当前记录拷贝到undo log中,作为旧记录
  • 拷贝完了后修改name为n2,并且修改记录的DB_TRX_ID为当前事务的id,即:2。DR_ROLL_ID指向undo log中的旧记录,即它的上一个版本
  • 事务提交后,释放锁

image.png

3> 又来一个事务 3修改记录:update name=n3 where age=11,流程如下:

  • 事务1修改改行记录时,InnoDB先对改行加排他锁
  • 把当前记录拷贝到undo log中,作为旧记录,由于该行记录已经有undo log了,那么最新的旧记录作为链表头,插在undo log最前面
  • 拷贝完了后修改name为n3,并且修改记录的DB_TRX_ID为当前事务的id,即:3。DR_ROLL_ID指向undo log中的旧记录,即它的上一个版本
  • 事务提交后,释放锁

image.png

3.1.3 ReadView 读视图

ReadView中有四个比较重要的内容:

  • creator_trx_id: 表示生成该ReadView的事务ID。 (只有在执行insert、update、delete时才会分配事务ID,在一个只读的事务中事务id默认为0)
  • m_ids: 在生成ReadView时所有活跃的事务id集合,活跃事务是指开启还未提交的事务。
  • min_trx_id: m_ids最小值。
  • max_trx_id: 生成ReadView时系统应该分配的下一个事务ID,并非m_ids最大值。

有了这个ReadView,就可以这样判断一条记录是否对该事务可见:

  • 如果被访问版本的trx_id等于creator_trx_id,说明生成该版本的事务就是当前事务,所以可见
  • 如果被访问版本的trx_id小于min_trx_id,说明生成该版本的事务在当前事务生成ReadView前已提交,所以该版本可见
  • 如果被访问版本的trx_id大于等于max_trx_id,表示生成该版本的事务在当前事务生成ReadView之后才开启,所以不可见
  • 如果被访问版本trx_id在min_trx_id与max_trx_id之间,则判断是否在m_ids之中,如果在,说明创建ReadView时生成该版本的事务还活跃,所以不可见;如果不在m_ids中,则说明事务已提交所以可见。

如果某个版本的记录不可见就顺着版本链寻找下一个版本,依次判断是否可见,直到遍历到最后。

3.1.4 MVCC的实现

现在我们已经了解了undo log与ReadView,那么就来看下MVCC到底是如何实操的。

我们假设当前数据结构如下:

image.png

假设 事务20 与 事务30 并发执行,那么对于事务20,它的ReadView中m_ids=[20,30],min_trx_id=20,max_trx_id=31,creator_trx_id=20,对于事务30,它的ReadView 中m_ids=[20,30],min_trx_id=20,max_trx_id=31,creator_trx_id=30

如果此时 事务20 去读取数据,当前版本链中,数据最新版本的DB_TRX_ID为10,它小于 事务20 ReadView的min_trx_id,所以这个版本对 事务20 是可见的。

接着 事务30 修改了这行记录,数据结构就变成了下面这样:

image.png

这时 事务 20 再去读这行记录,当前版本链中,数据最新版本的DB_TRX_ID为30,30在 事务20 的m_ids中,所以这个版本数据对 事务20 不可见,继续顺着版本链读上一个版本,上一个版本DB_TRX_ID为10,可见,所以 事务20 就读到了 上一个版本的数据。

4. 几个概念

在了解InnoDB四种隔离级别的实现之前,我们先明确几个概念

4.1 锁定读和一致性非锁定读

  • 锁定读:在一个事务中主动给读加锁,eg. select … for update(排他锁)、select … lock in share mode(共享锁)
  • 一致性非锁定度:InnoDB通过MVCC向事务提供数据库某个时间点的快照,查询时只能查到当前事务开始前提交的修改,查不到该事务开始之后的修改。就是说事务开始后,事务看到的数据就是事务开始时的数据,后续其他事务的修改在当前事务不可见。

一致性非锁定读是InnoDB在RC和RR两个级别处理SELECT的默认模式,这个过程不用加锁,所以其他事务可以并发修改和读取。

4.2 当前读和快照读

  • 当前读:像update、delete、insert、select … for update、select … lock in share mode,读到的都是当前版本数据,读取时要保证其他并发事务不能修改当前记录,还要加锁
  • 快照读:读到的是快照版本,不加锁的select就是快照读,不加锁。前提是隔离级别不是未提交读和串行化,因为未提交读所有读都是当前读,串行化会对表加锁。

4.3 隐式锁定与显示锁定

  • 隐式锁定 InnoDB在事务执行过程中采用两阶段锁协议,InnoDB根据隔离级别在需要的时候自动加锁,直到事务提交或回滚之后才释放锁,所有的锁都在同一时刻释放。

  • 显示锁定 通过特定的语句显式锁定:

    select ... for update
    select ... lock in share mode
    

5. InnoDB隔离级别实现

InnoDB中,RC与RR两个隔离级别生成ReadView时机是不同的 * RC - 每次读取记录前都生成一个ReadView,而这就导致不可重复读问题 * RR - 在第一次读取时生成一个ReadView,这就解决了可重复读问题

事务隔离级别 实现
未提交读 事务对读都不加锁,都是当前读; 事务在更新的瞬间对其加行级共享锁(读锁),直到事务结束才释放。
提交读 事务对读不加锁,都是快照读;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。
可重复读 事务读不加锁,都是快照读;事务在更新时,加Next-Key Lock直到事务结束才释放
串行化读 事务在读取时,加表级共享锁,直到事务结束才释放,都是当前读;写入时加表级排他锁,直到事务结束才释放

我们再思考两个问题:

5.1 RC级别就是快照读了,那还存在不可重复读的问题吗?

答案是仍然存在,原因是InnoDB在这个级别每次读取记录前都生成一个ReadView。

5.2 很多文章提到InnoDB在RR级别就通过MVCC解决了幻读问题,真的吗?

我们先运行一个例子:

事务A 事务B
begin;
select * from users;

Empty set (0.00 sec)
begin;
insert into users(name,age) values('n1', 1);
commit;
select * from users;

Empty set (0.00 sec)

OK,看起来是解决了,这个例子中事务B的ID>=事务A的ReadView的max_trx_id,所以事务B写入的数据对事务A是不可见的。

不过先别着急下结论,再看下下面的这个例子:

事务A 事务B
begin;
select * from users;

Empty set (0.00 sec)
begin;
insert into users(name,age) values(‘n1’, 1);
commit;
update users set name=‘n2’ where id=1;
select * from users;

+—-+——+——+
| id | name | age |
+—-+——+——+
| 1 | n2 | 1 |
+—-+——+——+
1 row in set (0.00 sec)

这个例子中第二次查询给查出来了,原因在于update是当前读,执行update后生成了一个新的快照,而这个快照对事务A是可见的,所以给查出来了。

如果想第二次select查询结果跟第一次一致,还依赖间隙锁(Gap Lock),事务A的第一个

select * from users;

要显式加锁,即:

select * from users lock in share mode;

这样事务B在执行insert语句时会被阻塞住直到事务A提交。

那么什么是间隙锁呢?

5.3 Gap Lock

举个例子,age字段有普通索引,对于如下sql:

update users set name='n3' where age = 30;

不止会锁住30这一行记录,而且还会锁住两侧的区间(10,30]和(30,positive infinity)

( 表示包括这个, [ 表示不包括这个,间隙锁遵循前开后闭原则,就是说update … age=10,insert age=30的话是不会撞到锁的。

image.png

注意,如果age没有索引,那么会给所有行上一个Gap Lock!但是如果age为唯一索引,就只锁一行了。

5.4 Next-Key Lock

Record Lock与Gap Lock的结合,既锁住行也锁住索引之间的间隙。

参考资料

阅读全文 »

数据库隔离级别以及Mysql实操

1. 事务的ACID

ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability),一个健壮的事务处理系统必须满足这四个特性。

  • 原子性 一个事务必须是一个不可分割的最小执行单元,事务中的所有操作要么都成功,要么失败回滚所有操作。
  • 一致性 数据库总是从一个一致性的状态转移到另一个一致性的状态,事务只要没有提交那么其中的所做的所有修改都不会落地到数据库。比如说A向B转账,A账户钱减少了,B账户钱没有响应增加,这时就处于一个不一致的状态。
  • 隔离性 一般来说一个事务未提交之前,它所做的操作对其他事务是不可见的。不同的隔离级别不可见的部分是不同的。
  • 持久性 事务一旦提交,其所做所有修改都会落地到数据库

2. 隔离级别

SQL标准中定义了四种隔离级别,隔离级别定义了在一个事务中所做的修改,哪些在事务内和事务间是可见的。高级的隔离级别实现起来更复杂,带来的开销也更高,支持的并发也更低。

每种存储引擎实现的隔离级别可能是不同的,可能会在较低的隔离级别上解决该级别的某些问题,从而具有了较高隔离级别的某些能力。例如InnoDB引擎在可重复读的级别上解决了幻读的问题。

  • READ UNCOMMITTED 未提交读 在未提交读级别,可以读到未提交事务中的修改,也被称为脏读。从性能上说该级别不会比其他级别高太多,所以一般不用。
  • READ COMMITTED 提交读 事务未提交的修改其他事务是读不到的,不存在脏读的问题,但是存在不可重复读的问题,即同样的一条查询两次读取读到的数据可能是不同的。
  • REPEATABLE READ 可重复读 可重复读不存在不可重复读的问题,即同样一条查询两次读取读的数据肯定是相同的,但是理论上存在幻读的问题,幻读是指同样一条查询第二次读取可能会读到另外一个事务刚刚新增的记录。不过InnoDB引擎在此级别通过MVCC(多版本并发控制,Multiversion Concurrency Control)解决了幻读的问题。Mysql默认的隔离级别即为该级别。
  • SERIALIZABLE可串行化 可串行化是最高的隔离级别,它通过强制事务串行化执行避免了幻读的问题,性能很差实际很少用。

3. Mysql实操

Mysql版本:Server version: 8.0.18 MySQL Community Server - GPL

3.1 查看mysql当前隔离级别

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

可以看到当前隔离级别为可重复读

3.2 修改mysql隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

如果指定了SESSION则只在该对话中生效,指定了GLOBAL则全局修改隔离级别。下面我们将隔离级别修改为未提交读

mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+

可以看到隔离级别成功被设置为未提交读,下面我们在未提交读的隔离级别下观察下脏读的问题。

3.3 观察脏读问题

我们保持未提交读的隔离级别,然后创建一张实验表,写入两条数据

mysql> CREATE TABLE `t` (
    ->     `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    ->     `age` INT(11) NOT NULL,
    ->     `name` varchar(255) NOT NULL,
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected, 2 warnings (0.21 sec)

insert into `t`(age,name) values(10,'n1');
insert into `t`(age,name) values(11,'n2');

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
|  2 |  11 | n2   |
+----+-----+------+
2 rows in set (0.00 sec)

这时我们开启事务A,然后修改id为1的记录的name为’o1’,但是不要提交事务:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='o1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时我们新开一个窗口,查询下id=1的数据:

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
+----+-----+------+
1 row in set (0.00 sec)

在默认可重复读的隔离级别下读不到事务A的修改。

我们修改隔离级别为未提交读,再查下:

mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+
1 row in set (0.00 sec)

可以看到事务A没有提交,但是我们仍然读到了修改,这就是脏读。

3.4 观察不可重复读问题

我们将事务隔离级别修改为提交读:

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

然后开启事务A,执行一条查询sql:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id =1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
+----+-----+------+
1 row in set (0.00 sec)

然后我们新开一个窗口,修改id=1的记录:

mysql> update t set name='o1' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

然后我们回到事务A,然后重新执行上一条查询:

mysql> select * from t where id =1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+
1 row in set (0.00 sec)

可以看到在一个事务中两次相同查询查到的结果是不同的,这就是不可重复读问题。

3.5 验证不可重复读隔离级别下是否解决了脏读问题

当前表数据为:

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
|  2 |  11 | n2   |
+----+-----+------+
2 rows in set (0.00 sec)

然后开启一个事务将id=1的记录的name改为’n1’,但是不要提交:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='n1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这时在另外一个窗口中查下:

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
|  2 |  11 | n2   |
+----+-----+------+

可以看到此时没有查询到未提交的事务中的修改,就是说提交读隔离级别解决了脏读问题。

3.6 验证可重复读隔离级别是否解决了不可重复读问题

首先将隔离级别修改为可重复读

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

然后我们开启一个事务A,查询下id=1的记录:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+

然后再另一个窗口中修改name为’n1’:

mysql> update t set name='n1' where id =1;
Query OK, 1 row affected (0.01 sec)

这时回到事务A中重新查询下id=1的记录:

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+

可以看到在一个事务中两次读到的是相同的,不可重复读问题已解决。

3.7 验证下InnoDB引擎是否解决了幻读问题

我们将表的存储引擎修改为InnoDB:

mysql> alter table t ENGINE=InnoDB;
Query OK, 3 rows affected (11.52 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

这时我们开启事务A,查询下所有表记录:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
|  2 |  11 | n2   |
|  3 |  12 | t3   |
+----+-----+------+

然后这时在另外一个窗口中新增一条记录:

mysql> insert into t(age,name) value (1, 't10');

执行完成后回到事务A,重新查一下:

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
|  2 |  11 | n2   |
|  3 |  12 | t3   |
+----+-----+------+

可以看到第二次查询跟第一次查询结果是相同的,就是说InnoDB解决了幻读问题。

阅读全文 »
   第 1 页