Configuring JDBC Savepoint usage for safe rollback after DDL schema changes
0 reputation · 29 Sept 2020, 06:05 UTC
Goal
Ensure that a transaction can be rolled back to a known point after executing a schema‑altering statement (e.g., ALTER TABLE) so that any unintended data changes are undone while preserving the ability to commit other work.
Constraints / Uncertainty
Many relational databases issue an implicit commit when a DDL statement is executed, which may terminate the current transaction and release any Savepoints set before the DDL. The JDBC Savepoint API does not specify whether a Savepoint remains valid after such an implicit commit, and behavior can vary across drivers and DBMS vendors.
Additionally, the UserTransaction interface (JTA) does not expose Savepoint operations, so container‑managed transactions may lack the fine‑grained rollback needed for schema‑change safety.
- Does a Savepoint created with Connection.setSavepoint() remain usable for Connection.rollback(savepoint) after an ALTER TABLE that triggers an implicit commit?
- If the Savepoint is invalidated, what JDBC‑level mechanisms can be used to achieve a safe rollback of data changes made before the DDL?
- Are there vendor‑specific properties or connection settings that control Savepoint preservation across DDL in popular JDBC drivers (e.g., PostgreSQL, MySQL, Oracle)?
java