【数据库】数据库事务理论

第6章：数据库事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

6.1 数据库事务介绍

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。
事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

事务命令

--开启事务
START TRANSACTION;

--提交事务
COMMIT;

--回滚事务
ROLLBACK;

--保存点
SAVEPOINT sp1;

--回滚到保存点
ROLLBACK TO sp1;

6.2 JDBC事务处理

数据一旦提交，就不可回滚。
数据什么时候意味着提交？
- 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
- 关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。
JDBC程序中为了让多个 SQL 语句作为一个事务执行：
- 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
- 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
- 在出现异常时，调用 rollback(); 方法回滚事务
若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

【案例：用户AA向用户BB转账100】

public void testJDBCTransaction() {
	Connection conn = null;
	try {
		// 1.获取数据库连接
		conn = JDBCUtils.getConnection();
		// 2.开启事务
		conn.setAutoCommit(false);
		// 3.进行数据库操作
		String sql1 = "update user_table set balance = balance - 100 where user = ?";
		update(conn, sql1, "AA");

		// 模拟网络异常
		//System.out.println(10 / 0);

		String sql2 = "update user_table set balance = balance + 100 where user = ?";
		update(conn, sql2, "BB");
		// 4.若没有异常，则提交事务
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
		// 5.若有异常，则回滚事务
		try {
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
    } finally {
        try {
			//6.恢复每次DML操作的自动提交功能
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
        //7.关闭连接
		JDBCUtils.closeResource(conn, null, null);
    }
}

默认情况下，关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

考虑上事务，所以使用同一个连接，将连接作为参数传入，执行完了也不要关闭连接，直到我们外面调用函数时全部事务功能已经结束（即转账，我减少、你增加），再去关闭连接，提交数据。

原先不考虑事务时的步骤：

获取连接
我减少
关闭连接（提交数据）
获取连接
你增加
关闭连接（提交数据）

考虑事务时的步骤：

获取连接
我减少
你增加
关闭连接（提交数据）

其中，对数据库操作的方法为：

//使用事务以后的通用的增删改操作（version 2.0）
public void update(Connection conn ,String sql, Object... args) {
	PreparedStatement ps = null;
	try {
		// 1.获取PreparedStatement的实例 (或：预编译sql语句)
		ps = conn.prepareStatement(sql);
		// 2.填充占位符
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		// 3.执行sql语句
		ps.execute();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// 4.关闭资源
		JDBCUtils.closeResource(null, ps);

	}
}

6.3 事务的ACID属性

注意：事务不是锁！

事务：事务能保证原子性（都成功或者都失败），但是跟锁没有关系！事务的隔离性有多种隔离级别可以选择。

锁：锁并不能保证原子性，锁只能保证隔离性！

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

就是把事务分割成像原子一样，表示我们事务需要细微的去控制。比如我给你转钱，里面有我扣钱，你到账。总不能我扣了钱，你没到账这种情况吧。所以就是指转账这个事务，里面的所有环节哪怕一个出错，都需要事务回滚，就是一切回到之前那样。
注意：这里的事务原子性，跟CPU指令的原子指令的原子性不同。
事务原子性是要么都成功，要么都失败；
CPU原子指令是 CPU 指令级的“读-改-写不可分割”，靠物理硬件保证原子性，最小指令。
一致性（Consistency）
事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
还是转账来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。
隔离性（Isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
关于事务的隔离性数据库提供了4种隔离级别，稍后会介绍到。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

只有满足一致性，事务的执行结果才是正确的。

在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。

在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。

事务满足持久化是为了能应对系统崩溃的情况。

6.3.1 数据库的并发问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
- 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
没有被提交的数据别人随时有可能回滚，所以是临时且无效的。
数据被未被提交的数据污染了，脏了。
- 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
不可重复地进行读取，因为读取前后不一。
- 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。
就像出现了幻觉，多了几个数据。
数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

6.3.2 事务的四种隔离级别

数据库提供的4种事务隔离级别：
Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

6.3.3 在MySql中设置隔离级别

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
查看当前的隔离级别：
```
SELECT @@tx_isolation;
```

设置当前 mySQL 连接的隔离级别：

set  transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

补充操作：

创建mysql数据库用户：

create user tom identified by 'abc123';

授予权限

#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
grant all privileges on *.* to tom@'%'  identified by 'abc123'; 

 #给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';

数据库并发性问题与解决方式

数据库并发性问题

数据库并发场景有三种，分别为：

读读：不存在任何问题，也不需要并发控制
读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、不可重复读、幻读
写写：有线程安全问题，可能存在更新丢失问题

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事物开始前的数据库快照，所以MVCC可以为数据库解决以下问题：

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作时也不用阻塞读操作，提高了数据库并发读写的性能
解决脏读、不可重复读、幻读等事物隔离问题，但是不能解决更新丢失问题

脏读：（读取了未提交的新事务，然后被回滚了）

事务A读取了事务B中尚未提交的数据。如果事务B回滚，则A读取使用了错误的数据。

比如我给你转了100万，但是我还没有提交，此时你查询自己账户，多了100万，很开心。然后我发现转错人了，回滚了事务。然后你100万就没了。在过程中你查到了没有提交事务的数据（多出的100万），这就是脏读。

解决：如果一个事务在读的时候，禁止读取未提交的事务。是不是就解决了。

不可重复读：（读取了提交的新事务，指更新操作）

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了另一事务提交的数据。

在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如同一个事务前后两次查询同一个数据，期望两次读的内容是一样的，但是因为读的过程中，因为令一个数据写了该数据，导致不可重复读。

解决：如果一个事务在读的时候，禁止任何事务写。是不是就解决了。

幻读：（也是读取了提交的新事务，指增删操作）

在事务A多次读取构成中，事务B对数据进行了新增操作，导致事务A多次读取的数据不一致。

幻读和不可重复读的区别在于，不可重复是针对记录的update操作，只要在记录上加写锁，就可避免；幻读是对记录的insert操作，要禁止幻读必须加上全局的写锁(比如在表上加写锁)。

解决：如果一个事务加上表级锁，只要有任何东西操作这个表的时候，禁止任何操作的并发

另外说一下两类丢失更新：

第一类事务丢失：（回滚丢失）

对于第一类事务丢失，就是比如A和B同时在执行一个数据，然后B事务已经提交了，然后A事务回滚了，这样B事务的操作就因A事务回滚而丢失了。

举个例子，比如我又1000元。买一个东西，花了100元。然后我朋友给我转了1000元。理论上这两个事务正常的话，我应该还有1900元。

但是比如现在两个A，B事务同时进行，第一步都先查询我余额还有1000元，然后B事务给我转了1000元，提交了，理论上我还有2000元。

然后我买东西，100元的，买到一半，我事务回滚，就回滚成了1000元（回滚丢失）。

如果我不回滚，也提交了，我就还剩900元（也就是下面介绍的第二类事务丢失，覆盖丢失）。

第二类事务丢失：（覆盖丢失）

对于第二类事务丢失，也称为覆盖丢失，就是A和B一起执行一个数据，两个同时取到一个数据，然后B事务首先提交，但是A事务加下来又提交，这样就覆盖了B事务，称为第二类事务丢失，覆盖丢失。

解决

对上面脏读，不可重复读，幻读，第一类事务丢失（回滚丢失），第二类事务丢失（覆盖丢失），有什么预防方法呢？

接下来讲事务的4个隔离级别：

从低到高开始：

隔离级别	脏读	不可重复读	幻读
read uncommitted（未提交读）	√	√	√
read committed（提交读）	×	√	√
repeatable read（可重复读）	×	×	√
serialization（可串行化）	×	×	×

可以看到，我们的四种隔离级别就是为了解决上面的三个问题。

1.未提交读(Read uncommitted)（写加锁，读不加锁）

写操作加写锁，读操作不加锁。禁止第一类丢失更新，但是会出现所有其他数据并发问题。

2.提交读(Read committed)（写加锁，读当前快照）

提交读的读操作是读当前读操作时刻的快照，到本次读操作结束。

写操作加写锁，读操作不加锁，但是是读当前快照。禁止第一类丢失更新和脏读。

就是你已经开始读了数据，然后一个事务开始写，然后写的事务不提交的话，是不能进行读的事务，避免了脏读。

3.可重复读(Read repeatable)（写加锁，读事务快照）

可重复读的读操作是读当前事务操作时刻的快照，到事务结束。

对于读操作是读当前事务操作时刻的快照。和提交读的区别在于，提交读的读操作是读当前读操作时刻的快照，而可重复读的快照是当前事务开始时的快照，在事务进行中无论执行多少次读操作，读到的都是当前事务开始时的那个快照，不会改变，不会有不可重复读的问题。禁止两类丢失更新，禁止脏读和不可重复度，但是可能出现幻读。

一个事务读的时候，我们把两次读看成整体，在读的过程中，其他事务的写操作不会影响当前事务的读操作，这样就可以禁止不可重复读。

这是大部分关系数据库的默认隔离级别。

4.序列化(Serializable)（对行级读写加锁）

读操作加行级读锁至事务结束。可以禁止两类丢失更新，禁止脏读，不可重复度和幻读。

隔离级别实例

提到事务，你肯定会想到ACID（Atomicity、Consistency、Isolation、Durability，即 原子性、一致性、隔离性、持久性），接下来我们就要讲解其中的I，也就是隔离性。

当数据库上存在多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了隔离级别的概念。

我们知道，隔离级别越高，效率就越低，因此我们很多情况下需要在二者之间找到一个平衡点。

SQL标准的事务隔离级别包括：

读未提交（read uncommitted）
读提交（read committed）
可重复读（repeatable read）
串行化（serializable ）

下面我逐一为你解释：

读未提交：事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读。这个级别会导致很多问题，从性能上来说也不会比其他隔离级别好很多，但却缺乏其他级别的很多好处，一般实际应用中很少用，甚至有些数据库内部根本就没有实现。
读已提交：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，这个级别有时候也叫做不可重复读（Nonrepeatable Read）,因为同一事务中两次执行同样的查询，可能会得到不一样的结果
可重复读：同个事务中多次查询结果是一致的，解决了不可重复读的问题。此隔离级别下还是无法解决另外一个幻读（Phantom Read）的问题，幻读是指当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，之前的事务再次读取该范围的记录时，会产生幻行
串行化：顾名思义是对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

对于上面的概念中，可能 读已提交 和 可重复读 比较难理解，下面会用一个例子说明这种集中隔离级别。

假设数据表T中只有一列，其中一行的值为1，下面是按照时间顺序执行两个事务的行为。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

接下来讲解不同的隔离级别下，事务A会有哪些不同的返回结果，也就是图里面V1、V2、V3的返回值分别是什么。

若隔离级别是读未提交，则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2。
若隔离级别是读已提交，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以，V3的值也是2。
若隔离级别是可重复读，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
若隔离级别是串行化，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看，V1、V2值是1，V3的值是2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

在读已提交隔离级别下，这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，读未提交隔离级别下直接返回记录上的最新值，没有视图概念；而串行化隔离级别下直接用加锁的方式来避免并行访问。

注意一下，每种数据库的行为会有所不一样，Oracle数据库的默认隔离界别是读已提交，因此，当我们需要进行不同数据库种类之间迁移的时候，为了保证数据库隔离级别的一致，切记将MYSQL的隔离级别设置为读已提交。

操作

最后简单的给大家操作一下如何设置

session 表示当前表，global表示整个全局

select @@[session | global].tx_isolation;  //查看当前的隔离级别

set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} 

//默认级别是repeatable read

事务启动方式

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

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

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。

因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

但是有的开发同学会纠结多一次交互的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 begin，减少了语句的交互次数。如果你也有这个顾虑，我建议你使用 commit work and chain语法。

在 autocommit为1的情况下，用 begin显式启动的事务，如果执行 commit则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在 information_schema库的 innodb_trx这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事务隔离的实现

数据库的读操作分为两类，一是当前读，使用锁机制；一是快照读，使用mvcc（Multi-Version Concurrency Control, 多版本并发控制）

当前读

数据的修改操作（insert update delete）和查询时显示加锁 select(查询条件后加上 lock in share mode & for update)
会锁住要读取的数据以保障数据的一致

快照读使用的是mvcc机制，就是多版本并发控制。

除当前读之外，普通的select查询为快照读，顾名思义，就是读取的是一个快照版本，以隔离多个事务之间的数据

接下来以可重复读来展开事务隔离具体是怎么实现的。

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。

可以看到当前值是4，从图中可以看到在查询的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。同时你会发现，即使现在有另外一个事务正在将4改成5，这个事务跟read-view A、B、C对应的事务是不会冲突的。

你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？

这是肯定不能一直保留的，在不需要的时候才删除。系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

那么什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view的时候。

基于上面的说明，我们来讨论一下为什么建议你尽量不要使用长事务。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在 ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB的库。最终只好为了清理回滚段，重建整个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。

多版本并发控制

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

基本思想

在封锁一节中提到，加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

版本号

系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号 TRX_ID ：事务开始时的系统版本号。

Undo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

例如在 MySQL 创建一个表 t，包含主键 id 和一个字段 x。我们先插入一个数据行，然后对该数据行执行两次更新操作。

INSERT INTO t(id, x) VALUES(1, "a");
UPDATE t SET x="b" WHERE id=1;
UPDATE t SET x="c" WHERE id=1;

因为没有使用 START TRANSACTION 将上面的操作当成一个事务来执行，根据 MySQL 的 AUTOCOMMIT 机制，每个操作都会被当成一个事务来执行，所以上面的操作总共涉及到三个事务。快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。

INSERT、UPDATE、DELETE 操作会创建一个日志，并将事务版本号 TRX_ID 写入。DELETE 可以看成是一个特殊的 UPDATE，还会额外将 DEL 字段设置为 1。

ReadView

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, ...}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。

