SQL Saturday May 7, Truths and Myths about SQL Transactions

Author by Nick Adams

Sometimes as we learn a new language or code we tend to focus a lot on syntax and how to write structured "sentences" or "statements" but may forget to understand what type of impact that may have on the overall goal.  SQL Saturday on May 7th had a session to help break down some of those barriers by helping SQL Professionals understand more about what happens during SQL Transaction handling.  Let's take a look at some simple examples.

Our first code example was the following:

BEGIN TRAN A
    INSERT [TBL] VALUES('A')
    BEGIN TRAN B
        INSERT [TBL] VALUES('B')
    ROLLBACK TRAN B
COMMIT TRAN A

What happens if this is run?  Some thought the INSERT for A would work but it doesn't.  Why?  The implications of ROLLBACK: it will rollback the entire transaction.  Once "A" is committed, there's nothing to insert and thus the values are not inserted.

The next example was the following:

BEGIN TRAN AAAA
    INSERT [TBL] VALUES('A')
    BEGIN TRAN BBBB
        INSERT [TBL] VALUES('B')
    COMMIT TRAN AAAA
COMMIT TRAN BBBB

This example would insert the values because the Transactions don't care about names.  COMMIT TRAN AAAA is committing INSERT [TBL] VALUES ('B') and COMMIT TRAN BBBB is committing INSERT [TBL] VALUES ('A') once INSERT [TBL] VALUES ('B') is committed.

The next example tripped up most everyone:

BEGIN TRAN
TRUNCATE TABLE [TBL]
ROLLBACK TRAN

Most assumed that the ROLLBACK wouldn't work because TRUNCATE is a non logged activity.  This is true in that each row is not logged however the TRUNCATE is logged as a system table change and the activity can be rolled back as a single transaction that affected a single database.  In a sense, who cares about the rows.

While the session was about SQL Transaction myths and truths, the big takeaway from this for me was not just Transactions but the implications and the consequences when writing code.  Just these three small examples here show how important it is to truly understand what is happening behind the scenes.

Tags in this Article