当前位置：首页 > news >正文

MySQL TCL 事务控制

news 文章来源：https://blog.csdn.net/K346K346/article/details/132362488 2024/11/24 1:08:47

文章目录

1.事务四大特性
2.事务并发问题
3.事务隔离级别
4.隔离级别查看与设置
5.动提交事务
- 5.1 查看自动提交事务
- 5.2 关闭或开启自动提交事务
6.事务执行的基本流程
7.设置事务的保存点
参考文献

说到事务控制，先说一下数据库的事务是什么以及 MySQL 中我们必知的知识点。

1.事务四大特性

数据库事务(Database Transaction) ，是指对数据库的一系列操作组成的逻辑工作单元（Unit）。

并非任意的对数据库的操作序列都是数据库事务。数据库事务拥有以下四个特性，习惯上被称之为 ACID 特性。

（1）原子性（Atomicity）

事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

（2）一致性（Consistency）

事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束，如主键约束、唯一约束和外键约束等。

（3）隔离性（Isolation）

多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

（4）持久性（Durability）

已被提交的事务对数据库的修改应该永久保存在数据库中。

MySQL 中并非所有的数据库存储引擎都支持事务操作，比如 MyISAM 就不支持。所以，使用事务处理的时候一定要确定所操作的表示是否支持事务处理，可以通过查看建表语句来查看有没有指定事务类型的存储引擎。当然，事务处理是为了保障表数据原子性、一致性、隔离性、持久性。这些都是要消耗系统资源，要谨慎选择。

本文以数据库引擎 InnoDB 为例来演示命令行模式下事务的基本操作。

2.事务并发问题

在数据库操作中，为了有效保证并发读取数据的正确性，提出了事务隔离级别。

数据库是要被广大客户共享访问的，那么在数据库并发操作过程中很可能会出现一些不确定的情况。

（1）更新丢失（Update Lost）

更新结果别其他事务覆盖。

两个事务同时读取相同数据并分别修改后，一个事务的修改覆盖了另一个事务的修改。这是因为系统没有执行任何锁操作，因此并发事务没有被隔离开来。

第一类更新丢失（回滚丢失）。

比如 A 事务对某一列 +1，B 事务对某一列 +2。B 事务事务提交后，A 事务进行了回滚，导致 B 事务的更新丢失。

第二类更新丢失（逻辑丢失）。

比如 A 事务对某一列 +1，B 事务对某一列 +2，A B 事务执行完成后正常预期结果应该是某一列的值被 +3，但是 B 事务的结果覆盖了 A 事务，导致结果只被 +2，A 事务的更新丢失了。

（2）脏读（Dirty Read）

读取未提交数据。

A 事务读取 B 事务尚未提交的数据，此时如果 B 事务发生错误并执行回滚操作，那么 A 事务读取到的数据就是脏数据。

（3）不可重复读（Non-repeatable Read）

前后多次读取，数据内容不一致。

A 事务在 B 事务开始前读和 B 事务结束后读的数据不一样，因为数据被事务 B 给修改了。

（4）幻读（Phantom Read）

一个行出现在查询结果集中，但不在较早查询的结果集中。

事务 A 在读取某个范围内的记录时，事务 B 在该范围内插入了新记录，事务 A 再次读取该范围内的记录时，会产生幻行。

幻读比不可重复读取更难防范，因为锁定第一个查询结果集中的所有行并不能阻止导致幻像出现的更改。

为了解决上面的问题，于是有了事务隔离。

3.事务隔离级别

MySQL 提供了多个事务隔离级别，每个隔离级别都有不同的特点和能力，以解决并发访问数据库时可能出现的不同问题。

以下是 MySQL InnoDB 支持的四个 SQL:1992 标准定义的四个隔离级别及其解决的问题。

读未提交（Read Uncommitted）

不允许第一类更新丢失，允许脏读、不可重复读、幻读和第二类更新丢失。