在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
- 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
- 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

快照读与当前读

1. 快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

SELECT * FROM table ...;

2. 当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

INSERT;
UPDATE;
DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;

MVCC工作原理

可重复读隔离级别下，事务在启动的时候就“拍了个快照” 。请注意，这个快照是基于整个库的，这时候你肯定觉得不可思议，如果一个库上百G的数据，那么我启动一个事务，那MYSQL岂不是要将上百G的数据拷贝出来，这个过程不是非常慢吗？但是为什么我们平时并没有感觉到它🈵️呢？

事实上，我们并不需要拷贝出这100G的数据。

我们先来看看这个快照是怎么实现的。InnoDB里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB的事务系统申请的，是按申请顺序严格递增的。

每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。这也说明了，数据表中的一行记录，可能存在多个版本（row）,每个版本有自己的 row_trx_id.

下面用一张图说明一个记录被多个事务连续更新后的状态，如下图所示：

图中用打括号表示一行数据的4个版本，当前最新版本是V4，k的值是12，它是被 transaction id 为25的事务更新的，因此它的 row trx_id也是25。

你可能会问，前面的文章不是说，语句更新会生成 undo log（回滚日志）吗？那么，undo log在哪呢？

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

明白了多版本和row trx_id的概念后，我们再来想一下，InnoDB是怎么定义那个“100G”的快照的。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

