SqlTransactions vs. transactions in the SQL code

19 Dec 2006 11:17 AM

urig

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
SqlTransactions in
our business logic layer to combine several data access layers method
calls into one coherent, atomic action.

My team leader says that we need to get rid of all SqlTransactions and
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, then can you
please give contradictory examples or links?

Thanks!

19 Dec 2006 5:20 PM

Mary Chipman [MSFT]

I agree with your team leader. Using SqlTransaction extends the
transaction boundary outside the server, which can also result in
blocking and performance issues as locks are held longer before they
are released. Explicit transactions inside of stored procedures gives
you encapsulation and security benefits as well, providing another
layer between your client code and the data.

--Mary

Show quote

On 19 Dec 2006 03:17:02 -0800, "urig" <uri.goldst***@gmail.com> wrote:

>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
>SqlTransactions in
>our business logic layer to combine several data access layers method
>calls into one coherent, atomic action.
>
>My team leader says that we need to get rid of all SqlTransactions and
>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, then can you
>please give contradictory examples or links?
>
>
>Thanks!