理解数据库事务和回滚：概念、实例与Python脚本实现

概念

• 事务（Transaction）: 数据库中的事务是一组不可分割的操作集合，它们要么全部成功，要么全部失败。这个概念保证了数据的完整性和一致性。
• 回滚（Rollback）: 当事务中的一个或多个操作失败时，回滚机制会撤销所有已执行的操作，将数据库恢复到事务开始前的状态。这是一种错误恢复机制。
• 提交（Commit）: 如果事务中的所有操作都成功完成，那么事务会被提交，其结果会永久地保存在数据库中。
• ACID属性:
  1)原子性（Atomicity）: 确保事务中的所有操作都作为一个整体来执行。
  2)一致性（Consistency）: 事务的执行保证了数据库从一个一致的状态转移到另一个一致的状态。
  3)隔离性（Isolation）: 确保并发执行的事务彼此独立，不会相互干扰。
  4)持久性（Durability）: 一旦事务提交，其结果在数据库中持久保存。

银行案例实践

创建数据

本例中，我们创建一个名为 accounts 的表，包含 account_id（账户ID，作为主键）和 balance（账户余额）两个字段。脚本将插入两个账户A和B，各自具有特定的初始余额。

-- 创建银行账户表
CREATE TABLE test.accounts (
    account_id VARCHAR(10) PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);

-- 插入初始数据
INSERT INTO test.accounts (account_id, balance) VALUES ('A', 1000.00);  -- 假设账户A有1000元
INSERT INTO test.accounts (account_id, balance) VALUES ('B', 500.00);   -- 假设账户B有500元

Python脚本中的事务回滚

以下Python脚本演示了如何在数据库操作中使用事务。脚本开始于 connection.begin()，标志着事务的开始，并尝试执行两个账户间的转账操作。任何操作的失败都将触发异常，执行 connection.rollback()，撤销所有更改。若操作成功，则通过 connection.commit() 提交事务。

import pandas as pd
import pymysql
from credentials import get_credentials
import datetime


def connect_to_database():
    credentials = get_credentials()

    connection = pymysql.connect(
        host='localhost',
        user=credentials['username'],
        password=credentials['password'],
        database='test',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )

    try:
        with connection.cursor() as cursor:
            current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            print(f"{current_time} - 开始事务")
            connection.begin()

            sql_statements = [
                "UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A'",
                "UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B'",
                "select * from accounts;"
            ]

            for sql_statement in sql_statements:
                try:
                    # 执行 SQL 语句
                    cursor.execute(sql_statement)

                    # 获取查询结果
                    result = cursor.fetchall()
                    df = pd.DataFrame(result)

                    current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

                    print(f"{current_time} - 正在执行sql: {sql_statement}")
                    print(f"{current_time} - 查询结果如下：")
                    print(df)
                except pymysql.MySQLError as e:
                    # 发生错误时记录错误并准备回滚
                    current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    print(f"{current_time} - SQL 错误: {e}")
                    connection.rollback()
                    print("Transaction rolled back.")
                    return  # 结束函数，不再执行后续操作

            # 提交事务
            current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            print(f"{current_time} - 提交事务")
            connection.commit()

    except Exception as e:
        current_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        print(f"{current_time} - 外部错误: {e}")
        connection.rollback()
        print("Transaction rolled back.")

    finally:
        connection.close()


if __name__ == "__main__":
    connect_to_database()

此外，为了安全地管理数据库凭证，我们使用 get_credentials.py 模块，以存储和获取数据库用户名和密码。

def get_credentials():
    # 替换成你实际的用户名和密码
    credentials = {
        'username': 'root',
        'password': '密码'
    }
    return credentials

结语：保障数据安全与完整性的关键

通过本文，我们深入了解了数据库事务处理的核心概念和重要性。事务（Transaction）、回滚（Rollback）和提交（Commit）这些操作不仅是数据库管理的基础，也是确保数据安全、完整和一致性的关键。

重要性归纳：

1. 数据完整性：事务确保了即使在出现错误或系统故障的情况下，数据库的完整性也不会受损。
2. 错误恢复：回滚机制提供了一种有效的错误恢复路径，保护数据免受部分操作失败的影响。
3. 并发控制：事务的ACID属性在并发环境中维持了数据的一致性和稳定性。