Deadlocks in SQL Server 2000 and how to prevent them.

I did some research on deadlocks and how to prevent deadlocks. Below are the ways where we can detect, log and eliminate deadlocks.

1. Elimation Method: we can use deadlock priority configuration
put SET DEADLOCK_PRIORITY LOW and SET LOCK_TIMEOUT in those long running sql queries, these will be "victims" in a deadlock, then these will be sacrificed in times of deadlock.
as describe in http://www.mssqltips.com/tip.asp?tip=1210
and http://msdn2.microsoft.com/en-us/library/aa213032(SQL.80).aspx

2. Detecting Deadlocks: see SQL Server 2000 - Locking Related Objects in
http://www.mssqltips.com/tip.asp?tip=1359
Using sql profiler to trace deadlogs
http://www.mssqltips.com/tip.asp?tip=1036

3.
Logging Deadlocks: we can use XML or the graph method.
Capture deadlock information in XML and graphs
http://www.mssqltips.com/tip.asp?tip=1234

Published Wednesday, December 12, 2007 12:31 PM by darenhan

DarenHan's Blog