MySQL - 事务

MySQL - 事务

什么是事务？

事务是一系列数据库操作，作为一个独立的逻辑工作单元执行。其核心原则是“全有或全无”：事务中的每个操作要么全部成功并使其更改永久化，要么如果任何一个操作失败，整个事务都会被撤销（回滚），使数据库恢复到其原始状态。这保证了数据完整性。

一个经典的例子是银行转账：从一个账户扣款并向另一个账户入账必须同时发生。如果在扣款成功后入账失败，事务会确保初始扣款被撤销。

ACID 特性

事务由四个标准特性定义，它们被称为 ACID 首字母缩略词：

• 原子性（Atomicity）：确保事务中的所有操作作为一个单一的、不可分割的单元完成。这是一个“全有或全无”的命题。
• 一致性（Consistency）：保证事务将数据库从一个有效状态带到另一个有效状态。所有数据库规则，例如约束和触发器，都必须得到满足。
• 隔离性（Isolation）：确保并发事务不会相互干扰。未完成事务的影响对其他事务不可见。
• 持久性（Durability）：保证一旦事务被提交，其更改是永久性的，并且在任何随后的系统故障（如断电或崩溃）后仍能保留。

事务控制语句

MySQL 提供多种语句来控制事务：

• START TRANSACTION：开始一个新的事务。BEGIN 或 BEGIN WORK 是其别名。
• COMMIT：使当前事务中进行的所有更改永久化。
• ROLLBACK：放弃当前事务中进行的所有更改。
• SAVEPOINT identifier：在事务中创建一个命名点，以便后续可以回滚到该点。
• ROLLBACK TO SAVEPOINT identifier：将事务回滚到指定命名保存点，而不结束整个事务。
• RELEASE SAVEPOINT identifier：删除一个命名保存点。
• SET AUTOCOMMIT = {0 | 1}：禁用或启用当前会话的默认自动提交模式。默认情况下，AUTOCOMMIT 为 1 (ON)，这意味着每个 SQL 语句都是一个独立的事务，并立即提交。要手动管理事务，您必须设置 SET AUTOCOMMIT = 0; 或以 START TRANSACTION 开始。

示例：COMMIT 和 ROLLBACK

使用上一章的 Customers 表，让我们演示一个事务。我们将开始一个事务，删除一条记录，然后改变主意并回滚它。

-- 1. 开始事务
START TRANSACTION;

-- 2. 删除年龄为 25 的客户
DELETE FROM Customers WHERE age = 25;

-- 此时，如果您从另一个连接检查，这些行将仍然存在。
-- 更改仅在此会话中可见。

-- 3. 我们决定撤销更改
ROLLBACK;

-- 现在，如果我们检查表，年龄为 25 的客户又回来了。
SELECT * FROM Customers WHERE age = 25;

如果我们使用 COMMIT 而不是 ROLLBACK，删除操作将变为永久性。

示例：SAVEPOINT

保存点允许进行部分回滚。

START TRANSACTION;

-- 操作 1
DELETE FROM Customers WHERE id = 1;
SAVEPOINT after_delete_1;

-- 操作 2
DELETE FROM Customers WHERE id = 2;
SAVEPOINT after_delete_2;

-- 操作 3
UPDATE Customers SET salary = salary * 1.1 WHERE id = 3;

-- 现在，让我们撤销对客户 2 的更新和删除操作
ROLLBACK TO SAVEPOINT after_delete_1;

-- 完成剩余的更改（仅删除客户 1）
COMMIT;

隔离级别

ACID 中的 ‘I’ 代表隔离性（Isolation），它控制一个事务对其他并发事务的更改可见程度。MySQL（使用 InnoDB）提供四种标准隔离级别，它们在一致性和性能之间进行权衡。

• READ UNCOMMITTED：最低级别。一个事务可以看到另一个事务未提交的更改（“脏读”或“uncommitted read”）。很少使用。
• READ COMMITTED：一个事务只能看到已提交的更改。防止脏读，但可能导致“不可重复读”（同一事务中的两次读取之间，行数据发生变化）。
• REPEATABLE READ：InnoDB 中的默认级别。一旦一个事务读取了一行数据，即使其他事务已提交了对该行的更改，如果该事务再次读取该行，它仍将看到相同的数据。然而，它仍然可能遇到“幻读”（其他事务插入的新行在后续读取中出现）。
• SERIALIZABLE：最高级别。它有效地对其操作的所有行施加读锁，阻止其他事务修改它们。这可以防止所有并发问题，包括幻读，但会以高昂的性能代价为代价。

您可以为下一个事务或整个会话设置隔离级别：

-- 仅为下一个事务设置
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 为整个会话设置
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

支持事务的存储引擎

并非所有 MySQL 存储引擎都支持事务。对于现代应用程序，InnoDB 是标准和默认的选择。它完全符合 ACID 特性。

像 MyISAM 这样的旧引擎不支持事务。对 MyISAM 表执行的任何 COMMIT 或 ROLLBACK 命令都不会有任何效果。创建新表时，请确保其引擎为 InnoDB 以使用事务。

