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 a S mode page lock that the 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?