What's the point of BEGIN TRANSACTION; ... ROLLBACK;

Question

I understand that a transaction will automatically roll back whenever an error occurs. I read up on this for T-SQL from Microsoft and I found this syntax:

BEGIN TRANSACTION;
    STATEMENT1;
    STATEMENT2;
    STATEMENT3;
ROLLBACK;

Why? If you intend to not do things anyway, why bother?
Specifically I found this here: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-2017#examples
But in other places as well.

Useful when you want to try out something and not destroy your entire database in the process. You can start a transaction, do your stuff (updates, inserts, deletes), verify that the results are what you expect, and then rollback all changes (and possibly try again later, after fixing your code - I never get tricky stuff totally right the first time around) ...... — marc_s, Jul 12 '19 at 07:31

score 1 · Accepted Answer · answered Jul 12 '19 at 07:32

The documentation link where you saw that is only for demonstration purposes. It is showing what happens when you begin a transaction, try to insert some rows and then roll it back. Then another insert statement is executed and the reader is supposed to get the idea of how transaction rollback works (by looking at the result set that only the second insert made it and the transaction that was rolled back didn't affect the data).

Usually rolling back in the way you have written is used for testing purposes only, while developing for example.

score 0 · Answer 2 · answered Jul 12 '19 at 07:34

0

That's just a bit of sql to show you what rollback does. It will always rollback, not just on an error.

Normally you'd only roll back on an error, see SQL Server - transactions roll back on error?

for details.

answered Jul 12 '19 at 07:34

Tony Hopkinson

20,172
3
31
39

What's the point of BEGIN TRANSACTION; ... ROLLBACK;

2 Answers2