Using Transactions

I wanted to jot some notes down about using transactions and @@trancount - mostly, I personally like checking for if I should begin a transaction or not at the beginning of stored procedures.  I know this comes from me being a software developer and thinking the database guy/gal handles all that, and then learning more about the database side of things and realizing database coding isnt always reviewed that closely by a DBA.  So, it came time for me to learn.  Now that I switched jobs, I dont have all the code blocks to copy/paste - so this blog post will help with that! :)

Lets start with the basics:

A transaction is a sequence of operations performed in it's own "bubble" from other processes and/or transactions occuring.  Once a transaction is completed, the effects are permanent in the database.

Now, that is a simple explanation, as with everything, there are many things that go into it - such as how transactions affect each other because the data that is modified in 1 transaction is the same data needed for another transaction, so the "bubble" a transaction is in isnt completely isolated - or how if a transaction is rolled back, there is no permanent effect because nothing was done, etc.  I am going to talk about some of the ways of starting, monitoring, and ending a transaction, as well as nested transactions, and possibly uncommitable transactions.

To start a transaction, the syntax is BEGIN TRANSACTION (or BEGIN TRAN).  No matter what, this is pretty much straight forward.  To end a transaction, you would either use COMMIT TRANSACTION, or ROLLBACK TRANSACTION, depending on if you want to make the changes you made, or not make the desired changes, respectively.  That, in and of itself, is all you need to start utilizing transactions now - but I will keep going with more juicy info. :)

Each time you use BEGIN TRAN, the global variable @@TRANCOUNT is incremented by 1, it starts out at 0.  This means, if you use BEGIN TRAN multiple times, the @@TRANCOUNT will keep adding 1 to the count, and you are nesting your transactions.  Just as begin tran increments, COMMIT TRAN decrements the @@TRANCOUNT by 1, each time it is used.  Lastly, ROLLBACK TRAN affects the outermost current transaction and everything in it, and the @@TRANCOUNT is reset to 0.  Therefore, the @@TRANCOUNT global variable can be very helpful in managing transactions. 
The Microsoft site has a quick and simple example of this, so I will show theirs (http://msdn.microsoft.com/en-us/library/ms187967(v=sql.105).aspx):

PRINT @@TRANCOUNT
--  The BEGIN TRAN statement will increment the transaction count by 1.
BEGIN TRAN
    PRINT @@TRANCOUNT
    BEGIN TRAN
        PRINT @@TRANCOUNT
--  The COMMIT statement will decrement the transaction count by 1.
    COMMIT
    PRINT @@TRANCOUNT
COMMIT
PRINT @@TRANCOUNT
--Results
--0
--1
--2
--1
--0

For the rest of the post, I am going to show you some code snippets to demo what I am talking about. For all the following examples, you need to setup 2 tables:

create table test_IceCreamFlavors (ID int
, IceCreamFlavor_Abbv varchar(5)
, IceCreamFlavor varchar(35));
create table test_PeopleWhoLikeIceCream (ID int
, PersonName varchar(25)
, IceCreamFlavor_Abbv varchar(5));

Using @@TRANCOUNT
Now, lets talk more about how to use the @@TRANCOUNT to start or not start a transaction, and whether to rollback or commit that transaction.  In the following example, I am only working with 1 transaction, I will get into nested transactions in just a bit.  As you can see, I check for the @@TRANCOUNT and only start a new transaction if there isnt one already started.  Then, at the end of the script, I again only worry about rolling back or committing if there is a transaction.

declare @li_error int;

select @@trancount as tranCount_atStart;
-- If there is a transaction already started, then dont start another one
-- for example, a stored procedure that calls another stored procedure
if @@TRANCOUNT = 0 begin
	begin tran;
end

insert into test_IceCreamFlavors (ID
	,IceCreamFlavor_Abbv
	,IceCreamFlavor)
values  (1,'VAN','Vanilla')
	,(2,'CHOC','Chocolate')
	,(3,'MCC','Mint Chocolate Chip');
set @li_error = @@error;

select * from test_IceCreamFlavors;

-- testing purposes only
-- uncomment this to test the rollback functionality
--set @li_error = 1;


-- if there is a transaction and it started here, finish it here
-- if it didnt start here, let the 
if @@trancount > 0 begin
	-- if there is an error, rollback, otherwise, commit
	if @li_error <> 0 begin
		rollback tran;
	end
	else begin
		commit tran;
	end
end

select * from test_IceCreamFlavors;

Nested Transactions
As I mentioned above, the @@TRANCOUNT is incremented and decremented by begin tran and commit tran, and reset by rollback tran.  That is really important to monitor when you have nested transactions.  There are 2 main things to remember: 1) a commit within a nested transaction doesnt really commit until the outer most transaction is committed; and 2) a rollback in any nested transaction affects the outermost transaction and everything in it.  Lets look at these more closely.

