Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 315

Finally, another option is to use the READ_COMMITTED_SNAPSHOT database option or the
SNAPSHOT isolation level. These two options can limit the amount of blocking that occurs as well.
The following techniques apply only to deadlocks, not blocking.
SettingaDeadlockPriority
As stated earlier, SQL Server will choose a deadlock victim based automatically. However, you have
some control over this by specifying a deadlock priority for a given user connection. This is accomplished
by using the SET DEADLOCK_PRIORITY T-SQL statement. Using this statement you can set the deadlock
priority to one of three values. Those are: LOW, NORMAL, and HIGH. Within each of those settings you
can further set a priority, which ranges from -10 to 10.
TrappingforDeadlock
A new technique available in SQL Server 2005 is to trap a deadlock error and retry the process. This
is accomplished by using the TRY /CATCH feature of SQL Server 2005. Below is a T-SQL template
example that demonstrates the structure and syntax of this approach. Using this technique, the SQL
statements defined in the DML section will be attempted three times before giving up. In the CATCH
block is a check to see if the error generated is a deadlock error. If it is, the DeadlockCounter variable
is updated.
-- Declare and set variable
-- to track number of retries
-- to attempt before exiting.
DECLARE @DeadlockCount TINYINT
SET @DeadlockCount = 1
WHILE (@DeadlockCount < =3)
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--DML statement(s)...
--Note that if the statements above succeed
--without error then this script will execute
--the following statements. Otherwise, control
--will jump into the CATCH section, and these
--statements will not execute.
SET @DeadlockCount = 99
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Deadlock error number is 1205.
IF (ERROR_NUMBER() = 1205)
BEGIN
SET @DeadlockCount = @DeadlockCount + 1
END
ROLLBACK TRANSACTION
END CATCH
END -- end while loop
Locking and blocking can be avoided with careful coding and adequate testing procedures, but
sometimes you want to know what would happen if your database size doubled and only certain aspects