Deadlocks

Hi,

I do have an enquiry. We are running on SQL 2000, but we faced a lot of deadlock issues. What are the features that is avaliable in SQL 2000 to reslove this problem or what will be a good suggestion to reslove this deadlock issues.

Thank you in advance.

Kelvin

The following text is from someone at microsoft that was posted in the newsgroup microsoft.public.sqlserver.programming. It is VERY good advice:

"Some ways to avoid Deadlocks are as follows:
1. Make sure the order of tables being accessed in all the transactions
are the same.Say a programmer writes a transaction that modifies the
Products table and then the Customers table.If another programmer writes a
second transaction that accesses the same tables in the reverse order, the
chance of a deadlock is greater. Make sure that the order is always
remained same.
2. Avoid user interaction in transactions, and keep transactions short and
in a single batch.
3. As far as possble, do not use lock hints to induce more locking than
necessary.
4. Know that the transaction Isolation Level has a lot of impact on the way
locking is done in SQL Server in that database and that obviously has
effect in deadlocks. Make sure you are using the lowest Isolation level as
much as possible.
5. Use update locks when applicable. This reduces Conversion Deadlocks.
You can explicitly ask for an update lock by using the (UPDLOCK) hint in a
SELECT statement.
SELECT AccountBalance FROM Customers WITH (UPDLOCK) WHERE
Customer = ?
Bob?
6. Create clustered index to distribute inserts across the entire table.
This ensures that the inserts are not happening on aht last page ONLY.
7. Avoid placing clustered index on columns that are frequently updated.
Updates to clustered index key columns will require locks on the
clustered index, data pages (to move the row), and all nonclustered indexes
(since NC indexes point to rows via the clustered index key).
8. Capture error 1205 and make sure you resubmit the transaction as a
general practice in your Application code.
9. Use bound connections where appropriate (see sp_getbindtoken in BOL).
Any locks acquired by the bound connections are held as if they were
acquired by the primary connection, and vice versa, and therefore do not
block each other. This may be used to avoid distributed deadlocks, which
occur when the circular blocking chain extends to resources outside of SQL
Server.

Special Considerations for Deadlocks when using MTS components:
An MTS application runs its transactions with a default isolation level of
Serializable when connected to SQL Server.
MTS transactions have a timeout of 60 seconds. If a transaction cannot be
completed ( a deadlock occured the MTS component would not know that it
occured and if a deadlock did not occur and there was some kind of generic
blocking going on ) within that time, the DTC assumes that a deadlock
situation has occurred. In either case, your code must be ready to deal
with deadlock errors when they arise.
You can get the same effect ( as requesting an Update Lock ) with SQL Server
by using a special type of ADO cursor. That is, if you open an ADO
recordset on a table using a pessimistic server-side cursor, SQL Server
will also acquire an update lock instead of a read lock. This shows how
to eliminate deadlocks that are caused by lock conversion."