产品概述
应用场景
产品架构
实例类型
兼容性说明
使用规范建议
BEGIN 语句BEGIN;
START TRANSACTION 语句START TRANSACTION;
SET autocommit=0;
SET autocommit=0 后，所有后续 SQL 语句都将在一个事务中执行，直到显式执行 COMMIT 或 ROLLBACK。START TRANSACTION;-- 更新用户表UPDATE users SET balance = balance - 100 WHERE user_id = 1;-- 插入订单记录INSERT INTO orders (user_id, amount, order_date)VALUES (1, 100, NOW());
COMMIT 语句提交事务：COMMIT;
ROLLBACK 语句回滚事务：ROLLBACK;
SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT savepoint_name;
RELEASE SAVEPOINT savepoint_name;
-- 创建测试表CREATE TABLE bank_account (account_id VARCHAR(20) PRIMARY KEY,account_name VARCHAR(50),balance DECIMAL(15, 2));-- 插入初始数据INSERT INTO bank_account VALUES('1001', '张三', 5000.00),('1002', '李四', 3000.00);
-- 1. 开始一个事务START TRANSACTION;-- 查看初始余额SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');+------------+--------------+---------+| account_id | account_name | balance |+------------+--------------+---------+| 1001 | 张三 | 5000.00 || 1002 | 李四 | 3000.00 |+------------+--------------+---------+2 rows in set (0.00 sec)-- 2. 创建第一个保存点（在扣款前）SAVEPOINT before_debit;-- 3. 从张三账户扣款1000元UPDATE bank_account SET balance = balance - 1000 WHERE account_id = '1001';-- 4. 创建第二个保存点（扣款后，存款前）SAVEPOINT after_debit;-- 5. 向李四账户存款1000元UPDATE bank_account SET balance = balance + 1000 WHERE account_id = '1002';-- 检查当前事务内的账户余额SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');+------------+--------------+---------+| account_id | account_name | balance |+------------+--------------+---------+| 1001 | 张三 | 4000.00 || 1002 | 李四 | 4000.00 |+------------+--------------+---------+2 rows in set (0.00 sec)-- 假设此时发现李四账户状态异常，需要回滚到存款操作之前-- 6. 回滚到第二个保存点（这将撤销存款操作，但保留扣款操作）ROLLBACK TO SAVEPOINT after_debit;-- 检查回滚后的余额（李四账户金额未变）SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');+------------+--------------+---------+| account_id | account_name | balance |+------------+--------------+---------+| 1001 | 张三 | 4000.00 || 1002 | 李四 | 3000.00 |+------------+--------------+---------+2 rows in set (0.00 sec)-- 7. 由于业务规则要求转账必须完整，我们决定回滚整个事务ROLLBACK; -- 回滚到事务开始状态，所有操作撤销-- 最终确认数据是否恢复原状SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');+------------+--------------+---------+| account_id | account_name | balance |+------------+--------------+---------+| 1001 | 张三 | 5000.00 || 1002 | 李四 | 3000.00 |+------------+--------------+---------+2 rows in set (0.00 sec)-- 8. 重新开始事务并进行完整转账START TRANSACTION;UPDATE bank_account SET balance = balance - 1000 WHERE account_id = '1001';UPDATE bank_account SET balance = balance + 1000 WHERE account_id = '1002';-- 9. 确认无误后提交事务，使更改永久生效COMMIT;-- 最终确认数据修改是否生效SELECT * FROM bank_account WHERE account_id IN ('1001', '1002');+------------+--------------+---------+| account_id | account_name | balance |+------------+--------------+---------+| 1001 | 张三 | 4000.00 || 1002 | 李四 | 4000.00 |+------------+--------------+---------+2 rows in set (0.00 sec)
文档反馈