因此，一个事务只需要在启动的时候声明说， 以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本 。

当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。在实现上， InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。

这个视图数组把所有的row trx_id 分成了几种不同的情况。如下图所示：

上图是数据库版本可见性规则，对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在灰色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在粉色部分，那就包括两种情况
- (a) 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
- (b) 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

比如，对于图2中的数据来说，如果有一个事务，它的低水位是21，那么当它访问这一行数据时，就会从V4通过U3计算出V3，所以在它看来，这一行的值是11。

你看，有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？因为之后的更新，生成的版本一定属于上面的2或者3(a)的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。

所以你现在知道了，InnoDB利用了所有数据都有多个版本的这个特性，实现了“秒级创建快照”的能力。

接下来我们用一个例子来巩固一下MVCC的知识，例子如下：

下面是一个只有两行的表的初始化语句。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

还需要注意的是，我们的例子中如果没有特别说明，都是默认 autocommit=1。

在这个例子中，事务C没有显式地使用 begin/commit，表示这个 update语句本身就是一个事务，语句完成的时候会自动提交。事务B在更新了行之后查询; 事务A在一个只读事务中查询，并且时间顺序上是在事务B的查询之后。

让我们想一下图中的三个事务，分析一下事务A的语句返回的结果是什么？

答案： 事务B查到的k的值是3，而事务A查到的k的值是1 ，是不是感到有点奇怪？

