Eu tenho dois procedimentos armazenados que estão travando quando o sistema está sob carga. O Proc A está selecionando em uma tabela enquanto o Proc B está inserindo na mesma tabela. O gráfico de bloqueio está mostrando que o Proc A tem um bloqueio de página no modo S para o qual o Proc B deseja um bloqueio no modo IX, no entanto, o Proc A está aguardando um bloqueio de página no modo S para uma página diferente em que o Proc B já possui um bloqueio de página no modo IX .
Claramente, isso pode ser resolvido garantindo que ambas as consultas bloqueiem as páginas na tabela na mesma ordem, mas não consigo descobrir como fazer isso.
Minha pergunta é: como o SQL Server determina em que ordem bloquear as páginas ao fazer INSERTs e SELECTs e como você pode modificar esse comportamento?
Indeterminado - feito por processamento interno e dependendo da saída do otimizador de consulta.
Controle seu isolamento. Se você ler para escrever, diga ao SQL Server para obter imediatamente um bloqueio de gravação. Ponto fechado.
Atualmente, o proc A possui um bloqueio compartilhado.
"Nenhuma outra transação pode modificar os dados enquanto houver bloqueios compartilhados (S) no recurso." http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx
Se você estiver no sql 2005 ou superior, tente usar o isolamento de instantâneo.
"O nível de isolamento de instantâneo usa controle de versão de linha para fornecer consistência de leitura em nível de transação. Operações de leitura não adquirem bloqueios de página ou linha; apenas bloqueios de tabela SCH-S são adquiridos. Ao ler linhas modificadas por outra transação, elas recuperam a versão da linha que existia quando a transação foi iniciada. Você só pode usar o isolamento de instantâneo em um banco de dados quando a opção de banco de dados ALLOW_SNAPSHOT_ISOLATION estiver ativada. Por padrão, esta opção está desativada para bancos de dados do usuário." http://msdn.microsoft.com/en-us/library/ms189122.aspx
Como apenas os bloqueios Sch-S são adquiridos, sua leitura não deve ser capaz de bloquear sua gravação.
"Bloqueios de estabilidade de esquema (Sch-S) não bloqueiam nenhum bloqueio transacional, incluindo bloqueios exclusivos (X)." http://msdn.microsoft.com/en-us/library/ms189122.aspx
Lembre-se de que o nível de isolamento de instantâneo faz uso intenso de tempdb para controle de versão de linha, portanto, dimensione-o adequadamente e siga as práticas recomendadas para estratégias de disco tempdb.