Interestingly, I found this earlier this week:
http://blogs.msdn.com/psssql/archive/2009/10/26/reduce-locking-and-other-needs-when-updating-data-better-performance.aspx==================================
The following pattern typically stems from an old practice used in SQL 4.x and 6.x days, before IDENTITY was introduced.
begin tran
declare @iVal int
select @iVal = iVal from CounterTable (HOLDLOCK) where CounterName = 'CaseNumber'
update CounterTable
set iVal = @iVal + 1
where CounterName = 'CaseNumber'
commit tran
return @iVal
This can be a dangerous construct. Assume that the query is cancelled (attention) right after the select. SQL Server treats this as a batch termination and does not execute other statements. The application now holds a lock under the open transaction and without proper handling it leads to blocking.
One Statement Fix
declare @iVal int
update CounterTable
set @iVal = iVal = iVal + 1
where CounterName = 'CaseNumber'
return @iVal
SQL Server allows you to update a value and return the updated value into a variable. A single update statement can do all the work. If the query is cancelled by the application the update is atomic and will complete or rollback but the application has much better control over the lock and transaction scope.
Use this to help reduce blocking and possible deadlock issues. Since the lookup only takes place one time it can also increase performance of you application.
Topic: MSSQL Deadlocks (Read 5775 times)