接下来我们用假设分析法，进行如下的假设：

事务A开始前，系统里面只有一个活跃事务ID是99；
事务A、B、C的版本号分别是100、101、102，且当前系统里只有这四个事务；
三个事务开始前，(1,1）这一行数据的row trx_id是90。

这样，事务A的视图数组就是 [99,100], 事务B的视图数组是 [99,100,101], 事务C的视图数组是 [99,100,101,102]。

为了便于我们分析，接下来我们通过一个图去分析，如下图所示：

这里需要说明一下，start transaction with consistent snapshot;的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的 start transaction。

从图中可以看到，第一个有效更新是事务C，把数据从 (1,1)改成了 (1,2)。这时候，这个数据的最新版本的row trx_id是102，而90这个版本已经成为了历史版本。

第二个有效更新是事务B，把数据从 (1,2)改成了 (1,3)。这时候，这个数据的最新版本（即row trx_id）是101，而102又成为了历史版本。

你可能注意到了，在事务A查询的时候，其实事务B还没有提交，但是它生成的(1,3)这个版本已经变成当前版本了。但这个版本对事务A必须是不可见的，否则就变成脏读了。

好，现在事务A要来读数据了，它的视图数组是 [99,100]。当然了，读数据都是从当前版本读起的。所以，事务A查询语句的读数据流程是这样的：

找到 (1,3)的时候，判断出row trx_id=101，比高水位大，处于红色区域，不可见；
接着，找到上一个历史版本，一看row trx_id=102，比高水位大，处于红色区域，不可见；
再往前找，终于找到了 （1,1)，它的row trx_id=90，比低水位小，处于绿色区域，可见。

这样执行下来，虽然期间这一行数据被修改过，但是事务A不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。

这个判断规则是我通过一些资料和高性能MYSQL中从代码逻辑直接转译过来的，但是正如你所见，用于人肉分析可见性很麻烦。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

现在，我们用这个规则来判断图4中的查询结果，事务A的查询语句的视图数组是在事务A启动的时候生成的，这时候：

(1,3)还没提交，属于情况1，不可见；
(1,2)虽然提交了，但是是在视图数组创建之后提交的，属于情况2，不可见；
(1,1)是在视图数组创建之前提交的，可见。

你看，去掉数字对比后，只用时间先后顺序来判断，分析起来是不是轻松多了。所以，后面我们就都用这个规则来分析。

这时候你是不是有一个这样的疑问：事务B的 update语句，如果按照一致性读，好像结果不对哦？

事务B的视图数组是先创建的，之后事务C才提交，不是应该看不见 (1,2)吗，怎么能算出 (1,3)来？

确实如此，如果事务B在更新之前查询一次数据，这个查询返回的k的值确实是1。

但是，当它要去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。

因此，事务B此时的 set k=k+1是在 （1,2）的基础上进行的操作，这里就用到了这样一条规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为**当前读。

因此，在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的row trx_id是101。所以，在执行事务B查询语句的时候，一看自己的版本号是101，最新数据的版本号也是101，是自己的更新，可以直接使用，所以查询得到的k的值是3。

这里我们提到了一个概念，叫作当前读。其实，除了update语句外，select语句如果加锁，也是当前读。

