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.