Question
Configuring JDBC Savepoint usage for safe rollback after DDL schema changes
Neel Byte
0 reputation · 29 Sept 2020, 06:05 UTC
56.5K views
Loading…
0 reputation · 29 Sept 2020, 06:05 UTC
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.
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.
34775 reputation · 29 Sept 2020, 12:51 UTC
In JDBC a Savepoint is only guaranteed to remain valid if the database does not perform an implicit commit when the DDL statement is executed. In practice:
javaALTER TABLE can be rolled back to, undoing the schema change and any other statements that followed it.SET session_replication_role = replica is not used) automatically commit the transaction when a DDL statement runs. In that case the savepoint is invalidated and Connection.rollback(savepoint) will not revert the schema change.When a container‑managed transaction (JTA UserTransaction) is in use, the API does not expose Savepoint operations, so you must either:
oracle.jdbc.OracleConnection can expose setSavepoint even inside a JTA transaction).Connection.setAutoCommit(false) starts an explicit transaction.Connection.setSavepoint() the returned object is valid until an explicit commit() or rollback() occurs, or until the database forces an implicit commit.DatabaseMetaData.supportsTransactionIsolationLevel() and supportsSavepoints(); supportsTransactionalDDL() is not part of the spec but can be inferred from the vendor documentation.conn.setAutoCommit(false);Savepoint sp = conn.setSavepoint("preDDL");stmt.execute("ALTER TABLE …");conn.commit();SQLException, roll back to the savepoint: conn.rollback(sp); (this undoes the DDL and any statements that followed).conn.setAutoCommit(true);conn.setAutoCommit(false) and conn.setSavepoint() as above.allowMultiQueries=true is unrelated; MySQL will still commit on DDL. The only workaround is to run the DDL in a separate transaction and then apply an inverse DDL if needed.conn.setAutoCommit(false) and conn.setSavepoint() work. For JTA you can cast to oracle.jdbc.OracleConnection and use setSavepoint inside the transaction.To tailor this guidance to your environment, could you let us know which database and JDBC driver you are using? If you are on a system that auto‑commits DDL, the recommended approach will differ.
Use comments to ask for clarification. Post a solution as an answer.
34,775 reputation · 29 Sept 2020, 08:39 UTC
While the distinction between transactional and non-transactional DDL is clear, it is important to note how JDBC handles the Savepoint object after an implicit commit occurs. In drivers for databases like MySQL, calling Connection.rollback(savepoint) after a DDL-triggered commit typically results in a SQLException because the savepoint identifier is no longer valid in the current transaction context.
To verify this behavior in a specific environment, you can use the following sequence:
setAutoCommit(false).Savepoint using setSavepoint().ALTER TABLE).rollback(savepoint).If the driver throws an exception or the schema change persists, the database is performing implicit commits. For these systems, schema safety must be managed via external snapshots or migration scripts with explicit undo logic, as the JDBC Savepoint API cannot override the engine's commit behavior.