因此，如果把事务A的查询语句 select * from t where id=1修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是101的数据，返回的k的值是3。下面这两个select语句，就是分别加了读锁（S锁，共享锁）和写锁（X锁，排他锁）。

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

假设事务C不是马上提交的，而是变成了下面的事务C’，会怎么样呢？如下图所示：

事务C’的不同是，更新后并没有马上提交，在它提交前，事务B的更新语句先发起了。前面说过了，虽然事务C’还没提交，但是 (1,2)这个版本也已经生成了，并且是当前的最新版本。那么，事务B的更新语句会怎么处理呢？

这时候，我们的两阶段锁协议就要上场了。事务C’没提交，也就是说(1,2)这个版本上的写锁还没释放。而事务B是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务C’释放这个锁，才能继续它的当前读。

那么回到之前的隔离界别中的事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read） ；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

接下来再看一下，在读提交隔离级别下，事务A和事务B的查询语句查到的k，分别应该是多少呢？如下图所示：

可以看到此时事务A的查询语句的视图数组是在执行这个语句的时候创建的，时间线上 (1,2)、(1,3)的生成时间都在创建这个视图数组的时刻之前。

但是，在这个时刻：(1,3)还没提交，属于情况1，不可见；(1,2)提交了，属于情况3，可见。所以，这时候事务A查询语句返回的是k=2。显然地，事务B查询结果k=3。

锁

实现多个事务之间的隔离。一种是锁，另一种是mvcc机制。

事务的隔离级别分为四种：

未提交读：不需要隔离机制
提交读：MVCC
可重复读：MVCC
串行化：锁

封锁粒度

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

封锁类型

INSERT;
UPDATE;
DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

--读锁
SELECT * FROM table WHERE ? lock in share mode;
--写锁
SELECT * FROM table WHERE ? for update;

1. 读写锁

互斥锁（Exclusive），简写为 X 锁，又称写锁。
共享锁（Shared），简写为 S 锁，又称读锁。

有以下两个规定：

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。

锁的兼容关系如下：

排它锁不受隔离级别控制，共享锁受隔离级别控制

结论如下:
在任何隔离级别下，事务在执行写操作时都申请排它锁（写锁）（exclusive lock），持有排它锁直到事务结束，排它锁不受隔离级别的控制；

而共享锁（读锁）（Shared Lock）受到隔离级别的控制，隔离级别影响Shared Lock的申请和释放：

理解：其实也很合理，事务有不同的隔离级别，不同的隔离级别能读取到的数据是不一样的，所以读锁也是不一样的。

在 Read Uncommitted隔离级别下，读操作不会申请Shared Lock；
在 Read Committed（不使用row-versioning），Repeatable Read 和 Serializable隔离级别下，都会申请Shared Lock；
在 Read Committed（不使用row-versioning）隔离级别下，在读操作执行时，申请和持有Share Lock；一旦读操作完成，释放Shared Lock;
在 Repeatable Read 和 Serializable隔离级别下，事务会持有Shared Lock，直到事务结束（提交或回滚）；
在Serializable隔离级别下，事务会持有范围Shared Lock（Range Lock），锁定一个范围，在事务活跃期间，其他事务不允许在该范围中进行更新（Insert 或 delete）操作；

https://blog.csdn.net/weixin_46879188/article/details/113882685

2. 意向锁

使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS，IX/IS 都是表锁，用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定：

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下：

解释如下：

任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T₁ 想要对数据行 R₁ 加 X 锁，事务 T₂ 想要对同一个表的数据行 R₂ 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

为什么需要意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

InnoDB引擎是一种支持多粒度锁的引擎，而意向锁则是 InnoDB中为了支持多粒度的锁，为了兼容行锁、表锁而设计的，怎么理解这句话呢？先来看一个例子：

假设一张表中有一千万条数据，现在事务 T1对 ID=8888888的这条数据加了一个行锁，此时来了一个事务 T2，想要获取这张表的表级别写锁，经过前面的一系列讲解，大家应该知道写锁必须为排他锁，也就是在同一时刻内，只允许当前事务操作，如果表中存在其他事务已经获取了锁，目前事务就无法满足“独占性”，因此不能获取锁。

那思考一下，由于 T1是对 ID=8888888的数据加了行锁，那 T2获取表锁时，是不是得先判断一下表中是否存在其他事务在操作？但因为 InnoDB中有行锁的概念，所以表中任何一行数据上都有可能存在事务加锁操作，为了能精准的知道答案，MySQL就得 将整张表的 1000W条数据全部遍历一次，然后逐条查看是否有锁存在 ，那这个效率自然会非常的低。

有人可能会说，慢就慢点怎么了，能接受！但实际上不仅仅存在这个问题，还有另外一个致命问题，比如现在 MySQL已经判断到了第 567W行数据，发现前面的数据上都没有锁存在，正在继续往下遍历。

要记住 MySQL是支持并发事务的，也就是 MySQL正在扫描后面的每行数据是否存在锁时，万一又来了一个事务在扫描过的数据行上加了个锁怎么办？比如在第 123W条数据上加了一个行锁。那难道又重新扫描一遍嘛？这就陷入了死循环，行锁和表锁之间出现了兼容问题。

