Thursday, October 23, 2008

Fun with SQL transactions, ADO.NETand how ExecuteScalar can bite you.

Today I wanted to create a section of code that grouped a bunch of stored procedure (SP) calls into one transaction. If any of these stored procedures failed, I want to rollback the entire transaction. It basically went something like this:

Call SP:CreateCase
Call SP:LinkIdentifierToCase
Call SP:CreatePatient
Call SP:LinkPatientToCase

Ok, first thing I tried was the .NET 2.0 TransactionScope. I won't go into details here, there are plenty of sites online how to use it. Anyway, after some digging I found that TransactionScope only works with MSDTC service enabled on the SQL server when you are running SQL 2000! Ahhh.. That's a bit heavy since it's not distributed by any means.

So, I went back to look at the ASP.NET 1.x stuff, which is the SqlConnection.BeginTransaction method. OK, looks good on the surface.. but what's it really doing? Well, I took a look at SQL profiler to find out.. And the answer is... drum roll please... Simple sending T-SQL commands BEGIN TRANSACTION, COMMIT, and ROLLBACK.

Well, that's not too bad, at least I understand it. However, bigger issues came up when your SPs have transactions in them. See, while SQL Server claims to support nested transactions, it really doesn't. For example, what would you expect this to do:



begin transaction trans1
select @@trancount
begin transaction trans2
select @@trancount
rollback transaction trans2
select @@trancount
commit transaction trans1


I would expect it to roll back the entire transaction because trans2 failed. However, commit transaction trans1 shouldn't throw an error. Guess what, as soon as rollback is executed, all nested layers are rolled back. Instead, it should nicely fall out. OK, trans2 failed, one nested layer to go. When we see another commit or rollback, we know to rollback the entire thing. Anyway, this makes things "interesting"...

What ends up happening just isn't what you expect. So, you just need to be prepared for stuff. Luckily, it seems generally ADO.NET can handle this for you. I made a simple SP that either happily completes with a COMMIT or ends with a ROLLBACK. It is:



CREATE Procedure TestProc (@DoError int)
AS
BEGIN TRAN
insert into TestTable (Name) VALUES ('Test')
if (@DoError=0)
COMMIT
else
ROLLBACK
GO


Then I created my test code:



SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand("exec TestProc 0", conn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

cmd = new SqlCommand("exec TestProc 1", conn);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();

tran.Commit();
}
catch (Exception)
{
tran.Rollback();
}
conn.Close();


Here's the output of SQL profiler:



As I would expect, while the first call the SP succeeded, the second one failed, and all the "test" table should be empty. In fact, it was. Luckily for us, ADO.NET executes a IF @@TRANCOUNT > 0 after executing each statement. This allows it to know when to throw an exception if a ROLLBACK occurs in the T-SQL, which I'm catching in the try/catch block. However, I still call tran.RollBack just to ensure the exception wasn't caused by something else, even maybe an error message (but not rollback!) from the SP itself. And look, ADO.NET is smart enough to check if the @@TRANCOUNT is great than zero before calling ROLLBACK. I can't say it's that smart when you call trans.Commit, but you should never be calling Commit if you get an exception.

So, I thought my work was done, until I found another curious issue. It seems that ExecuteScalar does not throw an exception even if a rollback occurred within the SP or an error was generated by the SP. This obviously causes a problem in my book! There is a nice forum discussion about this on MSDN.

So, conclusion. Don't blindly use BeginTransaction, especially if you have stored procedures with transactions within them. Secondly, if you want to capture any sort of error from the SQL server, don't use ExecuteScalar.

3 comments:

  1. Another good one Mr.... hey can you either expand on this a little or give a couple of links... it'd be nice to have some information about .NET transaction stuffs:


    Ok, first thing I tried was the .NET 2.0 TransactionScope. I won't go into details here, there are plenty of sites online how to use it. Anyway, after some digging I found that TransactionScope only works with MSDTC service enabled on the SQL server when you are running SQL 2000! Ahhh.. That's a bit heavy since it's not distributed by any means.

    ReplyDelete