Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that was rigorously updating a table within a cursor.

This is the first time I've seen this error and didn't know how to track and fix it effectively. I tried all the possible ways I know, and finally found that the table which is being updated doesn't have a primary key! luckily it was an identity column.

I later found the developer who scripted database for deployment messed-up. I added a primary key and the problem was solved.

I felt happy and came back to my project, and did some research to found out the reason for that deadlock...

Apparently, it was a circular wait condition that caused the deadlock. Updates apparently take longer without a primary key than with primary key.

I know it isn't a well defined conclusion, that is why I'm posting here...

  • Is the missing primary key the problem?
  • Are there any other conditions which cause deadlock other than (mutual exclusion, hold and wait, no preemption and circular wait)?
  • How do I prevent and track deadlocks?
share|improve this question
1  
IME majority (all ?) of the deadlocks I've seen happen because of circular waits ( mainly because of overzealous use of triggers). –  Sathya Jan 4 '11 at 13:26
    
Circularity is one of the necessary conditions of a deadlock. You can avoid any deadlock whatsoever if all your sessions acquire locks in the same order. –  Peter G. Mar 31 '11 at 12:09

6 Answers 6

up vote 26 down vote accepted

tracking deadlocks is the easier of the two:

By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605)

Turn it off: DBCC TRACEOFF(-1, 1204, 3605)

See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on. http://msdn.microsoft.com/library/en-us/trblsql/tr_servdatabse_5xrn.asp

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data. Where I've seen the most issues occur is where you saw yours, inside a cursor. Cursors are notoriously inefficient, avoid them if at all possible.

share|improve this answer
    
thank you, this explains alot! –  Coder Hawk Jan 5 '11 at 4:52
    
According to stackoverflow.com/a/112256/14731 you cannot prevent deadlocks, only reduce their frequency. –  Gili May 14 '13 at 21:49
    
I read that article and did not reach the conclusion that they are impossible to prevent. Only difficult. –  Jay Jul 9 '13 at 20:40

my favorite articles to read and learn about deadlocks are: Simple Talk - Track down deadlocks and SQL Server Central - Using Profiler to resolve deadlocks. They will give you samples and advices about how to handle suck a situation.

In short, to solve a current problem, I'd make the transactions involved shorter, take out the unneeded part out of them, take care of the order of the use of the objects, see what isolation level is actually needed, not read unneeded data...

But better read the articles, they will be way nicer in advices.

share|improve this answer
3  
Chichu - +1 good articles! thank you –  Coder Hawk Jan 14 '11 at 12:58

Sometimes a deadlock can be solved by adding indexing, as it allows the database to lock individual records rather than the whole table, so you reduce contention and the possibility of things getting jammed up.

For example, in InnoDB :

If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily scan many rows.

Another common solution is to turn off transactional consistency when it's not needed, or otherwise change your isolation level, for instance, a long-running job to compute statistics ... a close answer is generally enough, you don't need precise numbers, as they're changing from under you. And if it takes 30 min to complete, you don't want it stopping all other transactions on those tables.

...

As for tracking them, it depends on the database software you're using.

share|improve this answer
    
It's common courtesy to provide comments when downvoting... This is a valid answer, a select statement upgrading to a table lock and taking forever can surely cause a deadlock. –  BlackICE Jan 4 '11 at 19:42
    
thank you, btw its SQL Server. –  Coder Hawk Jan 5 '11 at 4:53
1  
MS SQLServer can also give unexpected locking behavior if the indexes are not clustered. It will silently ignore your direction to use row level locking and will do page level locking. You can then get deadlocks waiting on the page. –  Jay Jul 9 '13 at 20:43

Missing a primary key is not the problem. At least by itself. First, you don't need a primary to have indexes. Second, even if you are doing table scans (which has to happen if your particular query isn't using an index, a table lock will not by itself cause a deadlock. A write process would wait for a read, and a read process would wait for a write, and of course reads wouldn't have to wait for each other at all.

Adding to the other answers, The transaction isolation level matters, because repeatable read and serialized are what cause 'read' locks to be held until the end of the transaction. Locking a resource does not cause a deadlock. Keeping it locked does. Write operations always keep their resource locked until the end of the transaction.

My favorite lock prevention strategy is using the 'snapshot' features. The Read Committed Snapshot feature means that reads do not use locks! And if you need more control than 'Read committed', there is the 'Snap shot isolation level' feature. This one allows a serialized (using MS terms here) transaction to occur while not blocking the other players.

Lastly, one class of deadlocks can be prevented by using an update lock. If you read and hold the read (HOLD, or using Repeatable Read), and another process does the same, then both try to update the same records, you will have a deadlock. But if both request an update lock, the second process will wait for the first, while allowing other processes to read the data using shared locks until data is actually written. This of course won't work if one of the processes still requests a shared HOLD lock.

share|improve this answer

Just to develop on the cursor thing. it is indeed really bad. It locks the whole table then processes the rows one by one.

It's best to go through rows in the fashion of a cursor using a while loop

In the while loop, a select will be performed for each rows in the loop and the lock will occur on only one row at the time. The rest of the data in the table is free for querying, thereby reducing the chances of deadlock happening.

Plus it's faster. Makes you wonder why there are cursors anyway.

Here's an example of this kind of structure:

DECLARE @LastID INT = (SELECT MAX(ID) FROM Tbl)
DECLARE @ID     INT = (SELECT MIN(ID) FROM Tbl)
WHILE @ID <= @LastID
    BEGIN
    IF EXISTS (SELECT * FROM Tbl WHERE ID = @ID)
        BEGIN
        -- Do something to this row of the table
        END

    SET @ID += 1  -- Don't forget this part!
    END

If your ID field is sparse, you may want to pull a separate list of IDs and iterate through that:

DECLARE @IDs TABLE
    (
    Seq INT NOT NULL IDENTITY PRIMARY KEY,
    ID  INT NOT NULL
    )
INSERT INTO @IDs (ID)
    SELECT ID
    FROM Tbl
    WHERE 1=1  -- Criteria here

DECLARE @Rec     INT = 1
DECLARE @NumRecs INT = (SELECT MAX(Seq) FROM @IDs)
DECLARE @ID      INT
WHILE @Rec <= @NumRecs
    BEGIN
    SET @ID = (SELECT ID FROM @IDs WHERE Seq = @Seq)

    -- Do something to this row of the table

    SET @Seq += 1  -- Don't forget this part!
    END
share|improve this answer

While cursors are slow in SQL Server, you can avoid deadlocking in a cursor by pulling the source data for the cursor into a Temp table and running the cursor on it. This keeps the cursor from locking the actual data table and the only locks you get are for the updates or inserts performed inside the cursor which are only held for the duration of the insert/update and not for the duration of the cursor.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.