How does SQL Server determine the order it takes locks in while selecting a table? I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc A has an S mode page lock that Proc B wants an IX mode lock for, Proc A however is waiting for an S mode page lock for a different page that Proc B already has a IX mode page lock on.

Clearly this could be sorted out by ensuring that both the queries lock pages in the table in the same order, but I can't figure out how to do that.

My question is: How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs and how can you modify this behaviour?
How does SQL Server determine what order to lock pages in while doing INSERTs and SELECTs a


Undetermined - done by internal processing and depending on query optimizer output.


  and how can you modify this behaviour?


Control your isolation. If you read in order to write, tell SQL Server to immediately get a write lock. Point closed.