title: Reduce deadlock on PAGE level on update query on MS SQL
body: <p>I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction.</p>
  
  <pre><code>UPDATE table SET column=@P1 WHERE PK=@P2
  </code></pre>
  
  <p>Column <code>PK</code> is <code>varchar(11)</code>, has a clustered index on it.
  no trigger or table relation..etc on the table.</p>
  
  <p>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)</p>
  
  <p>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?</p>
  
  <p>--</p>
  
  <p>Thanks for your suggestion.</p>
  
  <p>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.</p>
  
  <p>--</p>
  
  <p>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.</p>
  
  <pre><code>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                  
  </code></pre>