Reduce deadlock on PAGE level on update query on MS SQL

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

Column PK is 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                  

Answers


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.


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.


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...


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.


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


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.


Need Your Help

Exception in thread "main" java.lang.NullPointerException (15-Puzzle)

java algorithm artificial-intelligence puzzle

First of all, this question is about 15-Puzzle. And I'm trying to learning how to code it in Java language.

How to simulate mobile app slide view with CSS and AngularJS properly?

css angularjs

Here is what I've done after researching and changing: