mysql 专业笔记 -- 第 45 章：事务（Transaction）

第 45.1 节：开始事务

事务是一组顺序执行的 SQL 语句（如 SELECT、INSERT、UPDATE 或 DELETE），作为一个单独的工作单元执行。

换句话说，只有当组内的每个操作都成功时，事务才算完成。如果事务中的任何操作失败，整个事务将失败。

银行转账是解释此概念的最佳示例。假设要在两个账户之间转账。你需要编写如下 SQL 语句：

检查第一个账户中是否有请求的金额
从第一个账户扣除请求的金额
将金额存入第二个账户

如果这些过程中的任何一个失败，整个过程应回滚到之前的状态。

事务的 ACID 属性

事务具有以下四个标准属性：

原子性（Atomicity）：确保工作单元内的所有操作都成功完成；否则，事务在失败点中止，之前的操作回滚到之前的状态。
一致性（Consistency）：确保数据库在成功提交的事务后正确更改状态。
隔离性（Isolation）：使事务能够独立且透明地操作。
持久性（Durability）：确保已提交事务的结果在系统故障后仍然存在。

事务以 START TRANSACTION 或 BEGIN WORK 语句开始，以 COMMIT 或 ROLLBACK 语句结束。开始和结束语句之间的 SQL 命令构成事务的主体。

START TRANSACTION;
SET @transAmt = '500';
SELECT @availableAmt := ledgerAmt FROM accTable WHERE customerId = 1 FOR UPDATE;
UPDATE accTable SET ledgerAmt = ledgerAmt - @transAmt WHERE customerId = 1;
UPDATE accTable SET ledgerAmt = ledgerAmt + @transAmt WHERE customerId = 2;
COMMIT;

使用 START TRANSACTION 时，autocommit 保持禁用状态，直到你使用 COMMIT 或 ROLLBACK 结束事务。然后 autocommit 模式恢复为之前的状态。

FOR UPDATE 表示（并锁定）事务期间的行。

当事务保持未提交状态时，其他用户无法看到此事务的更改。

事务的一般流程

通过 BEGIN WORK 或 START TRANSACTION 开始事务。
运行所有 SQL 语句。
检查所有语句是否按需求执行。
如果是，则发出 COMMIT 命令；否则发出 ROLLBACK 命令将一切恢复到之前的状态。

注意：即使在使用（或可能最终使用）Galera/PXC 时，也应在 COMMIT 后检查错误。

第 45.2 节：COMMIT、ROLLBACK 和 AUTOCOMMIT

AUTOCOMMIT

MySQL 会自动提交不属于事务的语句。任何未以 BEGIN 或 START TRANSACTION 开头的 UPDATE、DELETE 或 INSERT 语句的结果将立即对所有连接可见。

AUTOCOMMIT 变量默认设置为 true。可以通过以下方式更改：

-- 设置 autocommit 为 false
SET AUTOCOMMIT = false;
-- 或
SET AUTOCOMMIT = 0;

-- 设置 autocommit 为 true
SET AUTOCOMMIT = true;
-- 或
SET AUTOCOMMIT = 1;

-- 查看 AUTOCOMMIT 状态
SELECT @@autocommit;

COMMIT

如果 AUTOCOMMIT 设置为 false 且事务未提交，则更改仅对当前连接可见。执行 COMMIT 语句后，更改将提交到表中，结果对所有连接可见。

考虑两个连接来说明这一点：

连接 1

-- 在设置 autocommit=false 之前向新表添加一行
mysql> INSERT INTO testTable VALUES (1);
-- 设置 autocommit = false
mysql> SET autocommit = 0;
mysql> INSERT INTO testTable VALUES (2), (3);
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
| 2   |
| 3   |
+-----+

连接 2

mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
+-----+
-- 仅显示在 autocommit=false 之前插入的行

连接 1（执行 COMMIT）

mysql> COMMIT;
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
| 2   |
| 3   |
+-----+

连接 2

mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
| 2   |
| 3   |
+-----+
-- 现在所有三行都可见

ROLLBACK

如果查询执行中出现任何问题，使用 ROLLBACK 来回滚更改。

-- 在设置 autocommit=false 之前向新表添加一行
mysql> INSERT INTO testTable VALUES (1);
-- 设置 autocommit = false
mysql> SET autocommit = 0;
mysql> INSERT INTO testTable VALUES (2), (3);
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
| 2   |
| 3   |
+-----+

