MySQL InnoDB事务

最新推荐文章于 2024-09-24 19:34:03 发布

原创最新推荐文章于 2024-09-24 19:34:03 发布 · 382 阅读

0 ·

CC 4.0 BY-SA版权

mysql原理专栏收录该内容

7 篇文章

订阅专栏

本文深入解析数据库事务的ACID特性，探讨不同隔离级别下事务并发异常的解决方案，以及InnoDB存储引擎如何通过重做日志(redo log)和回滚日志(undo log)保证事务的原子性、一致性和持久性。

事务：

目的：

将数据库从一种一致性状态转换为另一种一致性状态

组成：

事务可以由一条非常简单的 SQL语句组成，也可以由一组复杂的SQL语句组成；

特征：

在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存；

在MySQL innodb 下，每一条语句都是事务；可以通过 set autocommit = 0; 设置当前会话手动提交；

事务控制语句

- - 显式开启事务

START TRANSACTION | BEGIN

- - 提交事务，并使得已对数据库做的所有修改持久化

COMMIT

- - 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改

ROLLBACK

- - 创建一个保存点，一个事务可以有多个保存点

SAVEPOINT identifier

- - 删除一个保存点

RELEASE SAVEPOINT identifier

- - 事务回滚到保存点

ROLLBACK TO [SAVEPOINT] identifier

ACID特性

InnoDB存储引擎中的事务完全符合ACID的特性。ACID是以下4个单词的缩写。

原子性（atomicity) ; 一致性（consistency); 隔离性（isolation); 持久性（durability);

MySQL 的NDB CLuster 引擎来说，虽然其支持事务，但是不满足D（持久性）要求；

InnoDB存储引擎，其默认的事务隔离级别为READ REPEATABLE,完全遵循和满足事务的ACID 特性。

A /原子性（atomicity):

是指整个数据库事务是不可分割的工作单位，只有使事务中所有的数据库操作都执行成功，才算整个事务成功；

通过 undolog 来实现回滚操作。 undolog 记录的是事务每步具体操作，当回滚时，回放事务具体操作的逆运算；

例子： ATM机取款例子

C (consistency),一致性。

是指事务将数据库从一种状态转变为下一种一致的状态。

一致性由原子性，隔离性，持久性共同来维护的。

如：表中有一个字段为姓名，为唯一约束，即在表中姓名不能重复，如果事务对姓名进行了修改，但是事务回滚后，表中的姓名变得非唯一了，这就破坏了事务的一致性要求。

I(isolation)隔离性。

每个读写事务的对象对其他操作的对象能相互分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。

MVCC 是多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读性能。

锁用来处理并发 DML操作；

数据库提供各种粒度锁的策略，针对表(聚集索引B+树)，页(聚集索引B+树叶子节点），行（叶子节点当中某一段记录行）三种粒度加锁。

D(durability) , 持久性。

事务一旦提交，结果就是永久性的。即使发生宕机（数据库）故障，数据库也能将数据恢复。但若不是数据库本身发生故障，而是外部原因，如 RAID卡损坏，自然损害等原因导致数据库发生问题，那么所有提交的数据都可能会丢失。因此持久性保证的是系统的高可靠性（High Reliability),而不是高可用性（High Availability).

事务提交后，事务DML操作将会持久化（写入 redolog 磁盘文件，内容就是哪一个页，页偏移值，具体数据);

事务并发异常

READ UNCOMMITTED 级别

可能出现的问题：脏读，不可重复读，幻读

脏读：事务A 可以读到另外一个事务 B 中未提交的数据；即事务A 读到了脏数据； #这种说的是操作同一个数据库有读有写的情况

在读写分离的场景下，可以将 slave 节点设置为 READ UNCOMMITTED; 此时不会出现脏读。

seq	session A	session B
1	set session transaction isolation level read uncommitted;
2	BEGIN;
3	select * from dirty_read_t where id > 3;
4		set session transaction isolation level read uncommitted;
5		begin;
6		insert into dirty_read_t(id, name, sex, age) values(5, ‘milo’, 2, 20);
7	select * from dirty_read_t where id > 3;
8		Rollback; / commit;
9	select * from dirty_read_t where id > 3;
10	commit;

说明：

