Avoiding SQL-Server Deadlocks

Some general techniques to avoid deadlocks in SQL Server.

1. Minimize Lock Duration: Keep transactions as short as possible to minimize the duration of locks and avoid lock contention. One common way to achieve this is to perform as many calculations and operations as possible before beginning the transaction, then start the transaction, execute the SQL operations quickly, and commit the transaction immediately.
2. Access Objects in the Same Order: Deadlocks often occur when two processes lock resources in a different order. If all processes access objects in a consistent order, you can reduce the likelihood of a deadlock.
3. Use Bound Connections: Bound connections can be used to group two or more connections together, so all tasks operating on the bound connections are accomplished in one transaction.
4. Use Lower Isolation Levels: Using the READ COMMITTED isolation level can help reduce deadlocks. Be aware, though, that using lower isolation levels can lead to issues such as dirty reads, non-repeatable reads, and phantom reads.
5. Use the NOLOCK or READPAST Table Hints: If a read operation doesn't need to be part of a transaction, you can use the NOLOCK hint to avoid locking altogether. Alternatively, use the READPAST hint to avoid reading rows with exclusive locks.
6. Avoid Cursors: Cursors lock resources for longer periods and are more likely to cause deadlocks. Whenever possible, use set-based operations instead of cursors.
7. Use Optimistic Concurrency: By using the ROWVERSION (previously TIMESTAMP) data type, you can determine whether a row has changed since it was last read. This approach avoids locking because you're only checking whether the row has changed when you try to update it.
8. Avoid User Interaction in Transactions: Transactions should not be designed in a way that user input is required for their completion. This is because the time spent waiting for user input is time during which locks are held, leading to potential deadlocks.
9. Use Query Tuning: Avoid complex, monolithic queries that can hold many locks for a long time. Instead, break up large queries and transactions into smaller ones, so locks are held for shorter periods.
10. Use Database Design Techniques: Normalize your database to reduce data redundancy and ensure data integrity. This reduces the chance of lock contention and potential deadlocks.

Remember, the aim is to write efficient and effective SQL code, so the database engine doesn't have to spend extra time resolving locks and deadlocks. And always test changes in a controlled environment before rolling them out to production.

Of course, the best solution of all is to avoid deadlocks.
You can avoid a deadlock by using the following guidelines:

1. Use as low of an isolation level as possible. Isolation levels are discussed in an earlier lesson. Lower isolation levels decrease the possibility of deadlocks, but increase the possibility of lost data. That is why MS SQL Server 2000 uses an isolation level of Read Committed.
2. Do not require any type of user interaction with a user inside of your transactions. If you request interaction from a user within your transactions, the system will be paused waiting for a user to respond. This could cause a deadlock timeout to occur.
3. Keep your transactions short. The longer the transaction, the more chances there are for deadlocks.
4. Keep your transactions within one batch, if possible. The more batches you have, the more network traffic you will have. This increases the possibility for a deadlock timeout.
5. If you have more than one connection to the server within a single application, use a bound connection. This allows for two or more connections to share transactions and locks.

Troubleshooting deadlocks can be done using the SQL Server Profiler. The Profiler comes with a very helpful wizard, called the Create Trace Wizard. Once you run the Profiler, choose the Tools->Create Trace Wizard. There are lots of types of traces that can be chosen, but the subject of this lesson is to discuss identifying the cause of a deadlock. Therefore, one of the steps in the wizard is to select the problem you are having. Choose Identify the cause of a deadlock, as shown in the following illustration:

When you complete the rest of the steps in the wizard, like selecting the database(s) to monitor, the trace will be run. When deadlocks occur, you will see the process ID, user name, server name, database, and other information. This will be useful in helping you troubleshoot deadlocks. One other thing that could help you to determine deadlocks is calling the system stored procedure sp_who. This report will show all processes that are connected to SQL Server, as well as whether a process is blocked. This can be useful in troubleshooting deadlocks. The next lesson shows you how to create transactions.