SAVEPOINT savepoint_name;

ROLLBACK TO SAVEPOINT savepoint_name;

RELEASE SAVEPOINT savepoint_name;

-- 创建测试表
CREATE TABLE account (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

-- 插入测试数据
INSERT INTO account VALUES (1, 'Alice', 1000.00);
INSERT INTO account VALUES (2, 'Bob', 500.00);

-- 开始事务
BEGIN;

-- 查看初始状态
SELECT * FROM account WHERE id IN (1,2);

-- Alice向Bob转账100元
UPDATE account SET balance = balance - 100 WHERE id = 1;
SELECT * FROM account WHERE id IN (1,2);

-- 设置保存点sp1
SAVEPOINT sp1;

-- Bob收到100元
UPDATE account SET balance = balance + 100 WHERE id = 2;
SELECT * FROM account WHERE id IN (1,2);

-- 设置保存点sp2
SAVEPOINT sp2;

-- 再次转账50元
UPDATE account SET balance = balance - 50 WHERE id = 1;
UPDATE account SET balance = balance + 50 WHERE id = 2;
SELECT * FROM account WHERE id IN (1,2);

-- 设置保存点sp3
SAVEPOINT sp3;

-- 回滚到sp2保存点（撤销第二次转账）
ROLLBACK TO SAVEPOINT sp2;
SELECT * FROM account WHERE id IN (1,2);

-- 释放sp1保存点（删除sp1及之后的所有保存点）
RELEASE SAVEPOINT sp1;

-- 尝试回滚到sp3会失败（因为sp3已被删除）
-- ROLLBACK TO SAVEPOINT sp3; -- 这行会报错

-- 提交事务
COMMIT;

-- 查询最终余额
SELECT * FROM account;

+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
| 1  | Alice | 900.00  |
| 2  | Bob   | 600.00  |
+----+-------+---------+