Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Exploring Deadlocks

By Robert Marda,

Exploring Deadlocks

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

 

COMMIT TRANSACTION

 

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.

Total article views: 8162 | Views in the last 30 days: 8
 
Related Articles
FORUM

DeadLock

DeadLock

FORUM

Transaction (Process ID 107) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Transaction (Process ID 107) was deadlocked on lock resources with another process and has been chos...

FORUM

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chose...

FORUM

Update Deadlock

Deadlock is reached when executing a simple query of the form of "update table where column = @param...

FORUM

deadlock

deadlock

Tags
administration    
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones