MySQL TCL 事务控制

2023-10-12 16:08:29 浏览数 (2)

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

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

1.隔离级别查看与设置

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

代码语言：javascript复制
# 查看全局
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。

MySQL InnoDB 缺省隔离级别是可重复读（Repeatable Read）。

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

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

代码语言：javascript复制
SET [GLOBAL | SESSION] TRANSACTION
    transaction_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 权限来做这个。

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

代码语言：javascript复制
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

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

代码语言：javascript复制
SET transaction_isolation='READ-COMMITTED';

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

代码语言：javascript复制
SET @@global.transaction_isolation='READ-COMMITTED';

2.自动提交事务

2.1 查看是否自动提交事务

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

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

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

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

代码语言：javascript复制
SELECT @@autocommit;
SELECT @@session.autocommit;
SHOW SESSION VARIABLES LIKE 'autocommit';

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

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

代码语言：javascript复制
SELECT @@global.autocommit;
SHOW GLOBAL VARIABLES LIKE 'autocommit';

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

  1. 关闭自动提交事务。

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

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

第一种：临时关闭。

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

代码语言：javascript复制
SET autocommit = 0;
SET @@autocommit = 0;
SET @@session.autocommit = 0;
SET SESSION autocommit = 0;

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

第二种：永久关闭。

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

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

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

代码语言：javascript复制
[mysqld]
init_connect='SET autocommit=0'

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

  1. 开启自动提交事务。

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

代码语言：javascript复制
SET autocommit = 1;
SET @@autocommit = 1;
SET @@session.autocommit = 1;
SET SESSION autocommit = 1;

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

3.事务执行基本流程

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

代码语言：javascript复制
CREATE TABLE transaction_test(id int primary key)engine=InnoDB;
  1. 开启一个事务。
代码语言：javascript复制
BEGIN;

# 或
START TRANSACTION;
  1. 执行一系列增删改语句。
代码语言：javascript复制
INSERT INTO transaction_test VALUES(1);
  1. 手动提交或回滚。

事务回滚：

代码语言：javascript复制
ROLLBACK;

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

代码语言：javascript复制
SELECT * FROM transaction_test;
Empty set (0.00 sec)

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

手动提交事务：

代码语言：javascript复制
COMMIT;

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

4.设置事务的保存点

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

  1. 设置折返点
代码语言：javascript复制
SAVEPOINT identifier;
  1. 回滚至折返点
代码语言：javascript复制
ROLLBACK [WORK] TO [SAVEPOINT] identifier;

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

  1. 提交或继续事务。

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

代码语言：javascript复制
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

mysql tcl 变量 事务 系统

0 人点赞