7 行发现可以查询到一行数据，这条数据是 session B 第6行插入进来的。 9行再次执行发现就没有了。如果 session A 中没有 3，9句，那么再两个事务完成后，sessionA 实际上是读取了不存在的行数据。

从 3，7，9 行可以看到相同的sql 读取到的内容不一样，这也可以说存在不可重复读的问题或者说重复读取的结果不一样。

READ COMMITTED 级别

这个级别下仍然存在不可重复读的问题，或者说重复读取的结果不一样。当然也存在幻读问题

一般而言，不可重复读的问题是可以接受的，因为读到的是已提交的数据，一般不会带来很大的问题。

所以很多如 Oracle SQL Server 的默认隔离级别就是 READ COMMITTED

seq	session A	session B
1	set session transaction isolation level read committed;
2	BEGIN;
3	select * from dirty_read_t where id > 3;
4		set session transaction isolation level read committed;
5		begin;
6		insert into dirty_read_t(id, name, sex, age) values(5, ‘milo’, 2, 20);
7	select * from dirty_read_t where id > 3;
8		commit;
9	select * from dirty_read_t where id > 3;
10	commit;

说明：

目的验证一下，确实是读不到别的事务未提交的数据。

REPEATABLE READ 级别

存在问题：可能出现幻读

在 REPEATABLE READ 级别及以下存在。但可以在 REPEATABLE级别下通过读加锁（使用next-key locking) 解决。

出现幻读的业务场景可能是：

事务A中先读了，然后以此为依据接下来执行了插入操作。在读和插入之间，别的事务B 也进行了一些操作（比如插入）影响到了事务A的业务。

场景如下：

seq	session A	session B
1	set session transaction isolation level repeatable read;
2		set session transaction isolation level repeatable read;
3	BEGIN;
4	SELECT * FROM t WHERE id = 5;
5		BEGIN;
6		INSERT INTO t (id, name, sex, age)VALUES(5, 'milo', 2, 20);
7		COMMIT;
8	INSERT INTO t (id, name, sex, age)VALUES(5, 'milo', 2, 20);
9	commit;
10

说明：

事务A 中业务逻辑就写成这样了，先读没有这条数据再插入，结果还失败了。事务A 的人可能不知道事务 B的存在。事务A 感觉产生了幻觉明明读了读到了怎么还失败了呢？如果事务A 难以忍受失败，想免受其他事务的打扰，那么就给读加个锁。

解决幻读：给读加锁，使用next-key locking

seq	session A	session B
1	set session transaction isolation level repeatable read;
2		set session transaction isolation level repeatable read;
3	BEGIN;
4	SELECT * FROM t WHERE id = 5 LOCK IN SHARE MODE;
5		BEGIN;
6		INSERT INTO t (id, name, sex, age)VALUES(5, 'milo', 2, 20);
7		COMMIT;
8	INSERT INTO t (id, name, sex, age)VALUES(5, 'milo', 2, 20);
9	commit;
10

说明： session B insert into 会被阻塞住，如下图：

session B 会等 session A 一提交就报错。

最终报错如下：

1062 - Duplicate entry '5' for key 'dirty_read_t.PRIMARY', Time: 15.457000s

另一种情况 session A 迟迟的不commit , 那么 session B 最终会等待超时，错误如下：

1205 - Lock wait timeout exceeded; try restarting transaction, Time: 51.043000s

隔离级别

MySQL innodb 默认支持的隔离级别是 REPEATABLE READ;

READ UNCOMMITTED 读未提交

读未提交；该级别下读不加锁，写加排他锁。

写锁在事务提交或回滚后释放锁。

READ COMMITTED 读已提交

