当前位置： 首页 > news >正文

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

news 2025/9/5 7:12:08

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

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

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

import sqlite3
from sqlite3 import OperationalErrordef 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: 转出账户IDto_id: 转入账户IDamount: 转账金额返回:bool: 转账是否成功"""conn = Nonetry:# 连接数据库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 Trueexcept Exception as e:# 发生异常，回滚事务if conn:conn.rollback()print(f"发生错误: {str(e)}, 事务已回滚")return Falsefinally:# 确保连接关闭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()

代码解析

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

  1. 事务开始：通过设置conn.isolation_level = 'EXCLUSIVE'关闭自动提交，开始一个事务

  2. 核心操作：

    • 检查转出账户余额是否充足
    • 减少转出账户的余额
    • 增加转入账户的余额

  3. 异常处理：

    • 如果任何步骤出现错误（如余额不足、账户不存在等），则执行conn.rollback()回滚事务
    • 所有操作成功完成后，执行conn.commit()提交事务

  4. 数据一致性保证：

    • 转账操作要么完全成功，要么完全失败
    • 不会出现一方账户金额减少而另一方未增加的情况

你可以取消代码中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,  -- 转出账户IDIN to_id INT,    -- 转入账户IDIN amount DECIMAL(10, 2)  -- 转账金额
)
BEGINDECLARE from_balance DECIMAL(10, 2);  -- 存储转出账户余额DECLARE EXIT HANDLER FOR SQLEXCEPTION  -- 捕获所有SQL异常BEGINROLLBACK;  -- 异常时回滚SELECT '转账失败，已回滚' AS result;END;START TRANSACTION;  -- 开始事务-- 1. 检查转出账户是否存在SELECT balance INTO from_balance FROM accounts WHERE id = from_id;IF from_balance IS NULL THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转出账户不存在';END IF;-- 2. 检查余额是否充足IF from_balance < amount THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';END IF;-- 3. 检查转入账户是否存在IF NOT EXISTS (SELECT 1 FROM accounts WHERE id = to_id) THENSIGNAL 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 元，锁被释放。

如何避免？

  1. 代码中强制事务闭环：使用 try...finally 或类似机制，确保事务最终要么 COMMIT（成功时），要么 ROLLBACK（失败时）。
    try:conn.begin()  # 开始事务# 执行操作conn.commit()  # 成功则提交
except:conn.rollback()  # 失败则回滚
finally:conn.close()  # 确保连接关闭
  2. 设置事务超时时间：部分数据库支持配置事务超时（如 MySQL 的 innodb_lock_wait_timeout），避免事务长期阻塞。
  3. 监控活跃事务：通过数据库工具（如 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 < 0BEGIN  -- 嵌套代码块PRINT '余额异常';END  -- 嵌套代码块结束ELSEBEGINPRINT '余额正常';END
END;  -- 代码块结束

  • 注意：

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

关键区别

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

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

http://www.xdnf.cn/news/1451611.html

相关文章：

  • 搭建基于 Solon AI 的 Streamable MCP 服务并部署至阿里云百炼
  • 【多线程初阶】线程安全问题 死锁产生 何如避免死锁
  • 前端vue常见标签属性及作用解析
  • 零售消费企业的数字化增长实践，2025新版下载
  • 在 Debian 系统上清理缓存的方式和具体操作方法
  • Grafana - 监控磁盘使用率Variables使用
  • 卫星互联网安全风险及关键技术探索
  • 【深度学习】P1 引言（待完成）
  • Conda 常用命令大全
  • Axure RP 9 Mac 交互原型设计
  • iPhone17再爆猛料？苹果2025秋季发布会亮点抢先看
  • Jenkins调用ansible部署lnmp平台
  • 阿里云-基于通义灵码实现高效 AI 编码 | 1 | 在 Visual Studio Code 中安装和使用灵码
  • Redis vs Memcached vs MongoDB：深入对比与选型指南
  • AE（自动编码器）技术解析
  • Photoshop - Photoshop 触摸功能
  • 2025高教社杯国赛数学建模选题建议+初步分析
  • Java Web ：技术根基与产业实践的多维耦合
  • CSS 渐变边框
  • tensorflow常用使用场景
  • 开源免费工具，使用 Copicseal 批量添加照片参数水印教程
  • 打造大师级渲染：10个高效工作流技巧，质效双升
  • VisionPro工业相机 硬触发操作前以及Vs实现
  • iOS 抓包工具怎么选？开发者的实战经验与选择指南
  • WEB3的资料——免费开放
  • 25高教社杯数模国赛【C题国一亲授思路+问题解析】第四弹
  • macOS下基于Qt/C++的OpenGL开发环境的搭建
  • 2025最新版鸿蒙HarmonyOS开发工具安装使用指南
  • 记录一次 Rclone挂载网盘，Emby播放视频出现连续跳集的原因分析以及解决
  • Spring Boot 拦截器（Interceptor）与过滤器（Filter）有什么区别？