Transactions Locks «Prev  Next»

Lesson 8 Avoiding deadlocks
Objective State how to avoid deadlocks.

Avoiding SQL-Server Deadlocks

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

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:

Identify the cause of a deadlockw

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.