The last time we were together, I discussed DCL and TCL. That post ended with a quick paragraph on transactions and how they can help with data integrity. Today, I want to go more in-depth on this very important topic.
Transactions always – and I do mean always – start with the phrase BEGIN TRAN. Yes, you can spell out TRANSACTION instead of TRAN if you’d like – but I take all of the shortcuts that I can so I’ll be using TRAN.
Now, ending a transaction is not always as easy. There are two ways that a TRAN can end – either COMMITed or in a ROLLBACK. For the rest of this post, we’ll go into why you would use one of those over the other.
A COMMIT is a statement that most data developers will want to use most of the time. This is because this means that all of the data that has been inserted/updated/deleted/whatnot since the BEGIN statement will actually be, well, committed. SQL Server will actually mark the changes that have been done as permanent at that point.
Since COMMIT means that we want to mark our changes as permanent, then you’re probably thinking that means that ROLLBACK means that we don’t want to do our changes. And you’d be right!
Most developers when they start out working with transactions do the following work in their code:
BEGIN TRAN
-- Insert/Update/Delete/Whatnot
COMMIT TRAN
This, while better than nothing, isn’t truly using the transaction in a helpful way. To do that truly requires the beauty of a TRY/CATCH block – which has been available in SQL Server since SQL Server 2012.
In our next blog post, we’ll go over how the TRY/CATCH block works in conjunction with TRANSACTIONs and how it can help with data integrity.
So until then, let’s end this post with a track from that database protege Narissa and Wiz Khalifa all about TRANSACTIONS. Well, maybe they’re talking about a different kind of transaction, but it is a good song.
1 comment