最低的隔离级别，事务可以读取其他事务尚未提交的数据，虽然拥有超高的并发处理能力及很低的系统开销，但很少用于实际应用，因为可能导致数据不一致性。

读已提交（Read Committed）：

不允许第一类更新丢失和脏读。允许不可重复读、第二类更新丢失和幻读。

事务只能读取已经提交的数据，避免了脏读问题，但可能导致不可重复读和幻读。

这是大多数数据库系统的默认隔离级别，但不是 MySQL 默认。

可重复读（Repeatable Read）：

不允许第一类更新丢失、脏读、不可重复读和第二类更新丢失，允许幻读。

事务在整个事务期间保持一致的快照，其他事务的修改不会影响正在运行的事务，从而防止不可重复读问题。

这是 MySQL 默认的事务隔离级别。

串行化（Serializable）：

解决所有事务并发问题。

最高的隔离级别，通过强制事务排序，使之不可能相互冲突，从而解决防止所有并发问题。

在这个级别，可以解决上面提到的所有并发问题，但可能导致大量的超时现象和锁竞争。最直观的体现就是，当数据库隔离级别设置为串行化后，A事务在未提交之前，B事务对A事务数据的操作都会被阻塞。通常数据库不会用这个隔离级别，我们需要其他的机制来解决这些问题：比如乐观锁和悲观锁机制。

下面表格总结了事务并发问题和四大隔离级别的关系。

隔离级别	第一类更新丢失	脏读	不可重复读	第二类更新丢失	幻读
读未提交	x	✓	✓	✓	✓
读未提交	x	x	✓	✓	✓
读未提交	x	x	x	x	✓
读未提交	x	x	x	x	x

每个隔离级别都在一定程度上解决了并发访问可能导致的问题，但随着隔离级别提升，对并发性能的影响也越大，因为更高级别的隔离通常需要更多的锁和资源开销。因此，在选择隔离级别时，您需要根据应用的需求平衡一致性和性能，选择最适合您应用场景的隔离级别。

4.隔离级别查看与设置

（1）查看全局和当前会话的事务隔离级别。

# 查看全局
SELECT @@global.transaction_isolation; # 查看当前会话
SELECT @@transaction_isolation;
SELECT @@session.transaction_isolation; 
SHOW VARIABLES LIKE 'transaction_isolation';

从 MySQL 8.0 起，tx_isolation 变量被 transaction_isolation 变量替换了，所以请使用最新的变量 transaction_isolation。

（2）更改事务的隔离级别。

MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。

SET [GLOBAL | SESSION] TRANSACTIONtransaction_characteristic [, transaction_characteristic] ...transaction_characteristic: {ISOLATION LEVEL level| access_mode
}level: {REPEATABLE READ| READ COMMITTED| READ UNCOMMITTED| SERIALIZABLE
}access_mode: {READ WRITE| READ ONLY
}

不显示指明 SESSION 或 GLOBAL，默认是 SESSION，即设置当前会话的事务隔离级别。如果使用 GLOBAL 关键字，为之后的所有新连接设置事务隔离级别，需要 SUPER 权限来做这个。

比如更改当前会话事务隔离级别为读已提交。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;# 或省略 SESSION
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

也可以直接使用 SET 语句为变更系统变量 transaction_isolation 的值修改当前 session 的事务隔离级别。

SET transaction_isolation='READ-COMMITTED';

或者设置全局事务隔离级别为读已提交。

SET @@global.transaction_isolation='READ-COMMITTED';

5.动提交事务

5.1 查看自动提交事务

MySQL 默认事务操作模式是自动提交模式（autocommit ）。

系统变量 @@autocommit 用来控制一条SQL语句提交后是否自动执行，默认值是1，表示在mysql命令行模式下每条增删改语句在键入回车后，都会立即生效，而不需要手动commit。我们可以把它关闭，关闭之后需要commit，SQL语句才会真正生效。

由于系统变量 autocommit 分会话系统变量与全局系统变量，所以查询的时候，最好区别是会话系统变量还是全局系统变量。

查看当前会话是否处于自动提交模式。