-- 现在执行 ROLLBACK
mysql> ROLLBACK;
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
+-----+
-- ROLLBACK 移除了所有尚未提交的行

一旦执行了 COMMIT，ROLLBACK 将不起作用：

mysql> INSERT INTO testTable VALUES (2), (3);
mysql> SELECT * FROM testTable;
mysql> COMMIT;
+-----+
| tId |
+-----+
| 1   |
| 2   |
| 3   |
+-----+

-- 现在执行 ROLLBACK
mysql> ROLLBACK;
mysql> SELECT * FROM testTable;
+-----+
| tId |
+-----+
| 1   |
| 2   |
| 3   |
+-----+
-- ROLLBACK 没有移除任何行

如果 AUTOCOMMIT 设置为 true，则 COMMIT 和 ROLLBACK 无效。

第 45.3 节：使用 JDBC 驱动的事务

使用 JDBC 驱动控制事务何时提交和回滚。

通过 JDBC 驱动程序创建到 MySQL 服务器的连接。

JDBC 驱动程序可从官网下载。

从获取数据库连接开始：

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(DB_CONNECTION_URL, DB_USER, USER_PASSWORD);
-- DB_CONNECTION_URL 示例: "jdbc:mysql://localhost:3306/testDB"

字符集：指示客户端将使用什么字符集向服务器发送 SQL 语句，也指定服务器返回结果时应使用的字符集。创建连接时应明确指定。连接字符串示例：

jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8

打开连接时，AUTOCOMMIT 模式默认设置为 true，应将其改为 false 以开始事务。最好在打开连接后立即调用 setAutoCommit() 方法。

或者使用 START TRANSACTION 或 BEGIN WORK 开始新事务。使用这些命令时，无需更改 AUTOCOMMIT，它会自动禁用。

完整 JDBC 事务示例：

package jdbcTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class accTrans {

    public static void doTransfer(double transAmount, int customerIdFrom, int customerIdTo) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            String DB_CONNECTION_URL = "jdbc:mysql://localhost:3306/testDB?useUnicode=true&characterEncoding=utf8";
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(DB_CONNECTION_URL, DB_USER, USER_PASSWORD);

            // 关闭自动提交，开启事务
            con.setAutoCommit(false);

            // 检查可用余额
            String selectSql = "SELECT ledgerAmt FROM accTable WHERE customerId = ? FOR UPDATE";
            pstmt = con.prepareStatement(selectSql);
            pstmt.setInt(1, customerIdFrom);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                double availableAmt = rs.getDouble("ledgerAmt");
                if (availableAmt >= transAmount) {
                    // 扣款
                    String updateFromSql = "UPDATE accTable SET ledgerAmt = ledgerAmt - ? WHERE customerId = ?";
                    pstmt = con.prepareStatement(updateFromSql);
                    pstmt.setDouble(1, transAmount);
                    pstmt.setInt(2, customerIdFrom);
                    pstmt.executeUpdate();

                    // 存款
                    String updateToSql = "UPDATE accTable SET ledgerAmt = ledgerAmt + ? WHERE customerId = ?";
                    pstmt = con.prepareStatement(updateToSql);
                    pstmt.setDouble(1, transAmount);
                    pstmt.setInt(2, customerIdTo);
                    pstmt.executeUpdate();

                    con.commit();
                } else {
                    con.rollback();
                }
            } else {
                con.rollback();
            }
        } catch (SQLException ex) {
            try {
                if (con != null) con.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            ex.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        doTransfer(500, 1020, 1021);
    }
}

JDBC 事务确保事务块内的所有 SQL 语句都成功执行。如果事务块中的任何一个 SQL 语句失败，则中止并回滚事务块内的所有操作。

mysql 专业笔记 -- 第 45 章：事务（Transaction）

第 45.1 节：开始事务

事务的 ACID 属性

事务的一般流程

第 45.2 节：COMMIT、ROLLBACK 和 AUTOCOMMIT

AUTOCOMMIT

COMMIT

ROLLBACK

第 45.3 节：使用 JDBC 驱动的事务

阅读量高的博客

关于pycharm连接gitlab需要令牌的操作步骤

virtualbox安装fnOS

qemu使用的简易教程

站点正式发布的首篇文章