✅ ROLLBACK 重点总结 + 操作示例

---

1. 作用本质：撤销未提交变更，保证原子性

START TRANSACTION;
UPDATE employees SET first_name = 'WrongName' WHERE emp_no = 10001;
ROLLBACK;  -- 修改被撤销

---

2. 触发时机：出错时手动或自动回滚

START TRANSACTION;
UPDATE employees SET first_name = 'Step1' WHERE emp_no = 10001;

-- 模拟出错
SELECT 1 / 0;  -- 报错：除零错误

ROLLBACK;  -- 业务判断后主动回滚

---

3. 影响范围：仅限当前事务

-- 会话 A
START TRANSACTION;
UPDATE employees SET first_name = 'A' WHERE emp_no = 10001;

-- 会话 B
SELECT first_name FROM employees WHERE emp_no = 10001;
-- 查询到原值，未被事务 A 提交

-- 会话 A
ROLLBACK;

---

4. 配套机制：Undo Log 支撑回滚行为

无法直接看 Undo Log，但你可以观察现象：

-- 事务内更新，之后回滚
START TRANSACTION;
UPDATE employees SET first_name = 'BeforeError' WHERE emp_no = 10001;
ROLLBACK;

-- 查询恢复原值（InnoDB 使用 Undo Log 还原）
SELECT first_name FROM employees WHERE emp_no = 10001;

---

5. 常见用途：业务校验失败时使用

START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 模拟库存不够或用户取消
ROLLBACK;  -- 取消订单创建和库存扣减

---

6. 与 COMMIT 区别：提交 vs 撤销

-- COMMIT 示例
START TRANSACTION;
UPDATE employees SET first_name = 'Committed' WHERE emp_no = 10001;
COMMIT;

-- ROLLBACK 示例
START TRANSACTION;
UPDATE employees SET first_name = 'ToBeRolledBack' WHERE emp_no = 10001;
ROLLBACK;

---

7. 与锁的关系：ROLLBACK 会释放锁

-- 会话 A
START TRANSACTION;
SELECT * FROM employees WHERE emp_no = 10001 FOR UPDATE;

-- 会话 B（此时被阻塞）
UPDATE employees SET first_name = 'Blocked' WHERE emp_no = 10001;

-- 会话 A
ROLLBACK;  -- 会释放锁，B 立刻继续执行

---

8. 不能回滚的操作：DDL 隐式提交

START TRANSACTION;
ALTER TABLE employees ADD COLUMN temp_col INT;
ROLLBACK;  -- 🚫 无效，ALTER 已自动提交

---

9. SAVEPOINT：可部分回滚

START TRANSACTION;
UPDATE employees SET first_name = 'Step1' WHERE emp_no = 10001;
SAVEPOINT sp1;
UPDATE employees SET first_name = 'Step2' WHERE emp_no = 10002;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;  -- 只保留 Step1 的修改

---

10. 自动提交下无效：autocommit 模式

SET autocommit = 1;

UPDATE employees SET first_name = 'AutoCommit' WHERE emp_no = 10001;
ROLLBACK;  -- 🚫 无效，更新已立即生效

---