长事务想撤几步TiDB SAVEPOINT 快速部分回滚【免费下载链接】tidbTiDB is built for agentic workloads that grow unpredictably, with ACID guarantees and native support for transactions, analytics, and vector search. No data silos. No noisy neighbors. No infrastructure ceiling.项目地址: https://gitcode.com/GitHub_Trending/ti/tidb长事务中途想撤掉某几步写入又不想丢掉整个事务。TiDBSAVEPOINT支持ROLLBACK TO SAVEPOINT乐观、悲观两种模式都能用。 先确认你的环境能不能用保存点SAVEPOINT的执行逻辑在执行器里很短但三个前提必须先过在显式事务中执行→ autocommit 开着又不在事务里时SAVEPOINT是静默空操作随后的ROLLBACK TO报SAVEPOINT s1 does not exist未开启 binlog→ 开启时执行SAVEPOINT报SAVEPOINT is not supported when binlog is enabled悲观事务需开启 in-place constraint check会话变量tidb_constraint_check_in_place_pessimistic→ 关闭时报savepoint is not supported in pessimistic transactions when in-place constraint check is disabledTiDB 4.0 之后已不建议使用 binlogbinlog 那条限制在绝大多数部署中不会触发。源码入口pkg/executor/simple.go#L662⚡ 跑通部分回滚一段 SQL 走完全部流程这段 SQL 建好表、开悲观事务插入两行中间打保存点 s1回滚到 s1 后重写再回滚一次最后提交看落盘。DROP TABLE IF EXISTS t; CREATE TABLE t(id int, a int, UNIQUE INDEX idx(id)); BEGIN PESSIMISTIC; INSERT INTO t VALUES (1, 1); SAVEPOINT s1; INSERT INTO t VALUES (2, 2); ROLLBACK TO s1; -- undo writes after s1 INSERT INTO t VALUES (2, 2); -- the txn is still alive SELECT * FROM t; -- (1,1) and (2,2) are visible ROLLBACK TO s1; -- the same savepoint, again SELECT * FROM t; -- only (1,1) remains COMMIT; SELECT * FROM t; -- (1,1) is the only row on disk两个观察点ROLLBACK TO s1之后事务并没有结束(2,2) 能重新插进去同一个 s1 还能反复回滚COMMIT 后落盘的只有 (1,1)。保存点之前做过删除也一样DELETE FROM t WHERE id 1;SAVEPOINT s1;INSERT INTO t VALUES (1, 2);ROLLBACK TO s1;—— 插入被撤销保存点之前的删除保留提交后表为空。事务内的写入先落在 TiDB 的内存缓冲MemDB提交时才真正写入 TiKV——保存点就是这份内存状态的检查点回滚只倒带内存不碰远端。对应集成测试tests/integrationtest/t/executor/executor_txn.test#L1-L24回滚后保存点栈长什么样保存点在事务上下文里按栈记录。依次建 s1、s2、s3然后ROLLBACK TO s2SAVEPOINT s1; SAVEPOINT s2; SAVEPOINT s3; stack: [s1, s2, s3] ROLLBACK TO s2; stack: [s1, s2] # s3 is deletedROLLBACK TO SAVEPOINT name→ 删除目标之后的所有保存点目标本身保留RELEASE SAVEPOINT name→ 删除该保存点及之后的所有保存点不提交也不回滚事务回滚到 s2 之后再执行ROLLBACK TO s3会报[executor:1305]SAVEPOINT s3 does not exist。名称区分大小写s1和S1是两个不同保存点。对不上与 MySQL 的两处行为锁的释放MySQL 在ROLLBACK TO SAVEPOINT时释放保存点之后持有的锁 → TiDB 悲观事务不立即释放提交或整体回滚时统一放开 → 回退之后其他会话对相应行的FOR UPDATE可能仍要等到本事务结束AUTO_INCREMENT / SEQUENCE 值MySQL 回退保存点时不回收已分配的值 → TiDB 行为相同值不重用 → 提交后序列出现空洞属预期行为docs/design/2022-07-22-transaction-savepoint.md#L133✅ 确认回滚真的生效事务内SELECT对比回滚前后的行数与内容确认保存点之后的写入被撤销COMMIT 后SELECT验证落盘数据只包含最后一次回滚点之前的修改保存点状态无法直接查询再执行一次ROLLBACK TO/RELEASE SAVEPOINT看是否报SAVEPOINT ... does not exist间接判断它还在不在限制速查限制现象或报错来源binlog 开启SAVEPOINT is not supported when binlog is enabledpkg/executor/simple.go#L660悲观事务未开 in-place constraint checksavepoint is not supported in pessimistic transactions when in-place constraint check is disabledpkg/executor/simple.go#L668不在事务中autocommitSAVEPOINT静默空操作ROLLBACK TO报SAVEPOINT s1 does not existpkg/executor/simple.go#L665不回收 AUTO_INCREMENT / SEQUENCE提交后序列留空洞无报错docs/design/2022-07-22-transaction-savepoint.md#L122悲观锁不随回退释放其他会话FOR UPDATE等到事务 commit / rollbackdocs/design/2022-07-22-transaction-savepoint.md#L135【免费下载链接】tidbTiDB is built for agentic workloads that grow unpredictably, with ACID guarantees and native support for transactions, analytics, and vector search. No data silos. No noisy neighbors. No infrastructure ceiling.项目地址: https://gitcode.com/GitHub_Trending/ti/tidb创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考