<< IIS 7.0 is here. Isn't it? | Home | How to name Private Methods in C#? >>

ADO.Net SqlTransactions vs Transactions in SQL

This is a rewrite of a post to microsoft.public.dotnet.framework.adonet.

I'm having a discussion with my team leader concerning the use of
SqlTransactions.

We work on an ASP.Net 1.1 website. Up until now we've been using
ADO.Net SqlTransactions in our business logic layer to combine several data access layer method calls into one coherent, atomic action.

I am in favor of this approach as it supports isolating the business logic from the database and provides for greater code readability.

My team leader diagrees - he thinks that we need to get rid of all ADO.Net SqlTransactions and instead guarantee atomicity by combining data operations inside stored procedures with transactions.

The reasons why he says SqlTransactions are bad are:

1. If one of the running threads in the IIS just dies all of a sudden, it would leave a transaction open and stuck, putting strain on the DB and risking deadlocks.

2. The DBA won't be able to properly debug the SQL Server whenever deadlocks and critical loads are encountered. Because transactions are opened from a place outside of the DBA's scope he will have no way of
knowing how or why certain statements and stored procedures might be executing together or locking each other.

Would you agree with my team lead? Do reason 1 and/or reason 2 justify moving some of our business logic into our DB? If not, does anyone have contradictory examples or links?

I'm calling on whoever is reading this to share their knowledge as I find that I do not know ADO.Net SqlTransaction well enough at this time.

Print | posted on Wednesday, December 20, 2006 5:44 PM

Comments on this post

# re: ADO.Net SqlTransactions vs Transactions in SQL

Requesting Gravatar...
In my opinion, the team lead is correct.

As long as the entire transaction can be wrapped up in a stored procedure, that's the best way to go. That's what they're for.

Coming up the chain, into ADO.NET transactions and further I guess into MTS/COM+/WhateverItsCalledToday transactions is for those times when the logic needs to span past what a sproc can do.

If my transaction needs to update my local SQL Server as well as a remote system via a webservice or whatnot, obviously a sproc cant do that.

What is the purpose of the transaction? Is it really just to make sure that the whole thing is atomic within the database? Then why in the world would you want to break up it's execution path? You wouldn't want to behave like a single SELECT statement. Send a command and the database performs.

That's my understanding anyway.
Left by Travis Laborde on Dec 21, 2006 1:05 PM

# re: ADO.Net SqlTransactions vs Transactions in SQL

Requesting Gravatar...
Hi Travis and thanks for your response.

The reason why I don't want to move these transaction into stored procedures is that they are part of the business logic.

My process could be:
Receive data from client
Update 1 record in table A.
If data is so and so - insert 2 records in table B.
Else, delete 1 record in table C and another in table D.

I find that putting decision trees like that into store procedures undermines the separation into layers.

What's more, SQL is not the place to put decisions in. I think SQL if for basic data manipulation not business rules.

Don't you think that would justify moving the transactions upwards to the DAL/BLL ?

Do you think ADO.net transactions will inevitably put a strain on the SQL Server?

Thanks!
urig





Left by urig on Dec 21, 2006 2:03 PM

# re: ADO.Net SqlTransactions vs Transactions in SQL

Requesting Gravatar...
Urig, if the "receive data from client" is part of the transaction then yes - I agree it would be best in the BLL not the sproc.

It sounds to me like once you receive the data from the client you know all you need to know to decide which path to take. Update one table and/or insert into another and/or delete from another etc.

I have my data from the client, and now I know that I need to take either path1, path2 or path3.

Path1 - sproc that updates table A and nothing else.

Path2 - sproc that updates table A and also inserts records in table B.

Path 3 - sproc that updates table A and also deletes records in tables C and D.

So, my BLL would get the data from the client, make a decision as to which path to follow and use SQL transactions inside the appropriate sproc to do the work.


Now if getting the data from the client happens within those paths - such as "updating table A causes triggers to fire and God only knows what I'll find and then after that happens I have to determine which path to follow...." In that case all the logic has to be in the SQL.

IMHO :)
Left by Travis Laborde on Dec 22, 2006 2:08 PM

# re: ADO.Net SqlTransactions vs Transactions in SQL

Requesting Gravatar...
Hi urig,

I think the BLL way (as you feel is better) because:

1. Database is meant for data-housing and not for implementing business logic ( as our Australian DBA told us)
2. It is easy to find the thread locked in BLL by DBA
3. Tomorrow, if the databse needs to be changed, the effort require would be much more in your Team Leads case
4. The concept of "all in one call" is fading and rather "mulltiple but single call" is getting popular.

As the name sugegst.. Business Layer and Data Layer.. The names in itself clears out things!
Left by Deepak on Jul 27, 2008 8:53 AM

# re: ADO.Net SqlTransactions vs Transactions in SQL

Requesting Gravatar...
A sproc per biz logic path displays the exact reason why generally you'd not want embed biz logic in the database.
Left by Bill on Sep 04, 2008 3:18 PM

Your comment:

 (will show your gravatar)