Regarding the first point - lets say you have a total of 3 transactions nested within each other.  If you commit an inner transaction, but rollback the outer transaction, the commit didnt actually make a change in the database; it was also rolled back.  The outermost result is what matters.  In the following example, the inner transaction is committed, but the outer is rolled back, therefore, even though the select statement after the inner transaction was committed showed the records, the final select statement produces no results.

declare @li_error int;

-- outer transaction: START
begin tran;


insert into test_IceCreamFlavors (ID
	,IceCreamFlavor_Abbv
	,IceCreamFlavor)
values  (1,'VAN','Vanilla')
	,(2,'CHOC','Chocolate')
	,(3,'MCC','Mint Chocolate Chip');
set @li_error = @@error

select * from test_IceCreamFlavors;



-- inner transaction: START
begin tran;

insert into test_PeopleWhoLikeIceCream (ID
	,PersonName
	,IceCreamFlavor_Abbv)
values  (1,'Glenda','MCC')  -- Mint Chocolate Chip - yummy!
	,(2,'Big Bird','VAN')  -- Vanilla
	,(3,'Grover','CHOC')   -- Chocolate

commit tran;
-- inner transaction: END

select * from test_PeopleWhoLikeIceCream;



rollback tran;
-- outer transaction: END

select * from test_IceCreamFlavors;
select * from test_PeopleWhoLikeIceCream;

Regarding the second point - if you use rollback tran within any of the nested transactions, the entire thing rolls back and nothing you did is changed in the database. In the same example above, try to flip the commit tran; and rollback tran; statements - that way the inner transaction has a rollback, and the outer transaction has a commit. GOTCHA - since the rollback affects ALL associated transactions, nothing happened, and you see an error indicating that the commit tran has no corresponding begin tran.

SAVE points and Rollbacks
This part is coming... hold tight!

My Thoughts/Use
To make sure I am keeping a single transaction as the default behaviour, I have come up with the following solution. It isnt really elegant, but I use a boolean value to check whether a transaction started within that procedure or not. I am only going to paste the opening and closing snippets, but hopefully you get the idea. If you have a better way - please let me know! I am still doing this through trial and error. :)

-- ===== BEGINNING SNIPPET
declare @li_error	int
	,@lb_tranStartedHere	bit

set @lb_tranStartedHere = 0
-- if there is not already a transaction, start one 
-- and indicate it started in this proc
if @@TRANCOUNT = 0 begin
	begin tran
	set @lb_tranStartedHere = 1
end



-- ===== ENDING SNIPPET
-- if there is a transaction and it started here, finish it here
-- if it didnt start here, let the 
if @@trancount > 0 and @lb_tranStartedHere = 1 begin
	-- if there is an error, rollback, otherwise, commit
	if @li_error <> 0 begin
		rollback tran
	end
	else begin
		commit tran
	end
end

Uncommittable Transactions
Lastly, I wanted to bring up another thing to watch out for. Since SQL Server 2005, the TRY... CATCH blocks can be used. This helps trap errors nicely. However, when using transactions, they can also end up making a transaction become unable to be committed or rolled back. The Microsoft website has a good explanation of it (http://msdn.microsoft.com/en-us/library/ms179296(v=sql.90).aspx). If you use a TRY... CATCH block, you can use the XACT_STATE function before you attempt to commit or rollback to check whether it is able to or not. If the XACT_STATE global variable has a value of -1, than the transaction is uncommittable. The values for XACT_STATE are either 1 or -1, so, make sure you test for it. If the value = 0, then there arent any transactions (much like a @@TRANCOUNT = 0), and an error would be thrown.

Wrap Up
I hope that helps someone - I did a lot of reading into this while making the mailtruck procedures and such. I wanted to make sure I caught errors and didnt have nested transactions unless I specifically meant to. TTFN!