Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process.

Most well-designed applications, after receiving this message, will resubmit the transaction, which most likely can now run successfully. This process, if it happens often on your server, can drag down performance. Here are some tips on how to avoid deadlocking on your SQL Server:

• Have the application access server objects in the same order each time.
• During transactions, don’t allow any user input. Collect it before the transaction begins.
• Keep transactions short and within a single batch.
• If appropriate, use as low of an isolation level as possible for the user connection running the transaction.  [6.5, 7.0, 2000]

*****

To help identify deadlock problems, use the SQL Server Profiler’s Create Trace Wizard to run the “Identify The Cause of a Deadlock” trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases.  [7.0, 2000]

*****

If you are unable to eliminate all deadlocks in your application, be sure to include program logic that will automatically resubmit a transaction after a random about of time after a deadlock appears and terminates a user transaction. It is important that there is a random waiting period because it is possible that another contending transaction could also be waiting, and you don’t want both contending transactions to wait the same amount of time and then both try to execute at the same time, causing another deadlock.  [6.5, 7.0, 2000]

*****

Keep All Transact-SQL transactions as short as possible. This helps to reduce the number of locks (of all types), helping to speed up the overall performance of your SQL Server application. If practical, you may want to break down long transactions into groups of smaller transactions.  [6.5, 7.0, 2000]

*****

Any conditional logic, variable assignment, and other related preliminary setup should be done outside of transactions, not inside them. Don’t ever pause a transaction to wait for user input. User input should always be done outside of a transaction.  [6.5, 7.0, 2000]

*****

Encapsulate all transactions within stored procedures, including both the BEGIN TRANSACTION and COMMIT TRANSACTION statements in the procedure. This provides two benefits that help to reduce blocking locks. First, it limits the client application and SQL Server to communications before and after when the transaction runs, thus forcing any messages between them to occur at a time other than when the transaction is running (reducing transaction time).  Second, It prevents the user from leaving an open transaction (holding locks open) because the stored procedure forces any transactions that it starts to complete or abort.  [6.5, 7.0, 2000]

*****

If you have a client application that needs to “check-out” data for awhile, then perhaps update it later, or maybe not, you don’t want the records locked during the entire time the record is being viewed. Assuming “viewing” the data is much more common that “updating” the data, then one way to handle this particular circumstance is to have the application select the record (not using UPDATE, which will put a share lock on the record) and send it to the client.

If the user just “views” the record and never updates it, then nothing has to be done. But if the user decides to update the record, then the application can perform an UPDATE by adding a WHERE clause that checks to see whether the values in the current data are the same as those that were retrieved.

Similarly, you can check a timestamp column in the record, if it exists. If the data is the same, the the UPDATE can be made. If the record has changed, then the application must include code to notify the user so he or she can decide how to proceed. While this requires extra coding, it reduces locking and can increase overall application performance.  [6.5, 7.0, 2000]

*****

Use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED. In order to do this without causing other problems, the nature of the transaction must be carefully analyzed as to what the effect of a different isolation will be.  [6.5, 7.0, 2000] More information from Microsoft

*****

Using cursors can reduce concurrency. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which can increase locking problems. [6.5, 7.0, 2000]

*****

If your users are complaining that they have to wait for their transactions to complete, you may want to find out if object locking on the server is contributing to this problem. To do this, use the SQL Server Locks Object: Average Wait Time (ms). You can use this counter to measure the average wait time of a variety of locks, including: database, extent, Key, Page, RID, and table.

If you can identify one or more types of locks causing transaction delays, then you will want to investigate further to see if you can identify what specific transactions are causing the locking. The Profiler is the best tool for this detailed analysis. [7.0, 2000]

*****

Use sp_who and sp_who2 (this stored procedure is not documented in the SQL Server Books Online, but offers more detail than sp_who) to identify which users may be blocking what other users. [6.5, 7.0, 2000] More info from Microsoft.

*****

Try one or more of the following suggestions to help avoid blocking locks: 1) Use clustered indexes on heavily used tables; 2) Try to avoid Transact-SQL statements that affect large numbers of rows at once, especially the INSERT and UPDATE statements; 3) Try to have your UPDATE and DELETE statements use an index; and 4) When using nested transactions, avoid commit and rollback conflicts. [6.5, 7.0, 2000]