由于行锁和表锁之间存在兼容性问题，提出了意向锁。意向锁实际上也是一种特殊的表锁，意向锁其实是一种“挂牌告知”的思想，好比日常生活中的出租车，一般都会有一个牌子，表示它目前是“空车”还是“载客”状态，而意向锁也是这个思想。

比如当事务 T1打算对 ID=8888888这条数据加一个行锁之前（行级别的读锁或写锁），就会先加一个表级别的意向锁。此时当事务 T2尝试获取一个表级锁时，就会先看一下表上是否有意向锁，如果有的话再判断一下与自身是否冲突，比如表上存在一个意向共享锁，目前 T2要获取的是表级别的读锁，那自然不冲突可以获取。但反之，如果 T2要获取一个表级的写锁时，就会出现冲突，T2事务则会陷入阻塞，直至 T1释放了锁（事务结束）为止。

2）分类

意向共享锁（IS）：由语句select ... lock in share mode添加，与表锁共享锁（read）兼容，与表锁排他锁（write）互斥。在准备给表数据添加一个S锁时，需要先获得该表的IS锁
意向排他锁（IX）：由insert、update、delete、select...for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。在准备给表数据添加一个X锁时，需要先获得该表的IX锁

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

注意版本，MySQL之前某些版本不支持 data_locks 表，即该表可能不存在。可以通过运行 SELECT VERSION(); 来检查你的 MySQL 版本。

3）演示

A.意向共享锁与表读锁是兼容的

B.意向排他锁与表读锁、写锁都是互斥的

https://cloud.tencent.com/developer/article/2431018

在SQL中，意向锁是一种特殊的表级锁，用于协调行级锁和表级锁之间的关系，以提高并发性能和减少死锁的可能性。意向锁主要有两种类型：意向共享锁（IS）和意向排他锁（IX）。

意向锁的加锁方式

意向锁的加锁方式是自动的，不需要用户手动操作。以下是意向锁的加锁机制：

意向共享锁（IS） ：

当事务需要对某一行加共享锁（S锁）时，它会先在表级加一个意向共享锁（IS锁）。
意向共享锁允许其他事务在表级加意向共享锁，但不能加意向排他锁（IX锁）。

意向排他锁（IX） ：

当事务需要对某一行加排他锁（X锁）时，它会先在表级加一个意向排他锁（IX锁）。
意向排他锁允许其他事务在表级加意向排他锁，但不能加表级共享锁（S锁）。

意向锁的使用示例

以下是意向锁在实际事务中的使用示例：

示例1：意向共享锁（IS）

START TRANSACTION;
-- 事务A：意向共享锁
SELECT * FROM accounts WHERE balance > 1000 LOCK IN SHARE MODE;
-- 事务A 已加意向共享锁，允许读取数据

示例2：意向排他锁（IX）

START TRANSACTION;
-- 事务B：意向排他锁
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1 FOR UPDATE;
-- 事务B 已加意向排他锁，允许更新数据

封锁协议

1. 三级封锁协议

一级封锁协议

事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

二级封锁协议

在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。

可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

三级封锁协议

在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

2. 两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。

lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度。

lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

MySQL 隐式与显式锁定

MySQL 的 InnoDB 存储引擎采用两段锁协议，会根据隔离级别在需要的时候自动加锁，并且所有的锁都是在同一时刻被释放，这被称为隐式锁定。

InnoDB 也可以使用特定的语句进行显示锁定：

SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

实例

在 SQL 中，FOR UPDATE 是一个用于锁定查询结果集的子句，通常在事务中使用。它的主要作用是防止其他事务在当前事务完成之前修改查询结果中的数据。以下是需要使用 FOR UPDATE 的一些典型场景：

1. 防止并发修改

当你希望确保查询到的数据在事务期间不会被其他事务修改时，可以使用 FOR UPDATE。
例如，在银行转账场景中，如果需要从一个账户扣款并给另一个账户加款，必须确保在事务完成之前，相关账户的余额不会被其他事务修改。

START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 123 FOR UPDATE;
-- 执行扣款操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
-- 执行加款操作
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;
COMMIT;

在这个例子中，FOR UPDATE 确保在事务完成之前，account_id = 123 的记录不会被其他事务修改。

2. 库存管理

在电商系统中，库存数量是一个关键数据。如果多个用户同时购买同一商品，可能会导致库存超卖。
使用 FOR UPDATE 可以锁定库存记录，确保在事务完成之前，库存不会被其他事务修改。

START TRANSACTION;
SELECT stock_quantity FROM inventory WHERE product_id = 101 FOR UPDATE;
-- 检查库存是否足够
IF stock_quantity >= 1 THEN
    UPDATE inventory SET stock_quantity = stock_quantity - 1 WHERE product_id = 101;
END IF;
COMMIT;

3. 订单处理

在处理订单时，可能需要确保订单状态不会在事务期间被其他事务修改。
例如，更新订单状态为“已发货”时，可以使用 FOR UPDATE 锁定订单记录。

