SQL Transaction – can be rollback or cannot be rollback that is the question

Someday every developer get to the point where it’s time to use transaction in SQL. It’s really usefull thing. Honestly.

If you don’t know what transaction is in SQL, check full definition at wikipedia. ;o) In short – it’s a group of statements that allow us to group our queries and if something goes wrong in one of them, the changes are rolled back. Of course you should group them with some logic.

But first you need to remember, that not all queries can be rolled back. These two groups of statements below are most common used.

DML – Data Manipulation Language –  like SELECT, INSERT, UPDATE, DELETE –  and that kind of queries we can rollback.

DDL – Data Definition Language – like CREATE, DROP, ALTER – and that kind of queries we can’t rollback.

Just to you know, there is also DCL (Data Control Language) and our TCL (Transaction Control).

So basically what i want to say is never ever put statements from DDL to your transactions. ;o)

If you mix CREATE with INSERT and than UPDATE in one transaction (see CREATE is from DDL and cannot be rolled back), and if UPDATE fails, earlier effects of queries cannot be rollback.