Menu
Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.
DDL commands are auto commit only if they are successfully executed without errors. If DDL command fails, the ongoing transaction is still active in the session and not committed into the database.
Data Definition Language (DDL) commands perform an implicit commit, which also confirms all outstanding DML changes in the current session. If you decide you don't want to keep some uncommitted changes, you can throw them away using using the ROLLBACK statement.
If you're halfway through a transaction and perform a DDL statement, such as truncating a table, then the transaction commits.
I was wondering whether this was always the case and by definition, or is there a setting hidden somewhere that would rollback the transaction instead of committing.
Thanks.
Edit to clarify...
I'm not looking to rollback after a truncate. I just want to confirm that statements already carried out are absolutely always going to be committed before a DDL. Just want to make sure there isn't a system property somewhere that someone could set to wreck my code.
I understand the need to commit before and after a DDL, but conceptually I'd have thought the same consistency requirement could be achieved with a rollback before the DDL and a commit after.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With