START TRANSACTION;
SELECT status FROM orders WHERE order_id = 789 FOR UPDATE;
-- 更新订单状态
UPDATE orders SET status = 'SHIPPED' WHERE order_id = 789;
COMMIT;

4. 防止死锁

在复杂的事务中，多个事务可能同时访问相同的记录。如果没有适当的锁定机制，可能会导致死锁。
使用 FOR UPDATE 可以显式地控制锁定顺序，从而减少死锁的可能性。

在库存管理系统中，如果使用共享锁（ LOCK IN SHARE MODE ）来处理库存超卖问题，确实可能会导致死锁。以下是原因和分析：
为什么共享锁会导致死锁？

共享锁的特性：共享锁允许多个事务同时读取数据，但不允许修改。当事务需要对数据进行修改时，必须将共享锁升级为排他锁。
死锁的形成：如果多个事务同时对同一行数据加共享锁，然后尝试修改数据，每个事务都需要将共享锁升级为排他锁。在升级过程中，事务会互相等待对方释放锁，从而形成死锁。

库存管理场景中的问题

假设两个事务（事务 A 和事务 B）同时对库存数据加共享锁。
事务 A 和事务 B 都尝试减少库存，但需要将共享锁升级为排他锁。
事务 A 等待事务 B 释放共享锁，而事务 B 也在等待事务 A 释放共享锁，最终导致死锁。

为什么不能用共享锁解决库存超卖？

共享锁无法确保事务之间的修改顺序，无法防止并发修改。
当事务尝试升级共享锁为排他锁时，容易引发死锁。

解决方案
在库存管理系统中，应使用排他锁（如 FOR UPDATE ），以确保同一行数据在同一时间只能被一个事务修改。这样可以避免库存超卖问题，同时减少死锁的风险。

总结

共享锁适合只读场景，但在需要修改数据的场景中容易导致死锁。
排他锁（如 FOR UPDATE ）是解决库存超卖问题的正确选择，因为它可以确保事务的独占性，避免并发修改和死锁。

5. 数据一致性

当事务需要基于查询结果进行后续操作时，FOR UPDATE 可以确保查询结果在事务期间不会发生变化。
例如，在计算用户积分时，需要确保在事务完成之前，用户的积分不会被其他事务修改。

START TRANSACTION;
SELECT points FROM users WHERE user_id = 1001 FOR UPDATE;
-- 更新用户积分
UPDATE users SET points = points + 100 WHERE user_id = 1001;
COMMIT;

注意事项

锁定范围 ：

FOR UPDATE 通常锁定查询结果中的所有行。如果查询返回大量数据，可能会导致性能问题。
如果只需要锁定单行，可以结合 WHERE 子句限制锁定范围。

事务隔离级别 ：

FOR UPDATE 的效果可能受到事务隔离级别（如 READ COMMITTED 或 REPEATABLE READ）的影响。
在某些隔离级别下，FOR UPDATE 可能会锁定更多的行。

性能影响 ：

锁定会降低并发性能，因此应尽量减少锁定的行数，并尽快提交事务。

总结

FOR UPDATE 是一种强大的工具，用于确保事务的正确性和数据一致性。它适用于需要防止并发修改、确保数据一致性或避免死锁的场景。然而，使用时需要谨慎，以避免对性能造成负面影响。

面试题

【MySQL】事务的隔离级别是如何实现的

水稻: 菜瓜，听说最近你在复习MySQL方面的知识，想请教一下MySQL的事务？

菜瓜：嗯，最近刚刚看到。事务指的是MySQL中不可拆分的业务单元，具有ACID的属性。

水稻: ACID我知道啊，但是不太懂他的实现，你能说和我聊聊事务在数据库底层是怎么实现的吗？

菜瓜：据我了解，不同的特性底层的实现不一样，主要依赖两种日志和锁来实现

先说持久性：我们知道数据的操作会先在内存中完成，那么事务提交后如何保证一定能持久化到磁盘呢
- redo log: 事务在提交前对数据的修改会先写到redo log 中，如果返回事务已提交成功，那么表示redo log已经记录完成。redo log 也有缓冲区，redo log的内存缓冲区大小和磁盘扇区的大小512字节一致，不会出现掉电易失的情况。另外redo log记录的是物理变化，体积很小，且redo log 写磁盘是顺序IO，极快~丝滑
- redo log 和binlog区别：一个是用于做持久化，另一个用作数据恢复和复制
原子性，指的是被事务包裹的一组操作要么全部成功，要么全部失败。不会存在执行了一部分，另一部分不执行的情况
- undo log: MySQL使用undo log实现操作回滚。事务开启后执行的命令都会有一条对应反向的逻辑日志计入undo日志文件中（譬如insert 就会有一条delete）。undo log的持久化会被记录在redo log中（利用redo log 速度快的特性）。一旦发生错误或者回滚的时候，利用undo就可以操作回去

水稻: 那还有一致性和隔离性呢？

