TiDB 保存点实操:用 SAVEPOINT 与 ROLLBACK TO 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长事务连续执行十几笔写入,中途发现某几步有问题,最直觉的处理是直接ROLLBACK——但这样会把之前所有写入一并丢弃,整笔事务推重做。TiDB 支持SAVEPOINT(保存点)与ROLLBACK TO SAVEPOINT(回滚到保存点):在事务内记录一个检查点,回滚到该检查点时只撤销检查点之后的数据修改,事务本身继续可用,之后还能继续写入并正常提交。乐观与悲观两种事务模式均支持,可用于替代 gorm 等 ORM 在应用层实现部分回滚的做法。前置确认:哪些会话能使用 SAVEPOINT处于显式事务中(如BEGIN PESSIMISTIC、BEGIN)。依据:pkg/executor/simple.go 中executeSavepoint对!sessVars.InTxn() sessVars.IsAutocommit()的分支直接返回nil——不在事务中且 autocommit 开启时,SAVEPOINT是空操作,随后ROLLBACK TO s1会报[executor:1305]SAVEPOINT s1 does not exist。实例未开启 binlog。开启时SAVEPOINT报错SAVEPOINT is not supported when binlog is enabled(同文件的错误变量ErrSavepointNotSupportedWithBinlog);设计文档 2022-07-22-transaction-savepoint.md 同时注明 TiDB 4.0 之后不建议使用 binlog,该约束只影响少数部署。悲观事务需开启 in-place constraint check(约束即时检查):会话变量tidb_constraint_check_in_place_pessimistic关闭时,SAVEPOINT报错savepoint is not supported in pessimistic transactions when in-place constraint check is disabled。最小可跑示例:保存点只回滚最后一条插入CREATE TABLE t(id int, a int, UNIQUE INDEX idx(id)); BEGIN PESSIMISTIC; -- 开启悲观事务 INSERT INTO t VALUES (1, 1); SAVEPOINT s1; -- 记录检查点 s1(事务 MemDB 快照) INSERT INTO t VALUES (2, 2); ROLLBACK TO s1; -- 只回滚 s1 之后的 (2,2),事务不结束 SELECT * FROM t; -- 只剩 (1,1) COMMIT;分步实操:悲观事务中重复回退到同一保存点以下取自动态测试 executor_txn.test 的TestRollbackToSavepoint场景,预期输出摘自 executor_txn.result,为示例结果;真实环境中行内容以实际插入的数据为准。第一步:建表、开启事务、记录保存点。输入语句: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);预期输出:各语句执行成功,无结果集返回。第二步:回退到s1后继续写入。原因:ROLLBACK TO s1把事务 MemDB 回退到保存点检查点,(2,2) 的插入被撤销,但事务不终止,仍可写。输入语句:ROLLBACK TO s1; INSERT INTO t VALUES (2, 2); SELECT * FROM t;预期输出(示例结果):id a 1 1 2 2第三步:再次回退同一保存点。保存点可以反复使用,第二次回退把新插入的 (2,2) 再撤销。输入语句:ROLLBACK TO s1; SELECT * FROM t;预期输出(示例结果):id a 1 1第四步:提交并验证最终落盘。输入语句:COMMIT; SELECT * FROM t;预期输出(示例结果):id a 1 1第五步:确认保存点之前的修改不受回退影响。回滚只撤销保存点之后的变更,保存点之前执行的删除会保留——这是与整事务回滚最本质的行为区别。输入语句:DELETE FROM t; INSERT INTO t VALUES (1, 1); BEGIN PESSIMISTIC; DELETE FROM t WHERE id 1; SAVEPOINT s1; INSERT INTO t VALUES (1, 2); ROLLBACK TO s1; SELECT * FROM t; COMMIT; SELECT * FROM t;预期输出(示例结果):两次SELECT均只有表头、无数据行——(1,2) 被回滚,保存点之前的删除生效,提交后仍为空表。边界与异常:报错触发条件及与 MySQL 的差异保存点不存在:ROLLBACK TO/RELEASE SAVEPOINT指向不存在的名称时均报[executor:1305]SAVEPOINT s1 does not exist(ErrSavepointNotExists)。在事务外空操作过的SAVEPOINT,后续ROLLBACK TO同样落入该报错。ROLLBACK TO SAVEPOINT会删除目标保存点之后的所有保存点。依次创建s1、s2、s3后执行ROLLBACK TO s3,列表只剩s2、s3,再执行ROLLBACK TO s1报 1305;RELEASE SAVEPOINT s2则删除s2及其后的s3,不提交、不回滚事务,随后RELEASE SAVEPOINT s3报 1305。保存点名大小写不敏感:创建s1后执行SAVEPOINT S1,直接替换原条目而非新增(测试断言列表仍为 3 项且存储名仍为s1)。上述两条规则均由 txn_test.go 的TestTxnSavepoint0用例覆盖。锁释放时机与 MySQL 不同:MySQL 在ROLLBACK TO SAVEPOINT时释放保存点之后持有的锁;TiDB 悲观事务不立即释放,而是在事务提交或整体回滚时统一释放。回退保存点后,其他会话对相应行的SELECT ... FOR UPDATE可能一直等待到本事务结束。已分配的 AUTO_INCREMENT / SEQUENCE 值不回收,提交后值存在空洞;该行为与 MySQL 一致,属预期。状态验证:判断保存点是否仍然存在保存点记录在事务上下文的TxnCtx.Savepoints列表(SavepointRecord结构,见设计文档),没有 SQL 语句能直接查询该列表,只能用间接路径:执行ROLLBACK TO sp_name:报 1305 说明保存点不存在;不报错则存在。注意成功即真实回滚,有副作用。执行RELEASE SAVEPOINT sp_name观察是否报 1305;成功则代表该保存点及其后续保存点被删除。进程内断言(单元测试场景)可直接读取GetSessionVars().TxnCtx.Savepoints字段,TestTxnSavepoint0即靠它在每条语句后断言列表内容。限制汇总表限制项可观测现象依据来源binlog 开启SAVEPOINT is not supported when binlog is enabledpkg/executor/simple.go的ErrSavepointNotSupportedWithBinlog悲观事务未开启 in-place checksavepoint is not supported in pessimistic transactions when in-place constraint check is disabledpkg/executor/simple.go的executeSavepoint非事务中且 autocommitSAVEPOINT静默忽略,后续ROLLBACK TO报 1305pkg/executor/simple.go的executeSavepoint悲观锁不随回退释放其他会话FOR UPDATE等待到事务结束设计文档 MySQL Compatibility 一节AUTO_INCREMENT / SEQUENCE 不回收提交后自增值出现空洞设计文档 ROLLBACK TO SAVEPOINT Implementation 一节保存点列表不可直接查询只能靠 1305 报错间接探测pkg/executor/test/txn/txn_test.go的TestTxnSavepoint0【免费下载链接】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），仅供参考
TiDB 保存点实操:用 SAVEPOINT 与 ROLLBACK TO SAVEPOINT 只回滚长事务的一半写入
TiDB 保存点实操:用 SAVEPOINT 与 ROLLBACK TO 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长事务连续执行十几笔写入,中途发现某几步有问题,最直觉的处理是直接ROLLBACK——但这样会把之前所有写入一并丢弃,整笔事务推重做。TiDB 支持SAVEPOINT(保存点)与ROLLBACK TO SAVEPOINT(回滚到保存点):在事务内记录一个检查点,回滚到该检查点时只撤销检查点之后的数据修改,事务本身继续可用,之后还能继续写入并正常提交。乐观与悲观两种事务模式均支持,可用于替代 gorm 等 ORM 在应用层实现部分回滚的做法。前置确认:哪些会话能使用 SAVEPOINT处于显式事务中(如BEGIN PESSIMISTIC、BEGIN)。依据:pkg/executor/simple.go 中executeSavepoint对!sessVars.InTxn() sessVars.IsAutocommit()的分支直接返回nil——不在事务中且 autocommit 开启时,SAVEPOINT是空操作,随后ROLLBACK TO s1会报[executor:1305]SAVEPOINT s1 does not exist。实例未开启 binlog。开启时SAVEPOINT报错SAVEPOINT is not supported when binlog is enabled(同文件的错误变量ErrSavepointNotSupportedWithBinlog);设计文档 2022-07-22-transaction-savepoint.md 同时注明 TiDB 4.0 之后不建议使用 binlog,该约束只影响少数部署。悲观事务需开启 in-place constraint check(约束即时检查):会话变量tidb_constraint_check_in_place_pessimistic关闭时,SAVEPOINT报错savepoint is not supported in pessimistic transactions when in-place constraint check is disabled。最小可跑示例:保存点只回滚最后一条插入CREATE TABLE t(id int, a int, UNIQUE INDEX idx(id)); BEGIN PESSIMISTIC; -- 开启悲观事务 INSERT INTO t VALUES (1, 1); SAVEPOINT s1; -- 记录检查点 s1(事务 MemDB 快照) INSERT INTO t VALUES (2, 2); ROLLBACK TO s1; -- 只回滚 s1 之后的 (2,2),事务不结束 SELECT * FROM t; -- 只剩 (1,1) COMMIT;分步实操:悲观事务中重复回退到同一保存点以下取自动态测试 executor_txn.test 的TestRollbackToSavepoint场景,预期输出摘自 executor_txn.result,为示例结果;真实环境中行内容以实际插入的数据为准。第一步:建表、开启事务、记录保存点。输入语句: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);预期输出:各语句执行成功,无结果集返回。第二步:回退到s1后继续写入。原因:ROLLBACK TO s1把事务 MemDB 回退到保存点检查点,(2,2) 的插入被撤销,但事务不终止,仍可写。输入语句:ROLLBACK TO s1; INSERT INTO t VALUES (2, 2); SELECT * FROM t;预期输出(示例结果):id a 1 1 2 2第三步:再次回退同一保存点。保存点可以反复使用,第二次回退把新插入的 (2,2) 再撤销。输入语句:ROLLBACK TO s1; SELECT * FROM t;预期输出(示例结果):id a 1 1第四步:提交并验证最终落盘。输入语句:COMMIT; SELECT * FROM t;预期输出(示例结果):id a 1 1第五步:确认保存点之前的修改不受回退影响。回滚只撤销保存点之后的变更,保存点之前执行的删除会保留——这是与整事务回滚最本质的行为区别。输入语句:DELETE FROM t; INSERT INTO t VALUES (1, 1); BEGIN PESSIMISTIC; DELETE FROM t WHERE id 1; SAVEPOINT s1; INSERT INTO t VALUES (1, 2); ROLLBACK TO s1; SELECT * FROM t; COMMIT; SELECT * FROM t;预期输出(示例结果):两次SELECT均只有表头、无数据行——(1,2) 被回滚,保存点之前的删除生效,提交后仍为空表。边界与异常:报错触发条件及与 MySQL 的差异保存点不存在:ROLLBACK TO/RELEASE SAVEPOINT指向不存在的名称时均报[executor:1305]SAVEPOINT s1 does not exist(ErrSavepointNotExists)。在事务外空操作过的SAVEPOINT,后续ROLLBACK TO同样落入该报错。ROLLBACK TO SAVEPOINT会删除目标保存点之后的所有保存点。依次创建s1、s2、s3后执行ROLLBACK TO s3,列表只剩s2、s3,再执行ROLLBACK TO s1报 1305;RELEASE SAVEPOINT s2则删除s2及其后的s3,不提交、不回滚事务,随后RELEASE SAVEPOINT s3报 1305。保存点名大小写不敏感:创建s1后执行SAVEPOINT S1,直接替换原条目而非新增(测试断言列表仍为 3 项且存储名仍为s1)。上述两条规则均由 txn_test.go 的TestTxnSavepoint0用例覆盖。锁释放时机与 MySQL 不同:MySQL 在ROLLBACK TO SAVEPOINT时释放保存点之后持有的锁;TiDB 悲观事务不立即释放,而是在事务提交或整体回滚时统一释放。回退保存点后,其他会话对相应行的SELECT ... FOR UPDATE可能一直等待到本事务结束。已分配的 AUTO_INCREMENT / SEQUENCE 值不回收,提交后值存在空洞;该行为与 MySQL 一致,属预期。状态验证:判断保存点是否仍然存在保存点记录在事务上下文的TxnCtx.Savepoints列表(SavepointRecord结构,见设计文档),没有 SQL 语句能直接查询该列表,只能用间接路径:执行ROLLBACK TO sp_name:报 1305 说明保存点不存在;不报错则存在。注意成功即真实回滚,有副作用。执行RELEASE SAVEPOINT sp_name观察是否报 1305;成功则代表该保存点及其后续保存点被删除。进程内断言(单元测试场景)可直接读取GetSessionVars().TxnCtx.Savepoints字段,TestTxnSavepoint0即靠它在每条语句后断言列表内容。限制汇总表限制项可观测现象依据来源binlog 开启SAVEPOINT is not supported when binlog is enabledpkg/executor/simple.go的ErrSavepointNotSupportedWithBinlog悲观事务未开启 in-place checksavepoint is not supported in pessimistic transactions when in-place constraint check is disabledpkg/executor/simple.go的executeSavepoint非事务中且 autocommitSAVEPOINT静默忽略,后续ROLLBACK TO报 1305pkg/executor/simple.go的executeSavepoint悲观锁不随回退释放其他会话FOR UPDATE等待到事务结束设计文档 MySQL Compatibility 一节AUTO_INCREMENT / SEQUENCE 不回收提交后自增值出现空洞设计文档 ROLLBACK TO SAVEPOINT Implementation 一节保存点列表不可直接查询只能靠 1305 报错间接探测pkg/executor/test/txn/txn_test.go的TestTxnSavepoint0【免费下载链接】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），仅供参考
