存储过程中的事务与错误传播——批处理过程代码、回滚验证与应用调用边界

文章目录每日一句正能量前言1. 背景与问题2. 环境与数据3. 复现过程3.1 错误示例异常被吞掉3.2 错误示例过程返回码代替异常4. 方案实施4.1 先决定批处理语义整批原子还是部分成功4.2 MySQL整批原子过程4.3 为什么一定要 RESIGNAL4.4 回滚验证4.5 JDBC 调用4.6 应用事务和过程事务不要打架4.7 更推荐的应用控事务模式4.8 MyBatis 调用过程4.9 JPA / Hibernate 调用4.10 PostgreSQL过程和函数的事务能力不同4.11 PostgreSQL 异常处理示例4.12 部分成功批处理怎么设计4.13 SAVEPOINT 是否适合4.14 异常分类4.15 重试必须在过程调用结束之后4.16 过程里不要吞异常写日志后继续4.17 回滚测试必须自动化5. 结果对比实施前实施后6. 风险与复盘6.1 事务控制权必须只有一个主导者6.2 存储过程不适合无限长批次6.3 部分成功必须有结果表6.4 DDL 不能混入普通事务预期6.5 错误码要稳定6.6 过程也需要版本管理6.7 事务异常不能被 ORM 静默转换结语每日一句正能量汗水是荣誉的勋章伤疤是强者的印记。珍视自己为目标付出的每一分努力坦然接受过程中留下的生理或心理伤痕。它们不是丑的而是你曾真实战斗过的证明。你的勋章不在别处就在你身上。前言批处理场景很适合使用存储过程。例如月末结算、批量状态推进、库存对账、历史数据修复这些任务通常包含多条 SQL而且所有步骤都在同一个数据库内执行。把它们封装成过程可以减少应用与数据库之间的往返也能把一组稳定的数据处理逻辑集中管理。但存储过程真正难的地方不是CREATE PROCEDURE语法而是事务到底由过程控制还是由应用控制 过程内部某一步失败时前面的写入会不会留下 异常应该 RESIGNAL还是返回错误码 批量处理是一条失败全部回滚还是允许部分成功如果这些边界没有先设计清楚就很容易出现一种危险结果过程告诉应用“失败了” 数据库里却已经留下了一半数据。本文通过一个“批量订单结算”过程分别说明 MySQL 与 PostgreSQL 的事务和异常处理方式并给出 JDBC、MyBatis、JPA/Hibernate 调用示例以及最重要的回滚验证测试。1. 背景与问题假设系统每天凌晨需要执行订单结算。一次批处理包含创建批次记录 更新订单状态 生成结算流水 更新批次结果最初有人会写成CREATEPROCEDUREsettle_orders()BEGININSERTINTObatch_job(...);UPDATEordersSETstatusSETTLEDWHEREstatusPAID;INSERTINTOsettlement_ledger(...)SELECT...FROMordersWHEREstatusSETTLED;END;看起来很直接。但如果第三步INSERT settlement_ledger因为唯一键、字段长度或其他约束失败前面的INSERT batch_job UPDATE orders到底会不会保留答案取决于存储引擎 自动提交状态 是否显式开启事务 过程是否捕获异常 调用方是否已经存在事务所以存储过程开发必须把事务行为写成明确契约而不能依赖“数据库应该会帮我回滚”的直觉。2. 环境与数据示例环境JDK 21 Spring Boot 3.3 MySQL 8.0 PostgreSQL 15 HikariCP Spring JDBC MyBatis 3.x Hibernate 6 / JPA订单表CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(64)NOTNULLUNIQUE,amountDECIMAL(18,2)NOTNULL,statusVARCHAR(32)NOTNULL,settled_batch_noVARCHAR(64)NULL,updated_atTIMESTAMP(6)NOTNULL);批次表CREATETABLEbatch_job(batch_noVARCHAR(64)PRIMARYKEY,job_typeVARCHAR(32)NOTNULL,statusVARCHAR(16)NOTNULL,total_countINTNOTNULLDEFAULT0,success_countINTNOTNULLDEFAULT0,failed_countINTNOTNULLDEFAULT0,created_atTIMESTAMP(6)NOTNULL,finished_atTIMESTAMP(6)NULL);结算流水CREATETABLEsettlement_ledger(idBIGINTPRIMARYKEYAUTO_INCREMENT,batch_noVARCHAR(64)NOTNULL,order_noVARCHAR(64)NOTNULL,amountDECIMAL(18,2)NOTNULL,created_atTIMESTAMP(6)NOTNULL,UNIQUEKEYuk_order_no(order_no),KEYidx_batch_no(batch_no));测试数据INSERTINTOorders(order_no,amount,status,updated_at)VALUES(O-1001,100.00,PAID,NOW(6)),(O-1002,200.00,PAID,NOW(6)),(O-1003,300.00,PAID,NOW(6));3. 复现过程3.1 错误示例异常被吞掉MySQL 中最危险的一种写法DELIMITER$$CREATEPROCEDUREsettle_orders_bad(INp_batch_noVARCHAR(64))BEGINDECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGINSELECTFAILEDASresult;END;INSERTINTObatch_job(batch_no,job_type,status,created_at)VALUES(p_batch_no,SETTLEMENT,RUNNING,NOW(6));UPDATEordersSETstatusSETTLED,settled_batch_nop_batch_noWHEREstatusPAID;INSERTINTOsettlement_ledger(batch_no,order_no,amount,created_at)SELECTp_batch_no,order_no,amount,NOW(6)FROMordersWHEREsettled_batch_nop_batch_no;END$$DELIMITER;问题在于CONTINUE HANDLER捕获异常后继续控制流。如果调用环境处于自动提交模式或者过程没有显式事务前面成功的语句可能已经产生不可接受的部分结果。更危险的是应用只看到FAILED却不知道数据库是否已经写了一半。3.2 错误示例过程返回码代替异常有些过程会这样SETp_result-1;然后直接结束。如果应用忘记检查p_result调用就会被当成成功。错误传播最好利用数据库原生异常机制而不是完全依赖约定式返回码。4. 方案实施4.1 先决定批处理语义整批原子还是部分成功写过程之前先回答1000 条订单里 1 条失败 其他 999 条要不要保留如果要求整批要么全部成功要么全部失败那么过程应该使用一个完整事务。如果允许部分成功就应该显式记录每条结果而不是靠异常“碰巧跳过”。这两个目标不能混在一个模糊过程里。4.2 MySQL整批原子过程DELIMITER$$CREATEPROCEDUREsettle_orders(INp_batch_noVARCHAR(64))BEGINDECLAREv_totalINTDEFAULT0;DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;RESIGNAL;END;IFp_batch_noISNULLORCHAR_LENGTH(TRIM(p_batch_no))0THENSIGNAL SQLSTATE45000SETMESSAGE_TEXTbatch_no must not be empty;ENDIF;STARTTRANSACTION;INSERTINTObatch_job(batch_no,job_type,status,created_at)VALUES(p_batch_no,SETTLEMENT,RUNNING,NOW(6));UPDATEordersSETstatusSETTLED,settled_batch_nop_batch_no,updated_atNOW(6)WHEREstatusPAID;SETv_totalROW_COUNT();INSERTINTOsettlement_ledger(batch_no,order_no,amount,created_at)SELECTp_batch_no,order_no,amount,NOW(6)FROMordersWHEREsettled_batch_nop_batch_no;UPDATEbatch_jobSETstatusSUCCESS,total_countv_total,success_countv_total,failed_count0,finished_atNOW(6)WHEREbatch_nop_batch_no;COMMIT;END$$DELIMITER;这里真正关键的是DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;RESIGNAL;END;也就是发生异常 - 退出过程 - 回滚当前事务 - 把原异常继续抛给调用方4.3 为什么一定要 RESIGNAL如果只写ROLLBACK;却不RESIGNAL;过程可能表现为“正常结束”。应用就无法知道这一批实际上失败了。所以对于整批原子处理ROLLBACK RESIGNAL通常是更清晰的失败语义。4.4 回滚验证先人为制造失败。例如预先插入INSERTINTOsettlement_ledger(batch_no,order_no,amount,created_at)VALUES(OLD,O-1002,200.00,NOW(6));由于UNIQUE(order_no)过程插入O-1002时会冲突。执行CALLsettle_orders(B-FAIL-001);预期过程抛异常然后验证SELECT*FROMbatch_jobWHEREbatch_noB-FAIL-001;应该0 行再查SELECTorder_no,status,settled_batch_noFROMordersWHEREorder_noIN(O-1001,O-1002,O-1003);应该仍然status PAID settled_batch_no NULL这才说明批次记录 订单状态 结算流水真正实现了原子回滚。4.5 JDBC 调用publicvoidsettle(StringbatchNo)throwsSQLException{try(ConnectioncdataSource.getConnection();CallableStatementcsc.prepareCall({call settle_orders(?)})){cs.setString(1,batchNo);cs.execute();}}如果过程内部RESIGNALJDBC 会收到SQLException可以读取catch(SQLExceptione){log.error(settlement failed, batchNo{}, sqlState{}, code{},batchNo,e.getSQLState(),e.getErrorCode(),e);throwe;}不要把过程错误变成returnfalse;然后让上层误以为只是“业务没处理”。4.6 应用事务和过程事务不要打架这是最重要的边界之一。如果存储过程内部自己START TRANSACTION COMMIT那么应用层最好不要再假设Transactional可以把过程和其他 SQL 组成一个更大的原子事务。例如TransactionalpublicvoidrunBatch(){repository.insertAudit();procedureRepository.settle();repository.updateAnotherTable();}如果过程内部已经自行COMMIT整个事务边界就会变得非常难理解。因此常见设计有两种方案 A 事务完全由存储过程控制 应用只负责 CALL。 方案 B 过程不主动 COMMIT 事务完全由应用控制。不要两边都抢控制权。4.7 更推荐的应用控事务模式如果过程只是应用事务的一部分可以改成不主动提交。例如过程只执行 DMLCREATEPROCEDUREsettle_orders_body(INp_batch_noVARCHAR(64))BEGIN...END;应用TransactionalpublicvoidsettleAndAudit(StringbatchNo){jdbcTemplate.update( INSERT INTO app_audit(...) VALUES(...) );procedureRepository.callSettleBody(batchNo);anotherRepository.updateSummary(batchNo);}这时Spring 事务成为唯一事务控制者。过程发生异常SQLException - DataAccessException - Transactional 回滚全部这是 Java 微服务项目里非常清晰的一种边界。4.8 MyBatis 调用过程MapperselectidsettleOrdersstatementTypeCALLABLE{ CALL settle_orders(#{batchNo}) }/selectServicepublicvoidsettle(StringbatchNo){batchMapper.settleOrders(batchNo);}如果过程自己管理事务Service 不再额外定义一个覆盖过程的业务事务。如果过程不提交则Transactionalpublicvoidsettle(StringbatchNo){batchMapper.settleOrders(batchNo);}由 Spring 管理。4.9 JPA / Hibernate 调用JPAStoredProcedureQueryqueryentityManager.createStoredProcedureQuery(settle_orders);query.registerStoredProcedureParameter(p_batch_no,String.class,ParameterMode.IN);query.setParameter(p_batch_no,batchNo);query.execute();异常通常会被包装成PersistenceException所以应用层应统一映射。4.10 PostgreSQL过程和函数的事务能力不同PostgreSQL 中要特别区分FUNCTION PROCEDURE函数不能像过程一样随意控制事务。如果需要COMMIT / ROLLBACK应该使用CREATE PROCEDURE并通过CALL执行。4.11 PostgreSQL 异常处理示例如果由外部事务管理可以写CREATEORREPLACEPROCEDUREsettle_orders_body(p_batch_noTEXT)LANGUAGEplpgsqlAS$$DECLAREv_totalINTEGER;BEGINIFp_batch_noISNULLORbtrim(p_batch_no)THENRAISE EXCEPTIONbatch_no must not be emptyUSINGERRCODEP0001;ENDIF;INSERTINTObatch_job(...);UPDATEordersSETstatusSETTLED,settled_batch_nop_batch_noWHEREstatusPAID;GET DIAGNOSTICS v_totalROW_COUNT;INSERTINTOsettlement_ledger(...)SELECT...FROMordersWHEREsettled_batch_nop_batch_no;EXCEPTIONWHENOTHERSTHENRAISE;END;$$;这里RAISE继续把错误抛给调用方。4.12 部分成功批处理怎么设计如果 1000 条订单允许999 条成功 1 条失败就不应该用一个异常让整个批次回滚。可以建立CREATETABLEbatch_item_result(batch_noVARCHAR(64),order_noVARCHAR(64),statusVARCHAR(16),error_codeVARCHAR(32),error_messageVARCHAR(256),PRIMARYKEY(batch_no,order_no));然后逐条处理。注意部分成功模式本质上是另一种业务语义不应该和整批原子模式混在一起。4.13 SAVEPOINT 是否适合部分成功时可以考虑SAVEPOINTitem_begin;单条失败ROLLBACKTOSAVEPOINTitem_begin;然后记录错误继续下一条。但需要谨慎。如果一批有几十万条逐条 SAVEPOINT 会增加复杂度和资源开销。更实用的方式往往是每 100 / 500 条一个子批降低事务长度。4.14 异常分类过程错误大致分成参数非法 业务约束冲突 唯一键冲突 死锁 锁超时 连接异常 系统错误应用不能catch(Exceptione){retry();}死锁可以新事务有限重试。参数错误则重试没有意义。4.15 重试必须在过程调用结束之后如果数据库已经因为死锁回滚事务当前过程调用已经失败应用可以开启新的事务 重新 CALL不要在同一个事务上下文里继续尝试。4.16 过程里不要吞异常写日志后继续错误DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGININSERTINTOerror_log(...);END;然后继续后面的业务 SQL。这种写法会把失败变成部分成功。只有业务明确允许时才能这么做。否则使用EXIT HANDLER ROLLBACK RESIGNAL更安全。4.17 回滚测试必须自动化JUnitTestvoidbatchFailureShouldRollbackEverything(){assertThrows(DataAccessException.class,()-batchService.settle(B-FAIL-001));assertFalse(batchRepository.exists(B-FAIL-001));ListOrderordersorderRepository.findAllTestOrders();assertTrue(orders.stream().allMatch(o-PAID.equals(o.status())));}这类测试不是可选项。因为存储过程事务最容易出现代码看起来有 ROLLBACK 实际调用边界却和预期不同。5. 结果对比实施前过程多条 DML CONTINUE HANDLER 返回 FAILED问题错误可能被吞 前面写入可能残留 应用无法识别 SQLSTATE 事务控制权不清晰实施后整批原子模式START TRANSACTION 步骤 1 步骤 2 步骤 3 任一步失败 - EXIT HANDLER - ROLLBACK - RESIGNAL应用收到异常 按 SQLSTATE 分类 不再假装过程“返回失败但事务正常”回滚验证batch_job 不存在 orders 未改变 ledger 未新增这才是可信的事务语义。6. 风险与复盘6.1 事务控制权必须只有一个主导者最怕Spring Transactional 过程内部 START TRANSACTION 过程内部 COMMIT开发人员很难判断哪个 COMMIT 才是最终边界。必须明确应用控事务 或 过程控事务6.2 存储过程不适合无限长批次一次处理几百万行事务日志巨大 锁持有时间长 回滚成本高 主从延迟应拆成可重入小批次例如每批 1000 条。6.3 部分成功必须有结果表不能只输出success999 failed1还应该保存哪一条失败 错误码 错误原因 是否可重试否则补偿非常困难。6.4 DDL 不能混入普通事务预期不同数据库对 DDL 的事务语义不同。批处理过程里不要随意CREATE TABLE ALTER TABLE TRUNCATE再假设和普通 DML 一样可回滚。6.5 错误码要稳定调用方不应该解析MESSAGE_TEXT判断业务逻辑。应优先依赖SQLSTATE 厂商错误码 自定义错误码再映射到应用异常。6.6 过程也需要版本管理推荐Flyway / Liquibase Git 自动测试 回滚脚本过程代码不能成为 DBA 手工维护的“黑盒”。6.7 事务异常不能被 ORM 静默转换无论 MyBatis、JPA 还是 JDBC只要过程失败就应该确保异常最终能够到达事务管理层。如果 DAO 返回false 0 null而不是抛异常外层事务可能误提交。结语存储过程在批处理里真正有价值的地方不只是“减少网络往返”而是把一组数据库操作组织成一个明确的数据处理单元。但要做到可靠至少需要四个条件事务边界明确 异常必须传播 回滚结果可验证 批处理语义先定义可以把本文的核心原则总结为过程内失败要能回滚 回滚后错误要继续抛出 应用只在新的事务里决定是否重试。如果业务要求部分成功就显式设计结果表、子批次和补偿策略如果要求整批原子就不要用CONTINUE HANDLER把异常悄悄吞掉。存储过程并不是一个“把 SQL 打包起来”的工具。真正专业的过程开发必须把事务、异常和调用方行为一起设计。转载自https://blog.csdn.net/u014727709/article/details/165243184欢迎 点赞✍评论⭐收藏欢迎指正