菜瓜：一致性和隔离性可以放在一起说，隔离级别的选择就是一致性和隔离性的权衡

实现多个事务之间的隔离。一种是锁，另一种是mvcc机制。

水稻：锁我知道，mvcc是什么？

菜瓜：我们把数据库的读操作分为两类，一是当前读，使用锁机制；一是快照读，使用mvcc（Multi-Version Concurrency Control, 多版本并发控制）

当前读
- 数据的修改操作（insert update delete）和查询时显示加锁 select(查询条件后加上 lock in share mode & for update)
- 会锁住要读取的数据以保障数据的一致
快照读使用的是mvcc机制，就是多版本并发控制。
- 除当前读之外，普通的select查询为快照读，顾名思义，就是读取的是一个快照版本，以隔离多个事务之间的数据

水稻：能不能仔细说说这个mvcc

菜瓜：可以，它的实现还是依赖undo log来做的

在RR(Repeated Read, 可重复读) RC(Read Committed, 读提交)两种级别下使用。其他两种不需要实现隔离
你肯定听说过mysql在RR级别下解决了幻读问题，就是依赖这个来做的
- 简单来说就是，MySQL维护了一个记录活跃事务id的列表readview
- undo log是怎么记录的呢。举个栗子🌰
  - innodb的表中存在三个额外的隐藏字段，分别是编辑该条记录的事务id，更改前的undo log的回滚指针，还有一个对我们这个分析不太重要
  - 如果有事务对该记录做了变更，事务id会更新，同时undo log里面会产生新记录，回滚指针字段指向最新的undo log链
  - 通过比较当前事务id和readview中其他事务的id大小来决定自己读取的数据是哪个版本的undo log记录
    - 如果当前事务id比readview中的都小，就说明该条记录没有被其他事务更改。直接读取
    - 如果当前事务id比readview中的都大，沿着undo log链能找到最小事务id指向的undo log，该数据为稳定数据
- RR级别下利用该机制避免了幻读
- RC级别下每次都会读取数据的最新记录

总结：

事务的持久性和原子性由Redo log和Undo log实现
隔离性和一致性的权衡由锁机制和MVCC(Multi-Version Concurrency Control, 多版本并发控制，即快照)实现

部分内容为自己猜想，如有错误，欢迎指正！

参考文章

MySql-Undo及Redo详解 https://blog.csdn.net/aaa821/article/details/80645242
MySql MVCC 多版本并发控制 https://www.cnblogs.com/paulwang92115/p/12189487.html

实例

隔离级别与锁的实验

理解：这里需要注意的是，

事务的隔离级别主要是针对读取数据，它保证的是两个不同的事务之间查询数据、读取数据的并发一致性问题。

而封锁机制主要是针对写入数据，它保证的是两个不同的操作之间，写入数据的一致性问题。
如果在一个事物中加了锁，事务阻塞不提交，那么并不会影响另一个事务的读取，只会影响另一个事务的写入，另一个事务会因为锁阻塞在那里。

首先将事务提交模式改为手动提交

事务一：

select * from user;

UPDATE user SET name = 'nemo' 
WHERE id = '1';

select * from user where id = '1';

事务二：事务被阻塞了，只能读，不能写

select * from user;

UPDATE user SET name = 'nemo2' 
WHERE id = '1';

事务导致锁表

我们在debug事务的时候需要注意一下是否锁住了整张表，让其他人也没办法使用这张表了。

Mysql 事务中Update 会锁表吗？

两种情况：
1.带索引
2.不带索引

前提介绍：

方式：采用命令行的方式来模拟

1.mysq由于默认是开启自动提交事务，所以首先得查看自己当前的数据库是否开启了自动提交事务。

命令：select @@autocommit;

结果如下：

如果是1，那么运行命令：set autocommit = 0;设置为不开启自动提交

2.当前的数据库表格式如下

显然除了主键，我没有加任何索引

实际例子：

1.没有索引

运行命令：begin;开启事务，然后运行命令修改，先别commit事务

update tb_user set phone=11 where name="c1";

再开一个窗口，直接运行命令：

update tb_user set phone=22 where name=“c2”;

会发现命令卡住了，但是当前面一个事务通过commit提交了，命令就会正常运行结束，说明是被锁表了。

2.给name字段加索引

create index index_name on tb_user(name);

然后继续如1里面的操作，也就是一个开启事务，运行，先不提交

update tb_user set phone=11 where name="c1";

然后另一个运行

update tb_user set phone=22 where name="c2";

发现命令不会卡住，说明没有锁表

但是如果另一个也是

update tb_user set phone=22 where name="c1";

更新同一行，说明是锁行了

3.总结

如果没有索引，所以update会锁表，如果加了索引，就会锁行

https://www.cnblogs.com/blknemo/p/16784867.html

posted @ 2020-10-16 21:16 Nemo& 阅读(482) 评论(0) 收藏举报

刷新页面返回顶部

𝓝𝓮𝓶𝓸

没有人在意你说什么，人们只听得见他们想听的....

Nemo&