CREATE TABLE my_transactional_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE = InnoDB;

常见陷阱：死锁和长事务

• 死锁：死锁发生在两个或多个事务相互等待对方释放锁时。例如，事务 A 锁定第 1 行并等待第 2 行，而事务 B 锁定第 2 行并等待第 1 行。InnoDB 会自动检测死锁，回滚其中一个事务（“受害者”），并返回一个错误。您的应用程序代码应该准备好捕获此错误并重试事务。
• 长时间运行的事务：长时间保持打开的事务可能会持有资源上的锁，从而阻塞其他事务并消耗系统资源。始终尽量使事务尽可能短。

在客户端程序中使用事务

正确管理事务是应用程序开发的关键部分。一般模式是：开始事务，在 try 块中运行查询，如果所有查询都成功则提交，如果任何查询失败则在 catch 块中回滚。

PHP
NodeJS
Java
Python

PHP
NodeJS
Java
Python



// 使用 PDO 进行事务管理
// （连接设置与存储过程示例相同）

try {
    // 1. 开始事务
    $pdo->beginTransaction();
    echo "Transaction started.\n";

    // 2. 执行操作
    $sql1 = "UPDATE Customers SET salary = salary - 500 WHERE id = 1";
    $pdo->exec($sql1);
    echo "Debited 500 from customer 1.\n";

    $sql2 = "UPDATE Customers SET salary = salary + 500 WHERE id = 2";
    $pdo->exec($sql2);
    echo "Credited 500 to customer 2.\n";

    // 3. 如果一切顺利，提交事务
    $pdo->commit();
    echo "Transaction committed successfully!\n";

} catch (\PDOException $e) {
    // 4. 如果发生任何错误，回滚事务
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
        echo "Transaction rolled back due to an error.\n";
    }
    echo "Error: " . $e->getMessage();
}




// 使用 mysql2 配合 promises 和 async/await
const mysql = require('mysql2/promise');

async function performTransaction() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        // 1. 开始事务
        await connection.beginTransaction();
        console.log('Transaction started.');

        // 2. 执行操作
        await connection.execute('UPDATE Customers SET salary = salary - 500 WHERE id = ?', [1]);
        console.log('Debited 500 from customer 1.');

        // 要模拟错误，请取消注释下一行：
        // throw new Error('Simulated application error!');

        await connection.execute('UPDATE Customers SET salary = salary + 500 WHERE id = ?', [2]);
        console.log('Credited 500 to customer 2.');

        // 3. 如果一切顺利，提交
        await connection.commit();
        console.log('Transaction committed successfully!');

    } catch (error) {
        console.error('An error occurred:', error.message);
        // 4. 如果发生错误，回滚
        if (connection) {
            await connection.rollback();
            console.log('Transaction rolled back.');
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

performTransaction();




// 使用现代 JDBC 配合 try-with-resources 和手动提交控制
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        // try-with-resources 确保连接被关闭
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // 1. 禁用自动提交以开始事务
            conn.setAutoCommit(false);
            System.out.println("Transaction started.");

            try (Statement stmt = conn.createStatement()) {
                // 2. 执行操作
                stmt.executeUpdate("UPDATE Customers SET salary = salary - 500 WHERE id = 1");
                System.out.println("Debited 500 from customer 1.");

                stmt.executeUpdate("UPDATE Customers SET salary = salary + 500 WHERE id = 2");
                System.out.println("Credited 500 to customer 2.");

                // 3. 如果一切顺利，提交
                conn.commit();
                System.out.println("Transaction committed successfully!");

            } catch (SQLException e) {
                // 4. 如果有任何错误，回滚
                System.out.println("An error occurred, rolling back transaction.");
                conn.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}




# 使用 mysql-connector-python 的内置事务方法
import mysql.connector
from mysql.connector import errorcode

def main():
    config = {
        'user': 'root',
        'password': 'password',
        'host': '127.0.0.1',
        'database': 'TUTORIALS'
    }
    connection = None
    try:
        connection = mysql.connector.connect(**config)
        cursor = connection.cursor()

        # 1. 开始事务
        # 在 mysql-connector-python 中，事务在第一个 DML 语句上自动开始。
        # 您可以通过 commit() 和 rollback() 来控制它。
        print("Transaction started implicitly.")

        # 2. 执行操作
        cursor.execute("UPDATE Customers SET salary = salary - 500 WHERE id = %s", (1,))
        print("Debited 500 from customer 1.")

        cursor.execute("UPDATE Customers SET salary = salary + 500 WHERE id = %s", (2,))
        print("Credited 500 to customer 2.")

        # 3. 如果一切顺利，提交
        connection.commit()
        print("Transaction committed successfully!")

    except mysql.connector.Error as err:
        print(f"An error occurred: {err}")
        # 4. 如果发生错误，回滚
        if connection:
            print("Rolling back transaction.")
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("Connection closed.")

if __name__ == "__main__":
    main()