Introduction

Deadlocks can occur in any database that allows users to view and modify data. You will always get a dead lock when you have two processes each having an exclusive lock on an object for which the other process requests an exclusive lock and neither process will release its exclusive lock until it gets an exclusive lock on the other object. In these cases SQL Server chooses one of the processes to kill (usually the process that will cost the least to rollback) and gives the following error:

Your transaction (process ID #32) was deadlocked with another process and has been chosen as the deadlock victim. Rerun your transaction.

Almost all deadlocks can be avoided with careful planning. In this article I plan to demonstrate deadlocks and some ways to avoid them. I do not plan to go in depth. For more information you can search for "locking, deadlocks" in SQL Servers BOL. There you will find many options to learn more about deadlocks.

Tables For Examples

CREATE TABLE [dbo].[Components] (
            [Iden] [int] IDENTITY (1, 1) NOT NULL ,
            [ComponentName] [varchar] (25) NULL ,
            [SerialNumber] [varchar] (25) NULL ,
            [NeedsMaint] [bit] NULL ,
            [Comments]
[varchar] (75) NULL ,
            [UserName] [varchar] (50) NULL ,
            [UpdateDate] [datetime]
NULL ,
            [UpdatedBy] [varchar] (35) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ComponentsMaint] (
            [Iden] [int] IDENTITY (1, 1) NOT NULL ,
            [ComponentName] [varchar] (25) NULL ,
            [SerialNumber] [varchar] (25) NULL ,
            [MaintCompleted] [bit] NULL ,
            [Comments]
[varchar] (75) NULL ,
            [UserName] [varchar] (50) NULL ,
            [UpdateDate] [datetime] NULL ,
            [UpdatedBy] [varchar] (35) NULL
) ON [PRIMARY]
GO
INSERT INTO Components (ComponentName, SerialNumber)
SELECT 'Monitor','test2001'
INSERT INTO ComponentsMaint (ComponentName, SerialNumber)
SELECT 'Monitor','test2001'

Creating A Deadlock

What I am about to explain will almost always (if not always) produce a deadlock. After running the above script to create each table and add one row to each you will need to open two windows in Query Analyzer and use the database where you created the tables. Paste the following code into the first window:

BEGIN TRANSACTION
UPDATE ComponentsMaint SET MaintCompleted = 0
WAITFOR DELAY '0:0:10'
UPDATE Components SET NeedsMaint =
1

Paste the following code into the second window:

BEGIN TRANSACTION
UPDATE Components SET NeedsMaint =
1
WAITFOR DELAY '0:0:10'
UPDATE ComponentsMaint SET MaintCompleted = 0
COMMIT TRANSACTION

Before executing these you’ll notice that the only

difference is which table gets updated first. The WAITFOR DELAY command tells SQL Server to wait 10 seconds before executing the second update. For the purposes of demonstrating a deadlock, this gives you enough time to start one transaction and then start the other and it gives SQL Server enough time to get the exclusive lock it needs to keep it from completing each transaction and thus causes SQL Server to kill one and issue the deadlock victim error.

Execute the code in the first window. Switch to the second window and start the execution there. In about 15 to 30 seconds you should get a deadlock error in one window and the other window will complete its execution successfully.

Next, I wanted to see the deadlock in progress. I increased the wait time to 15 seconds to make sure I could catch the deadlock before SQL Server killed one of the processes. I waited until about 12 seconds had passed and then switched to Enterprise Manager and refreshed the Current Activity folder. Then I went and looked at Locks / Process ID. I saw that SPID 32 was blocked by SPID 33 and SPID 33 was blocked by SPID 32. I did it a few more times. Sometimes I would see one SPID blocking the other and after refreshing again I would see each SPID blocking the other SPID.

I hope this gives you a better idea of what causes a deadlock to occur. I doubt most people put a WAITFOR DELAY command in their stored procedures. However if you have a stored procedure or query that updates table1 and then table2 and another that updates table2 and then table1 then you are asking for a deadlock. I am sure there are other ways deadlocks happen. I used the above example because it was the simplest to develop.

Avoiding Deadlocks

Too many deadlocks are a problem. The less you have the better your SQL Server will perform. Each deadlock has the potential to give more overhead as SQL Server must rollback the transactions for each process it kills due to a deadlock. Afterwards SQL Server often must reprocess the same transaction again as a user will resubmit because they didn’t get the results they needed the first time.

One of the best ways to avoid deadlock errors is to ensure that updates to tables are done in the same order all the time. To check this copy the following code and paste it into both Query Analyzer windows:

BEGIN TRANSACTION
UPDATE Components SET NeedsMaint =
1
WAITFOR DELAY '0:0:10'
UPDATE ComponentsMaint SET MaintCompleted = 0
COMMIT TRANSACTION

Now execute both of them. If you refresh your Current Activity folder you will now find that one transaction will always block the other. After about 20 to 30 seconds the last transaction will finish processing. No deadlocks will occur.

Other ways to avoid deadlocks is to access objects in the same order. Don’t have transactions wait for users to enter information. Let them enter the information first and then run the query or stored procedure passing needed info in as variables. Also, the less time a transaction takes to complete the less time available for it to deadlock with another transaction. So, keeping your transactions as short as possible helps.

Conclusions

This article in no way covers everything related to deadlocks and does not necessarily mention everything you can do to avoid deadlocks. From this article you should be able to see the effects of a deadlock and see how a deadlock looks in Enterprise Manager. You should also know some ways you can avoid deadlocks.