SELECT @@autocommit;
SELECT @@session.autocommit;
SHOW SESSION VARIABLES LIKE 'autocommit';

如果返回结果为 1 或 ON，则表示当前会话处于自动提交模式；如果返回结果为 0 或 OFF，则表示当前会话未处于自动提交模式。

如果想查看全局配置，可查看系统变量 @@global.autocommit。

SELECT @@global.autocommit;
SHOW GLOBAL VARIABLES LIKE 'autocommit';

5.2 关闭或开启自动提交事务

关闭自动提交事务。

MySQL默认自动提交事务，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。

关闭自动提交事务主要有两种方法。一种是临时关闭，只对当前会话有效。第二种是永久关闭，对所有会话有效。

第一种：临时关闭。

关闭当前会话的自动提交事务。

SET autocommit = 0;
SET @@autocommit = 0;
SET @@session.autocommit = 0;
SET SESSION autocommit = 0;

这样之后，所有增删改语句，都必须使用 commit 之后，才能生效。

第二种：永久关闭。

在 MySQL 中，要永久地关闭自动提交事务，必须在配置文件中进行设置，以便在每次启动 MySQL 服务器时都保持这个设置。

找到 MySQL 的配置文件。在大多数情况下，MySQL 的配置文件名为 my.cnf 或 my.ini，具体位置取决于您的操作系统和安装方式。

打开配置文件并找到 [mysqld] 部分，添加或修改下面的配置项。

[mysqld]
init_connect='SET autocommit=0'

保存，然后重新启动 MySQL 服务器即可生效。

开启自动提交事务。

如果需要，可以开启自动提交模式。

SET autocommit = 1;
SET @@autocommit = 1;
SET @@session.autocommit = 1;
SET SESSION autocommit = 1;

要想永久有效，需要将上面配置文件中的配置项init_connect='SET autocommit=0'删除或设置为 1 即可。

6.事务执行的基本流程

首先创建一个测试数据表，建表语句如下：

CREATE TABLE transaction_test(id int primary key)engine=InnoDB;

开启一个事务。

BEGIN;
# 或
START TRANSACTION;

执行一系列增删改语句。

INSERT INTO transaction_test VALUES(1);

手动提交或回滚。

事务回滚：

ROLLBACK;

回滚后我们查看数据表中的数据。

SELECT * FROM transaction_test;
Empty set (0.00 sec)

表中没有数据，回滚成功。

手动提交事务：

COMMIT;

提交后，再 ROLLBACK 则不能回滚了，数据已经插入到数据表了。这里需要注意的是，在当前会话中，我们还没有手动 COMMIT 提交事务的时候，表中的数据已经被插入了，但对于其它会话，如果事务隔离级别是 READ COMMITED，那么在 COMMIT 之前，查询不到新插入的记录。

7.设置事务的保存点

在 MySQL 中，您可以使用事务保存点（Savepoint）来标记事务中的一个特定位置，以便在事务进行过程中进行部分回滚。事务保存点可以在事务内部创建，并且可以用于回滚到该保存点之前的状态，而不影响事务中的其他操作。

设置折返点

SAVEPOINT identifier;

回滚至折返点

ROLLBACK [WORK] TO [SAVEPOINT] identifier

这将撤销从保存点创建后到当前位置之间的所有操作。

提交或继续事务。

如果您满意回滚后的状态，可以继续进行其他操作，并最终提交事务。

COMMIT; -- 提交事务

通过使用事务保存点，您可以更细粒度地控制事务的回滚操作，以适应复杂的业务需求。请注意，保存点只在当前事务内部有效，并且一旦事务提交或回滚，保存点将被清除。

参考文献

13.3 Transactional and Locking Statements
MySQL 8.0 Reference Manual :: MySQL Glossary
15.7.2.1 Transaction Isolation Levels - MySQL
13.3.7 SET TRANSACTION Statement
MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables
脏读、不可重复读、幻读、两类丢失更新与四大隔离级别 - 51cto