SQL 事务

本文最后更新于：2024年3月18日 凌晨

SQL 事务

  • 事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。

ACID原则

  • 原子性（Atomic)：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节，事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性（Consist)：一个事务可以封装状态改变（除非它是一个只读的），事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少，也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。
  • 隔离性（Isolated)：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作，如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统，这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
  • 持久性（Durable)：在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

并发下的问题

  • 只有存在并发数据访问时才需要事务，当多个事务访问同一数据时，可能会存在5类问题，包括3类数据读取问题（脏读，不可重复读和幻读）和2类数据更新问题（第1类丢失更新和第2类丢失更新）

脏读（Dirty Read)

  • A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。
时间 转账事务A 取款事务B
T1 开始事务
T2 开始事务
T3 查询账户余额为1000元
T4 取出500元余额修改为500元
T5 查询账户余额为500元（脏读）
T6 撤销事务余额恢复为1000元
T7 汇入100元把余额修改为600元
T8 提交事务

不可重复读（Unrepeatable Read)

  • 事务A重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务B修改过了。
时间 转账事务A 取款事务B
T1 开始事务
T2 开始事务
T3 查询账户余额为1000元
T4 查询账户余额为1000元
T5 取出100元修改余额为900元
T6 提交事务
T7 查询账户余额为900元（不可重复读）

幻读（Phantom Read)

  • 事务A重新执行一个查询，返回一系列符合查询条件的行，发现其中插入了被事务B提交的行。
时间 统计金额事务A 转账事务B
T1 开始事务
T2 开始事务
T3 统计总存款为10000元
T4 新增一个存款账户存入100元
T5 提交事务
T6 再次统计总存款为10100元（幻读）

第1类丢失更新

  • 事务A撤销时，把已经提交的事务B的更新数据覆盖了。
时间 取款事务A 转账事务B
T1 开始事务
T2 开始事务
T3 查询账户余额为1000元
T4 查询账户余额为1000元
T5 汇入100元修改余额为1100元
T6 提交事务
T7 取出100元将余额修改为900元
T8 撤销事务
T9 余额恢复为1000元（丢失更新）

第2类丢失更新

  • 事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。
时间 转账事务A 取款事务B
T1 开始事务
T2 开始事务
T3 查询账户余额为1000元
T4 查询账户余额为1000元
T5 取出100元将余额修改为900元
T6 提交事务
T7 汇入100元将余额修改为1100元
T8 提交事务
T9 查询账户余额为1100元（丢失更新）

事务隔离级别

  • 数据库为了维护事务的隔离性，数据库通常会通过锁机制来解决数据并发访问问题，直接使用锁是非常麻烦的，为此数据库为用户提供了自动锁机制，只要用户指定会话的事务隔离级别，数据库就会通过分析SQL语句然后为事务访问的资源加上合适的锁。
隔离级别 脏读 不可重复读 幻读 第一类丢失更新 第二类丢失更新
READ UNCOMMITED 允许 允许 允许 不允许 允许
READ COMMITTED 不允许 允许 允许 不允许 允许
REPEATABLE READ 不允许 不允许 允许 不允许 不允许
SERIALIZABLE 不允许 不允许 不允许 不允许 不允许
  • 需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差，所以要根据具体的应用来确定合适的事务隔离级别。

查看事务隔离级别

  • 在 MySQL 中，可以通过show variables like '%tx_isolation%'或select @@tx_isolation;语句来查看当前事务隔离级别。
  • 查看当前事务隔离级别的 SQL 语句和运行结果如下。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.17 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
  • 结果显示，目前 MySQL 的事务隔离级别是 REPEATABLE-READ
  • 另外，还可以使用下列语句分别查询全局和会话的事务隔离级别：
1
2
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
  • 注意：在MySQL 8.0.3 中，tx_isolation 变量被transaction_isolation变量替换了，在 MySQL 8.0.3 版本中查询事务隔离级别，只要把上述查询语句中的 tx_isolation变量替换成transaction_isolation变量即可。

修改事务隔离级别

  • MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别，语法格式如下：
1
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

  • SESSION：表示修改的事务隔离级别将应用于当前 session 内的所有事务。

  • GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响。

  • 如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

  • 使用 SET TRANSACTION 语句分别修改 session 和全局的事务隔离级别SQL 语句和运行结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql>  select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE           |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

mysql>  select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
  • 还可以使用 set tx_isolation 命令直接修改当前 session 的事务隔离级别，SQL 语句和运行结果如下：
1
2
3
4
5
6
7
8
9
10
mysql> set tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set, 1 warning (0.00 sec)

编程式事务

改变自动提交模式

1
2
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

注意:

  • MySQL中默认是自动提交。
  • 使用事务时应先关闭自动提交。

开始个事务

  • 标记事务的起始点。
1
START TRANSACTION

提交事务

1
COMMIT

事务回滚

  • 数据回到本次事务的初始状态。
1
ROLLBACK

保存点

1
2
3
SAVEPOINT 保存点名称 -- 设置一个事务保存点。
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点。
RELEASE SAVEPOINT 保存点名称 -- 删除保存点。

实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
    $dbhost = 'localhost';  // MySQL服务器主机地址。
$dbuser = 'root';            // MySQL用户名。
$dbpass = '123456';          // MySQL用户名密码。
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败： ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码。
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'Test' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行。
mysqli_begin_transaction($conn);            // 开始事务定义。

if(!mysqli_query($conn, "insert into transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚。
}

if(!mysqli_query($conn, "insert into transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // 判断执行失败时回滚。
}
mysqli_commit($conn);            // 执行事务。
mysqli_close($conn);
?>
Software BackEnd Database 关系数据库 DML

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！