数据库中事务、指令、写法解读-EW帮帮网

数据库事务示例：银行转账操作

数据库事务是确保一组数据库操作要么全部成功执行，要么全部失败回滚的机制，这对于维护数据一致性至关重要。最经典的例子就是银行转账操作。

下面是一个使用Python和SQLite实现的银行转账事务示例，包含了异常处理和回滚机制：

import sqlite3
from sqlite3 import OperationalError

def create_database():
    """创建数据库和账户表"""
    conn = sqlite3.connect('bank.db')
    cursor = conn.cursor()
    
    # 创建账户表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS accounts (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        balance REAL NOT NULL CHECK(balance >= 0)
    )
    ''')
    
    # 插入测试数据（如果表为空）
    cursor.execute("SELECT COUNT(*) FROM accounts")
    if cursor.fetchone()[0] == 0:
        cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ("Alice", 1000.0))
        cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ("Bob", 500.0))
        conn.commit()
    
    conn.close()

def transfer_money(from_id, to_id, amount):
    """
    转账操作，包含完整的事务处理
    
    参数:
        from_id: 转出账户ID
        to_id: 转入账户ID
        amount: 转账金额
        
    返回:
        bool: 转账是否成功
    """
    conn = None
    try:
        # 连接数据库
        conn = sqlite3.connect('bank.db')
        cursor = conn.cursor()
        
        # 关闭自动提交，开始事务
        conn.isolation_level = 'EXCLUSIVE'
        
        # 1. 检查转出账户余额
        cursor.execute("SELECT balance FROM accounts WHERE id = ?", (from_id,))
        from_balance = cursor.fetchone()
        
        if not from_balance:
            raise ValueError(f"账户 {from_id} 不存在")
        
        if from_balance[0] < amount:
            raise ValueError(f"账户 {from_id} 余额不足")
        
        # 2. 减少转出账户余额
        cursor.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (amount, from_id)
        )
        
        # 这里可以模拟一个异常，测试回滚功能
        # raise OperationalError("模拟数据库错误")
        
        # 3. 增加转入账户余额
        cursor.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (amount, to_id)
        )
        
        # 检查转入账户是否存在
        if cursor.rowcount == 0:
            raise ValueError(f"账户 {to_id} 不存在")
        
        # 所有操作成功，提交事务
        conn.commit()
        print(f"转账成功: 从账户 {from_id} 向账户 {to_id} 转账 {amount} 元")
        return True
        
    except Exception as e:
        # 发生异常，回滚事务
        if conn:
            conn.rollback()
            print(f"发生错误: {str(e)}, 事务已回滚")
        return False
    finally:
        # 确保连接关闭
        if conn:
            conn.close()

def print_account_balances():
    """打印所有账户余额"""
    conn = sqlite3.connect('bank.db')
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, balance FROM accounts")
    accounts = cursor.fetchall()
    conn.close()
    
    print("\n账户余额:")
    for account in accounts:
        print(f"账户ID: {account[0]}, 姓名: {account[1]}, 余额: {account[2]} 元")
    print()

if __name__ == "__main__":
    # 初始化数据库
    create_database()
    
    # 显示初始余额
    print("初始状态:")
    print_account_balances()
    
    # 执行转账操作 (Alice的ID是1，Bob的ID是2)
    transfer_success = transfer_money(1, 2, 300)
    
    # 显示操作后的余额
    print("操作后状态:")
    print_account_balances()

代码解析

这个示例模拟了银行转账的完整流程，包含以下关键事务处理步骤：

事务开始：通过设置conn.isolation_level = 'EXCLUSIVE'关闭自动提交，开始一个事务
核心操作：
- 检查转出账户余额是否充足
- 减少转出账户的余额
- 增加转入账户的余额
异常处理：
- 如果任何步骤出现错误（如余额不足、账户不存在等），则执行conn.rollback()回滚事务
- 所有操作成功完成后，执行conn.commit()提交事务
数据一致性保证：
- 转账操作要么完全成功，要么完全失败
- 不会出现一方账户金额减少而另一方未增加的情况

你可以取消代码中raise OperationalError("模拟数据库错误")这一行的注释，测试异常发生时事务的回滚功能，此时两个账户的余额都不会发生变化。

这个例子很好地展示了事务的原子性 - 整个转账过程被视为一个不可分割的原子操作。

使用SQL语句操作事务时，核心是通过BEGIN（开始事务）、COMMIT（提交事务）和ROLLBACK（回滚事务）这三个命令控制事务的生命周期。以下以银行转账为例，用SQL语句（以MySQL语法为例，其他数据库如PostgreSQL、SQL Server语法类似）展示完整的事务操作流程。

1. 先创建数据库和表（准备环境）

首先需要创建账户表并插入测试数据，作为事务操作的基础：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

-- 创建账户表（包含ID、姓名、余额，余额不允许为负）
CREATE TABLE IF NOT EXISTS accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)  -- 确保余额非负
);

-- 插入测试数据（Alice有1000元，Bob有500元）
INSERT INTO accounts (name, balance) 
VALUES ('Alice', 1000.00), ('Bob', 500.00)
ON DUPLICATE KEY UPDATE name = name;  -- 避免重复插入

2. 用SQL语句执行转账事务（核心逻辑）

假设需要从Alice（id=1）向Bob（id=2）转账300元，完整的事务SQL如下：

-- 开始事务（不同数据库语法可能略有差异，MySQL用START TRANSACTION，SQL Server用BEGIN TRANSACTION）
START TRANSACTION;

-- 步骤1：检查转出账户（Alice）的余额是否充足
-- 先查询余额（实际操作中可在程序中判断，或用存储过程）
SELECT balance FROM accounts WHERE id = 1;  -- 假设结果为1000.00，足够转账

-- 步骤2：减少转出账户（Alice）的余额
UPDATE accounts 
SET balance = balance - 300.00 
WHERE id = 1;

-- 步骤3：增加转入账户（Bob）的余额
UPDATE accounts 
SET balance = balance + 300.00 
WHERE id = 2;

-- 步骤4：检查操作是否成功（例如：确认转入账户存在，更新行数为1）
-- 可通过查看影响行数判断（MySQL中用ROW_COUNT()）
SELECT ROW_COUNT();  -- 若返回1，说明上一步UPDATE成功

-- 若所有步骤无异常，提交事务（数据永久生效）
COMMIT;

3. 遇到异常时的回滚操作

如果在事务执行过程中出现错误（如余额不足、账户不存在等），需要用ROLLBACK撤销所有操作：

-- 模拟异常场景：假设Alice余额不足时强行转账
START TRANSACTION;

-- 步骤1：查询Alice余额（假设此时余额为200元，不足300元）
SELECT balance FROM accounts WHERE id = 1;  -- 结果为200.00

-- 步骤2：尝试减少Alice余额（此时会触发CHECK约束失败，或程序判断后主动回滚）
UPDATE accounts 
SET balance = balance - 300.00 
WHERE id = 1;  -- 执行后会报错（余额将为-100，违反CHECK约束）

-- 步骤3：由于步骤2失败，执行回滚（所有操作撤销）
ROLLBACK;

4. 用存储过程封装事务（更贴近实际应用）

实际开发中，事务逻辑通常封装在存储过程中，便于复用和添加逻辑判断。以下是MySQL存储过程示例：

-- 创建转账存储过程
DELIMITER //  -- 临时修改分隔符，避免与SQL中的分号冲突

CREATE PROCEDURE transfer_money(
    IN from_id INT,  -- 转出账户ID
    IN to_id INT,    -- 转入账户ID
    IN amount DECIMAL(10, 2)  -- 转账金额
)
BEGIN
    DECLARE from_balance DECIMAL(10, 2);  -- 存储转出账户余额
    DECLARE EXIT HANDLER FOR SQLEXCEPTION  -- 捕获所有SQL异常
    BEGIN
        ROLLBACK;  -- 异常时回滚
        SELECT '转账失败，已回滚' AS result;
    END;

    START TRANSACTION;  -- 开始事务

    -- 1. 检查转出账户是否存在
    SELECT balance INTO from_balance FROM accounts WHERE id = from_id;
    IF from_balance IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转出账户不存在';
    END IF;

    -- 2. 检查余额是否充足
    IF from_balance < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;

    -- 3. 检查转入账户是否存在
    IF NOT EXISTS (SELECT 1 FROM accounts WHERE id = to_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转入账户不存在';
    END IF;

    -- 4. 执行转账操作
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    COMMIT;  -- 所有操作成功，提交事务
    SELECT '转账成功' AS result;
END //

DELIMITER ;  -- 恢复分隔符

5. 调用存储过程测试事务

-- 测试正常转账（Alice向Bob转300元）
CALL transfer_money(1, 2, 300.00);
-- 执行后查询余额，Alice应为700，Bob应为800

-- 测试异常场景（余额不足）
CALL transfer_money(1, 2, 1000.00);  -- Alice此时余额700，不足1000
-- 会触发"余额不足"异常，事务回滚，余额不变

事务核心SQL命令总结

命令	作用
`START TRANSACTION`	开始一个事务（MySQL语法，其他数据库可能用`BEGIN`或`BEGIN TRANSACTION`）
`COMMIT`	提交事务，所有操作永久生效
`ROLLBACK`	回滚事务，撤销所有未提交的操作
`SAVEPOINT`	设置保存点，可回滚到指定节点（而非全部）

通过这些命令，可以确保一组SQL操作要么全部成功，要么全部失败，从而保证数据一致性（如转账时不会出现"一方扣钱、另一方未加钱"的情况）。

在事务未提交的情况下，数据库是否自动回滚，取决于事务的结束方式，主要分为以下两种场景：

1. 若事务因“连接断开”而结束（最常见）

如果只执行了第一个扣钱语句（UPDATE accounts ... id=1），事务未提交，且此时数据库连接被断开（例如：程序崩溃、网络中断、手动关闭连接），那么数据库会自动回滚该事务。

原因：数据库会为每个连接维护活跃事务，当连接异常终止时，数据库无法确定后续操作，为保证数据一致性，会默认撤销该连接中所有未提交的事务操作。
结果：Alice 的账户余额会恢复到扣钱之前的状态，仿佛从未执行过该 UPDATE 语句。

2. 若事务未结束（连接仍保持）

如果连接依然保持活跃（例如：程序暂停执行、未继续发送 COMMIT 或 ROLLBACK 命令），那么事务会一直处于“未提交”状态，数据库不会自动回滚。

此时的影响：
- 该事务的操作（Alice 扣钱）会被记录在数据库的事务日志中，但未写入实际的数据文件（即“未持久化”）。
- 对于其他并发连接：默认情况下（隔离级别为 READ COMMITTED），其他连接看不到 Alice 被扣钱的临时结果（因为事务未提交），只能看到事务开始前的余额。
- 对于当前连接：可以看到扣钱后的临时结果（因为当前事务内的操作对自身可见）。

总结

事务未提交时，只要连接保持活跃，数据库不会自动回滚，操作处于“临时生效”状态。
只有当连接断开（主动关闭或异常终止）时，数据库才会自动回滚未提交的事务，确保数据一致性。

因此，在实际开发中，若事务执行到一半需要终止，应显式执行 ROLLBACK 命令，而不是依赖连接断开的自动回滚（避免因连接未正常关闭导致的异常）。

如果执行了事务但忘记执行 COMMIT 或 ROLLBACK，会导致事务长期处于“未完成”状态，可能引发一系列问题，具体影响取决于数据库连接的状态和事务的隔离级别，主要包括以下几点：

1. 事务长期处于“活跃状态”，修改未持久化

未提交的事务会一直占用数据库的“事务资源”，事务中的所有修改（如 UPDATE、INSERT、DELETE）处于“临时生效”状态：

对当前连接：可以看到事务内的修改结果（例如，执行 SELECT 会返回扣钱后的余额），但这些修改并未写入数据库的永久存储（仅存在于事务日志和内存中）。
对其他连接：根据数据库的隔离级别（如 READ COMMITTED，MySQL 默认级别），其他连接无法看到未提交的修改（避免“脏读”），只能看到事务开始前的数据。

2. 可能导致锁阻塞，影响并发性能

事务执行过程中，数据库会自动对修改的数据加锁（如行锁、表锁），以保证事务隔离性。如果事务未提交/回滚，这些锁会一直持有：

若其他事务需要修改相同的数据（如 Alice 的账户），会被阻塞，等待锁释放，导致系统响应变慢。
极端情况下，可能引发“死锁”（例如，两个未提交的事务互相等待对方释放锁），进一步拖慢整个数据库。

3. 连接断开后，事务会被自动回滚

如果忘记提交/回滚的事务所在的数据库连接被关闭（主动关闭、程序崩溃、网络中断等），数据库会：

检测到连接终止，自动执行 ROLLBACK，撤销事务中所有未提交的修改。
释放该事务持有的所有锁，避免资源长期占用。

这是数据库的“保护机制”，确保异常情况下的数据一致性，但依赖连接断开的“被动触发”，无法解决连接未关闭时的问题。

4. 长期占用数据库资源，可能引发性能问题

未完成的事务会持续占用数据库的日志空间（事务日志需要记录所有修改，以便回滚或提交）和内存资源。如果大量事务长期未提交：

事务日志可能被撑满，导致数据库无法继续处理新事务。
内存占用过高，影响数据库的整体性能。

举例说明

假设执行了转账事务的前两步，但忘记 COMMIT 或 ROLLBACK：

BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;  -- Alice 扣钱
-- 忘记执行 COMMIT 或 ROLLBACK，程序一直运行，连接未关闭

当前连接：查询 Alice 的余额会显示“扣钱后的值”（如 700 元）。
其他连接：查询 Alice 的余额仍显示“扣钱前的值”（如 1000 元），且如果尝试修改 Alice 的余额（如另一笔转账），会被阻塞，一直等待锁释放。
若程序崩溃：连接断开，数据库自动回滚，Alice 的余额恢复为 1000 元，锁被释放。

如何避免？

代码中强制事务闭环：使用 try...finally 或类似机制，确保事务最终要么 COMMIT（成功时），要么 ROLLBACK（失败时）。

try:
    conn.begin()  # 开始事务
    # 执行操作
    conn.commit()  # 成功则提交
except:
    conn.rollback()  # 失败则回滚
finally:
    conn.close()  # 确保连接关闭

设置事务超时时间：部分数据库支持配置事务超时（如 MySQL 的 innodb_lock_wait_timeout），避免事务长期阻塞。
监控活跃事务：通过数据库工具（如 SHOW PROCESSLIST 或 pg_stat_activity）定期检查长期未提交的事务，及时处理。

总之，忘记 COMMIT 或 ROLLBACK 是开发中常见的问题，虽然数据库会在连接断开时自动回滚，但可能在此之前引发锁阻塞、性能下降等问题，因此必须通过代码逻辑确保事务的“闭环”。

在数据库操作和编程语境中，BEGIN 和 END 的作用因场景不同而有所区别，主要分为 事务控制 和 代码块界定 两种场景：

1. 事务控制中的 `BEGIN`

在数据库事务中，BEGIN（或其变体，如 BEGIN TRANSACTION、START TRANSACTION）的核心作用是 标记一个事务的开始。

作用：
执行 BEGIN 后，后续的一系列 SQL 操作（如 INSERT、UPDATE、DELETE 等）会被纳入同一个事务中，处于“临时生效”状态。这些操作不会立即写入数据库永久存储，直到执行 COMMIT（提交事务）才会永久生效；若执行 ROLLBACK（回滚事务），则会撤销所有操作。

示例（MySQL 中）：

BEGIN;  -- 开始事务
UPDATE accounts SET balance = balance - 300 WHERE id = 1;  -- Alice 扣钱
UPDATE accounts SET balance = balance + 300 WHERE id = 2;  -- Bob 加钱
COMMIT;  -- 提交事务，所有操作永久生效

注意：
不同数据库对“开始事务”的语法略有差异：
- MySQL 支持 BEGIN 或 START TRANSACTION；
- SQL Server 常用 BEGIN TRANSACTION；
- PostgreSQL 支持 BEGIN。

2. 代码块中的 `BEGIN...END`

在数据库存储过程、函数或脚本中，BEGIN 和 END 通常成对出现，用于 界定一个逻辑代码块，类似编程语言中的 {}（大括号）。

作用：
将多条 SQL 语句或逻辑（如条件判断、循环）组合成一个整体，确保它们被作为一个单元执行。常用于存储过程、触发器或批处理脚本中。

示例（SQL Server 存储过程中）：

CREATE PROCEDURE check_balance
    @account_id INT
AS
BEGIN  -- 代码块开始
    DECLARE @balance DECIMAL(10,2);
    SELECT @balance = balance FROM accounts WHERE id = @account_id;
    
    IF @balance < 0
    BEGIN  -- 嵌套代码块
        PRINT '余额异常';
    END  -- 嵌套代码块结束
    ELSE
    BEGIN
        PRINT '余额正常';
    END
END;  -- 代码块结束

注意：
- 这种用法不直接控制事务，仅用于组织代码结构；
- 不同数据库的语法细节可能不同（如 Oracle 的 PL/SQL 用 BEGIN...END;，且结尾需加分号）。

关键区别

场景	`BEGIN` 作用	`END` 作用	关联命令
事务控制	启动事务，标记操作的起点	无单独的 `END` 命令（事务结束用 `COMMIT` 或 `ROLLBACK`）	`COMMIT`、`ROLLBACK`
代码块界定	标记代码块的开始	标记代码块的结束	常与 `IF`、`WHILE` 等配合

简单说：BEGIN 要么用于“启动事务”，要么用于“开始一个代码块”；END 仅用于“结束一个代码块”，事务的结束永远依赖 COMMIT（提交）或 ROLLBACK（回滚），而非 END。

数据库中事务、指令、写法解读

数据库事务示例：银行转账操作

代码解析

1. 先创建数据库和表（准备环境）

2. 用SQL语句执行转账事务（核心逻辑）

3. 遇到异常时的回滚操作

4. 用存储过程封装事务（更贴近实际应用）

5. 调用存储过程测试事务

事务核心SQL命令总结

1. 若事务因“连接断开”而结束（最常见）

2. 若事务未结束（连接仍保持）

总结

1. 事务长期处于“活跃状态”，修改未持久化

2. 可能导致锁阻塞，影响并发性能

3. 连接断开后，事务会被自动回滚

4. 长期占用数据库资源，可能引发性能问题

举例说明

如何避免？

1. 事务控制中的 `BEGIN`

2. 代码块中的 `BEGIN...END`

关键区别

网站公告

今日签到

热门文章

最新发布

数据库中事务、指令、写法解读

数据库事务示例：银行转账操作

代码解析

1. 先创建数据库和表（准备环境）

2. 用SQL语句执行转账事务（核心逻辑）

3. 遇到异常时的回滚操作

4. 用存储过程封装事务（更贴近实际应用）

5. 调用存储过程测试事务

事务核心SQL命令总结

1. 若事务因“连接断开”而结束（最常见）

2. 若事务未结束（连接仍保持）

总结

1. 事务长期处于“活跃状态”，修改未持久化

2. 可能导致锁阻塞，影响并发性能

3. 连接断开后，事务会被自动回滚

4. 长期占用数据库资源，可能引发性能问题

举例说明

如何避免？

1. 事务控制中的 BEGIN

2. 代码块中的 BEGIN...END

关键区别

网站公告

今日签到

热门文章

最新发布

1. 事务控制中的 `BEGIN`

2. 代码块中的 `BEGIN...END`