Reduce deadlock on PAGE level on update query on MS SQL

Question

I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction.

UPDATE table SET column=@P1 WHERE PK=@P2; While PK varchar(11), has a clustered index on it. no trigger or table relation..etc on the table.

I did some check and find that the deadlock happen on "PAGE" level, not at ROW/record level. Then, I find that for each update query, it does take 100(and more) PAGE locks. (It does not make sense to me because I am updating one row at once)

Is there any way to prevent deadlock being happen? Or, how can reduce the number of locks it takes for one single row update without using cursor?

--

Thanks for your suggestion.

I had tried to rebuild the index a few times, with high and low fill factor. I had tried to make processes update different position/slice. But nothing got improved or worst.

--

I tried the SQL Server Profiler. I captued some "Lock:Deadlock Chain" and "Lock:Deadlock", but no "Deadlock Graph" was captured. Both side are doing the simple update query in read commited, auto-commit mode.

Lock:Deadlock Chain 17887475 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange        Lock:Deadlock Chain 17887476 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:438102                                                                                                                                                                                                                                                        265006271  0 0X56AF060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887477 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange        Lock:Deadlock Chain 17887478 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 54 1:426206                                                                                                                                                                                                                                                        265006240  0 0XDE80060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887479 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:426206                                                                                                                                                                                                                                                        265006271  0 0XDE80060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887480 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange        Lock:Deadlock Chain 17887481 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 54 1:426066                                                                                                                                                                                                                                                        265006240  0 0X5280060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887482 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:426066                                                                                                                                                                                                                                                        265006271  0 0X5280060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887483 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange        Lock:Deadlock Chain 17887484 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:425614                                                                                                                                                                                                                                                        265006271  0 0X8E7E060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887485 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange        Lock:Deadlock Chain 17887486 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:426687                                                                                                                                                                                                                                                        265006271  0 0XBF82060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887487 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange    
Lock:Deadlock Chain 17887488 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:425392                                                                                                                                                                                                                                                        265006271  0 0XB07D060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887489 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange    
Lock:Deadlock Chain 17887491 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange    
Lock:Deadlock Chain 17887493 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange    
Lock:Deadlock Chain 17887494 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:435792                                                                                                                                                                                                                                                        265006271  0 0X50A6060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock Chain 17887495 1  0X01 4 myserver  2008-11-28 10:16:46.210 Parallel query worker thread was involved in a deadlock                 0   971497 102 - Resource type Exchange    
Lock:Deadlock Chain 17887496 1  0X01 4 myserver  2008-11-28 10:16:46.210 Deadlock Chain SPID = 209 1:438206                                                                                                                                                                                                                                                        265006271  0 0XBEAF060001000000000000001B0006  27    0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971497 101 - Resource type Lock    
Lock:Deadlock   17887497  myuser 0XCD85FBB269700B4AA2F4E8579D118999 209 myserver myuser 2008-11-28 10:16:45.930 1:426206 265006271 myapps 0 0XDE80060001000000000000001B0006 123 27 281 2008-11-28 10:16:46.210 myclient 0 - LOCK 4 - U   0 72057594040352768 1 - TRANSACTION 0 6 - PAGE mydatabase 971498

Answer 1

You have 2 options to reduce the lock escalation:

1) add the WITH (ROWLOCK) hint to ask sql server to take finer granularity locks (your mileage may vary:

UPDATE table WITH (ROWLOCK) SET column=@P1 WHERE PK=@P2; While PK varchar(11), has a clustered index on it. no tigger or table relation..etc on the table.

2) update rows in a random order, which reduce the likelihood of row locks being escalated to page locks.

Also, ensuring that the indexes on that table are up to date can often reduce locking. As can leaving a Fill factor (90 is good) if you are going to be doing lots of inserts.

Answer 2

In the normal, simple case this type of begavior is not often seen. My question to you is this : what's on the 'other side' of this transaction? What's the other update statement that is running and causing this deadlock? That, I think, will be the key to diagnosing this problem. Honestly, my money is on this other, heretofore unidentified query being the culprit. And I'm in vegas now...

Answer 3

Have you run a profile trace?

Fire up SQL Profiler and create a standard trace with these events added:

• Locks:Deadlock Graph
• Locks:Lock:Deadlock Chain
• Locks:Lock:Escalation

Should should provide details of the precise nature of the deadlock.

Answer 4

What select statements from same table AND same records do occur inside the same transaction before update statement? Use (updlock) locking hints in these selects.

Answer 5

do you have any UPDATE TRIGGERS on the table? If so, the action of the trigger, might be causing your deadlock.

Answer 6

I finally have to do a workaround by using cusror in a stored procedure.

But it is still interesting that how the PAGE lock happen and how to resolve.

---

After some more search on Google, there are some other people have the same problem and they(from MSDN forum) suggest to turn off the parallelism in SQL Server 2005 but I never get a chance to try.