Comportamento de bloqueio de leitura do SQL Server

Question

Quando o SQL Server Books online diz que "bloqueios Shared (S) em um recurso são liberados assim que a operação de leitura é concluída , a menos que o nível de isolamento da transação esteja definido para leitura repetível ou superior, ou uma dica de bloqueio é usada para manter os bloqueios compartilhados (S) pela duração da transação. "

Supondo que estamos falando de um bloqueio em nível de linha, sem nenhuma transação explícita, no nível de isolamento padrão (Read Committed), o que " operação de leitura " se refere?

• A leitura de uma única linha de dados?
• A leitura de uma única página 8k IO?
• ou até o completo Select declaração em que o bloqueio foi criado terminou a execução, não importa quantas outras linhas são envolvido?

OBSERVAÇÃO: O motivo pelo qual preciso saber disso é que temos uma segunda instrução seletiva somente leitura gerada por um serviço da Web da camada de dados, que cria bloqueios de leitura compartilhados no nível da página, gerando um impasse devido ao conflito com o nível de linha. bloqueios de atualização exclusivos de um processo de replicação que mantém o servidor atualizado. A instrução select é razoavelmente grande, com muitas sub-seleções, e um DBA está propondo que nós a reescrevamos para dividi-la em várias instruções menores (partes menores em execução), "para reduzir o tempo de retenção dos bloqueios". Como isso pressupõe que os bloqueios de leitura compartilhados são mantidos até que a instrução select completa seja concluída, se isso estiver errado (se os bloqueios forem liberados quando a linha ou a página for lida), essa abordagem não terá efeito algum. / p>     

Answer 1

Infelizmente, o comportamento é bastante complexo e não documentado publicamente. Depende da declaração que está sendo executada, e alguns planos de execução usarão uma estratégia de aquisição / liberação de bloqueio que outros usarão outra. mas, como regra geral, os bloqueios S e IS são mantidos por um curto período de tempo e durante a execução da instrução, os bloqueios S podem ser adquiridos e liberados a uma taxa bastante frenética. Os bloqueios compartilhados são mantidos por muito tempo (durante a transação) somente nos níveis de isolamento de leitura repetível e leitura Searializable.

Sua melhor ferramenta para investigação é o Profiler, desde o Bloqueio: Adquirido e Bloqueio: Lançados os eventos são rastreados com todos os detalhes necessários. Os XEvents também podem ser usados, mas são um pouco mais complicados para fazê-los funcionar.

No entanto, se a contenção de bloqueio for um problema, uma solução trivial é ativar o isolamento de instantâneo de leitura confirmada. Quando isso é ativado, o Read Committed lê não mais adquirir qualquer bloqueio e obtém os dados bloqueados do armazenamento de versão no tempdb. A desvantagem é, obviamente, que o armazenamento de versão precisa ser mantido e ele adiciona a carga de trabalho ao tempdb.

    

Answer 2

"Como isto assume que os bloqueios de leitura compartilhados são mantidos até que a instrução select completa tenha terminado"

A primeira linha da página do documento diz: "O Microsoft® SQL Server ™ 2000 possui um bloqueio multigranular que permite que tipos diferentes de recursos sejam bloqueados por uma transação." ( link )

Portanto, os recursos parecem estar bloqueados por transação - sua consulta de seleção grande com as várias subconsultas seria uma transação.

Além disso, alguém poderia pensar que uma instrução select colocaria bloqueios em todos os objetos envolvidos de uma só vez, para que um ponto confiável na leitura do tempo fosse feito.

    

Answer 3

Isso significa que o bloqueio pode ser liberado antes que a declaração ou transação seja concluída.