该级别支持 MVCC(多版本并发控制），也就是提供一致性非锁定读；此时读取的是历史快照数据；该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据。

REPEATABLE READ

可重复读，该级别下也支持 MVCC, 此时读取操作事务开始时的版本数据。

MVCC 等待深入了解

SERIALIZABLE

可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行；此时隔离级别最严苛

不同隔离级别下并发异常

隔离级别脏读不可重复读幻读

READ UNCOMMITTED 存在存在存在

READ COMMITTED 不存在存在存在（加锁行不行？？

REPEATABLE READ 不存在不存在存在（可手动给读加锁解决）

serializable 不存在不存在不存在

命令：

- 设置隔离级别

set [ GLOBAL | SESSION ] TRANSACTION ISOLATION LEVEL REPEATABLE READ;

- 或者采用下面的方式设置隔离级别

set @@tx_isolation = ‘REPEATABLE READ’;

set @@global.tx_isolation = ‘REPEATABLE READ’;

- 查看全局隔离级别

select @@global.transaction_isolation;

- 查看当前会话隔离级别

select @@session.transaction_isolation;

select @@transaction_isolation;

- 手动给读加 S 锁

select . . . LOCK IN SHARE MODE;

- 手动给读加 X锁

select … FOR UPDATE;

- 查看当前锁信息

select * from information_schema.innodb_locks

?? 没有 innodb_locks 这个表了

锁

锁机制用来管理对共享资源的并发访问；用来实现事务的隔离级别；

锁类型

三种锁粒度，针对表(B+树)，页(B+树叶子节点)，行(B+树叶子节点当中某一段记录行）

共享锁和排他锁是行级锁

意向共享锁和意向排他锁都是表级别的锁

共享锁(S)

事务读操作的锁；对某一行加锁；

在 serializable 隔离级别下，默认帮读操作加共享锁；

在 repeatable read 级别下，需要手动加共享锁，可解决幻读问题；

在 read committed 级别下，没必要加共享锁，采用的是 MVCC

在 read uncommitted 隔离级别下，即没有加锁也没有使用 MVCC;

排他锁(X)

事务删除或更新加的锁，对某一行加锁；

在4种隔离级别下，都添加了排他锁，事务提交或事务回滚后释放锁

锁的兼容性

注意：意向锁之间是相互兼容的；

当想为某一行添加S 锁，先自动为所在的页和表添加意向锁IS，再为该行添加 S锁；

当想为某一行添加X 锁，先自动为所在的页和表添加意向IX, 再为该行添加 X锁；

锁算法

Record Lock

记录锁，单个行记录上的锁；

Gap Lock

间隙锁，锁定一个范围，但不包含记录本身；全开区间。 repeatable read 级别及以上支持间隙锁；

如果 repeatable read 修改 innodb_locks_unsafe_for_binlog = 0, 那么隔离级别相当于退化为 read committed;

- 查看是否支持间隙锁，默认支持，也就是 innodb_locks_unsafe_for_binlog

SELECT @@innodb_locks_unsafe_for_binlog; ??

Next-key Lock

记录锁 + 间隙锁，锁定一个范围，并且锁住记录本身；左开右闭区间

Insert Intention Lock

在insert 操作的时候产生。

AUTO-INC Lock

自增锁，是一种特殊的表级锁，发生在AUTO_INCREMENT 约束下的插入操作；采用的一种特殊的表锁机制；

锁的对象

行级锁是针对表的索引加锁；索引包括聚集索引和辅助索引；

表级索引是针对页或表进行加锁；

for update 在不同的索引上加什么锁?

例如： InnoDB 在 read committed 和 repeatable read 级别下锁的情况

select * from t where id = 5 for update; lock in share mode

- id 为主键， Read committed 隔离级别
- 在主键id = 5行上加 x 锁

- id 是唯一索引， Read committed 隔离级别
- 在唯一索引 id = 5 行上加 x 锁，在主键索引上对应行加 x 锁

- id 不是索引， read committed 级别
- 在聚集索引上扫描，所有行上加 x锁，此处有个优化，不满足的行在加锁后，判断不满足即可释放锁。

- id 为主键 repeatable read 隔离级别
- 在主键 id=5 行上加x锁

- id 是唯一索引， repeatable read 隔离级别
- 在唯一锁引id=5 行上加 x 锁，在主键索引上对应列加 x 锁

- id 是非唯一索引， repeatable read 隔离级别
- 在非唯一索引上查找id=5行，找到则加上x锁和 GAP锁，然后对应的聚集索引加上 x 锁；
没有找到则加上 GAP锁。

- - id 不是索引， repeatable read 隔离级别

- 在聚集索引上扫描，所有行加上 x 锁和 GAP 锁。

事务的分类：

扁平事务（Flat Transactions)

带有保存点的扁平事务（Flat Transaction with Savepoints);

链事务（ chained Transactions);

嵌套事务（Nested Transactions);

分布式事务（Distributed Transactions);

扁平事务（Flat Transactions): 是最简单的一种，实际生产环境中这可能是使用最为频繁的事务。

