SqlTransactions vs. transactions in the SQL code

[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!

 

[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