ADO.Net and transaction locking

[Robert Strickland]

Earlier today I was in a discussion with the team's DBA on where explicit
database transaction locks should be placed - in the .Net application or in
the stored procedures. This can up when we ran into some concurrency
problems for our web application. Is here any guidelines or standards on
where the best place is for explicit locks? We use SQL Server 2000 and 2005
only (no Oracle and MySql). However, we do have several databases that make
up the datasource for the applications. All of the T-SQL code is in stored
procedures.

Also, with the CLR now in SQL Server 2005, is there push to move business
code from the business layer directly into SQL Server?

Thanks
Bob

 

[Mary Chipman [MSFT]]

As a general rule, it's always better to have explicit transactions
executing on the server, not from the client. You have more control
using stored procedures (error handling, wait queues, etc) than you do
from the client where more round trips are involved. As far as pushing
business code to the server, that's not what the CLR was designed for.
It's useful for procedural code that would otherwise require
non-performant server-side cursors or the like. T-SQL was never a true
programming language, and the CLR fills in the gaps.

-mary

 

[Robert Strickland]

Thanks

As a general rule, it's always better to have explicit transactions
executing on the server, not from the client. You have more control
using stored procedures (error handling, wait queues, etc) than you do
from the client where more round trips are involved. As far as pushing
business code to the server, that's not what the CLR was designed for.
It's useful for procedural code that would otherwise require
non-performant server-side cursors or the like. T-SQL was never a true
programming language, and the CLR fills in the gaps.

-mary