扁平事务的三种不同结果。：

成功完成，约占所有事务的96%；应用程序要求停止事务，约占所有事务的3%；强制终止事务，约占所有事务的1%；。

扁平事务的主要限制是不能提交或回滚事务的某一部分。

例子：用户预订旅行度假的事务为：

S1 : 预订杭州到上海的高铁

S2: 上海浦东国际机场坐飞机，预订去米兰的航班。

S3: 在米兰转火车前往佛罗伦萨，预订去佛罗伦萨的火车。

但是当用户执行到S3时，发现由于飞机到达米兰的时间太晚，已经没有当天的火车。这时用户希望在米兰当地住一晚，第二天出发去佛罗伦萨。这时如果为扁平事务，则需要回滚之前S1，S2, S3的三个操作，这个代价有点大。下面的带有保存点的扁平事务可以解决这个问题。

带有保存点的扁平事务（Flat Transaction with Savepoint);

保存点（Savepoint)用来通知系统应该记住事务当前的状态，以便当之后发生错误时，事务能能回到保存点当时的状态。

对于扁平事务，其隐式地设置了一个保存点。保存点用SAVE WORK 函数来建立，通知系统记录当前的状态。

注意：保存点在事务内部是递增的图：返回保存点2以后，下一个保存点可以为3，因为之前的工作都终止了。然而新的保存点编号为 5，这意味着ROLLBACK 不影响保存点的计数。这种单调递增的编号能保持事务执行的整个历史过程，包括在执行过程中想法的改变。

链事务（chained Transaction): 可视为保存点模式的变种，当系统崩溃时，所有的保存点都将消失，因为保存点是易失去的（volatitle) , 非持久的（persistent).

与带保存点不同是，带有保存点的事务能回到任意正确的保存点。而链事务的回滚仅限于当前事务。对锁的处理不同，链事务在执行commit 后即释放了当前事务所持有的锁。

嵌套事务（Nested Transaction) 。顶层事务top-level transaction 控制着各个层次的事务。嵌套事务是由若干事务组成的一棵树，子树既可以是嵌套事务，也可以是扁平事务。

分布式事务（Distributed Transactions) 通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

例子：从ATM机转账。可以将ATM机视为节点A,招商银行的后台数据库视为节点B, 工商银行的后台数据库视为C,这个转账的操作可分解为以下的步骤：

1）节点A 发出转账命令

2）节点B执行存储卡中余额减去 10 000；

3）节点C执行存储卡中的余额加上 10 000;

4) 节点A 通知用户操作完成；或节点A 通知用户操作失败。

对于InnoDB 存储引擎来说，其支持扁平事务，带有保存点的事务，链事务，分布式事务。对于嵌套事务，并不原生支持。然而用户可以通过带有保存点的事务来模拟串行的嵌套事务。

事务的实现：

redo log 称为重做日志，用来保证事务的原子性和持久性。 undo log 用来保证事务的一致性。

redo 恢复提交事务修改的页操作； redo 通常是物理日志，记录的是页的物理修改操作。

undo 回滚行记录到某个特定版本。 undo 是逻辑日志，根据每行记录进行记录。

redo:

两部分组成：一是内存中的重做日志缓冲（redo log buffer), 其是易失的；二是：重做日志文件 redo log file 是持久的。

当事务提交commit时,必须先将事务的所有日志写入到重做日志文件进行持久化。

redo log 基本上都是顺序写的，；而undo log 是需要随机读写的。

为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎都需要调用一次fsync操作。

当然InnoDB存储引擎允许用户手工设置不要立马 fsync 。这样虽然可以提高数据的性能，但是当数据库发生宕机时，由于部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。

参数： innodb_flush_log_at_trx_commit //用来控制重做日志刷新到磁盘的策略。默认值是： 1；

1； //表示事务提交时必须进行一次fsync操作

0； //表示事务提交时不进行 fsync; 但是 master thread 中每1秒会进行一次重做日志文件的 fsync操作。

2； // 表示事务提交时，将重做日志写入重做日志文件，但仅仅写入文件系统的缓存中，不进行fsync操作。

在这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。但是当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件的那部分事务。

重做日志与二进制日志（binlog): 不同

1. 重做日志是在InnoDB 存储引擎层产生，而二进制日志是在MySQL数据库的上层产生的

2. 内容格式不同；二进制日志是一种逻辑日志，其记录的是对应的SQL语句。重做日志是物理格式日志，其记录的是对应的是每个页的修改。

3. 写入磁盘的时间点不同：二进制日志只在事务提交完成后进行一次写入。重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。

重做日志 log block

在InnoDB存储引擎中，重做日志都是以512字节进行存储的；也就是重做日志缓冲，重做日志文件都是以块（block)的方式进行保存的，称为重做日志块。

由于重做日志块的大小和磁盘扇区大小一样，都是512字节，因此重做日志的写入可以保证原子性，不需要 doublewrite 技术。

组成：日志块头（log block header) 及日志块尾（log block tailer) 两部分组成。头占用12字节，重做日志尾部占用8字节。

实际可以存储的大小为 492字节。

LOG_BLOCK_HDR_NO 用来标记这个数组中的位置。其是递增并且循环使用的，占用4个字节。但是由于第一位用来判断是否是flush bit, 所以最大的值为2G。

log group : 重做日志组，其中有多个重做日志文件。因此InnoDB存储引擎实际只有一个log group。

从InnoDB 1.2 版本开始重做日志总大小的限制提高为：512G。

log buffer 根据一定的规则将内存中的log block 刷新到磁盘。这个规则是：

1. 事务提交时

2. 当log buffer 中有一半的内容空间已经被使用时。

3. log checkpoint 时。

log group 中第一个文件。redo log file 的前2KB 的部分不保存log block 的信息。保存 log file header(512字节)； checkpoint1(512字节）； checkpoint2(512字节）； log group 中的其余redo log file 仅保留这些空间，但不保存上述信息。

重做日志格式：

重做日志格式也是基于页的，格式： redo_log_type space page_no redo log body

space //表空间的ID。 page_no //页的偏移量。

redo log body 的部分，根据重做日志类型的不同，会有不同的存储内容。

到InnoDB1.2 版本时，一共有51种重做日志类型。

LSN Log Sequeue Number 的缩写； //日志序列号，占用8字节，并且单调递增。 LSN 表示的含义有：

重做日志写入的总量

checkpoint 的位置

页的版本。

重做日志的 LSN; 表示当前重做日志的LSN为1000，有一个事务T1 写入 100字节的重做日志，那么LSN就变成了1100，若又有事务T2写入了200字节的重做日志，那么LSN就变成了1300。单位是字节。

页中 LSN, ；在每个页的头部，有一个值 FIL_PAGE_LSN, 记录了该页的LSN。页面中的LSN用来判断页是否需要进行恢复操作。

例如：页p1的LSN 为 10000，而数据库启动时，InnoDB检测到重做日志的LSN为13000，并且该事务已经提交，那么数据库需要进行恢复操作，将重做日志应用到 p1页中。

恢复：

InnoDB 存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。因为重做日志记录的是物理日志，因此恢复的速度比逻辑日志，如二进制日志（binlog) 要快很多。

对于insert 操作，其记录的是每个页上的变化。对于下面的表：

create table t( a INT, b INT, PRIMARY KEY(a), KEY(b));

如执行 SQL语句：

insert into t select 1,2;

由于需要对聚集索引页和辅助索引页进行操作，其记录的重做日志大致为：

page(2, 3), offset 32, value 1,2 #聚集索引

page(2,4), offset 64, value 2 #辅助索引

undo

基本概念：事务需要回滚，这时就需要undo.

undo 存放在数据库内部的一个特殊段（segment) 中，这个段称为 undo 段（undo segment) 。这个undo段位于共享表空间内 ibdata1.

undo 是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。但是数据结构和页本身在回滚之后可能不大相同。这是因为在多用户并发系统中，可能会有数千个并发事务。比如一个事务在修改当前一个页中某几条记录,同时还有别的事务在对同一个页中另外几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这会影响其他事务正在进行的工作。

undo 的另一个作用是 MVCC。如当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此来实现非锁定读取。

注意一点： undo log 也会产生 redo log。因为undo log 也需要持久性的保护。

存储：

InnoDB 存储引擎有 n 个 rollback segment ，每个回滚段记录了 1024个undo log segment,

如果 n == 128 ; 那么同时在线事务限制为 128* 1024。