Simon Learning SQL Server

Simon Learning SQL Server

In a previous post I talked about Locking, Blocking and Isolation Levels. Neste post eu vou entrar em mais detalhes sobre os bloqueios de gama de chaves usados no nível de isolamento serializável e também demonstrar a usar o sistema.dm_tran_locks DMV para ver bloqueios existentes e também como descobrir quais recursos os bloqueios são colocados.

Fechaduras chave-alcance são usadas apenas no nível de isolamento serializável e seu objetivo é parar leituras phantom que são possíveis nos outros três níveis de isolamento de concorrência pessimista, nomeadamente leitura sem compromisso, leitura comprometida e leitura repetível. Uma leitura fantasma é quando uma transação realiza uma leitura em uma gama de dados, por exemplo, usando uma consulta com uma cláusula onde, e outra transação adiciona dados que irão satisfazer as condições da cláusula onde. Se a primeira transação realizar a mesma leitura, ela agora retornará os novos dados, resultando em dados phantom aparecendo na segunda leitura dentro da mesma transação. Os bloqueios chave-alcance são usados para evitar que os dados phantom sejam adicionados e eles são colocados nas chaves do índice que é usado para recuperar os dados. O seguinte exemplo usando a pessoa.A tabela de pessoas na base de dados AdventureWorks2012 irá demonstrar como estas chaves de gama são usadas.

A Pessoa.A tabela pessoa tem um índice IX_Person_LastName_FirstName_Middlename com a coluna LastName como a primeira coluna da chave de índice. Isto significa que qualquer consulta onde estamos filtrando no LastName deve usar este índice para recuperar os dados.

se executarmos uma consulta para devolver algumas linhas da pessoa.Pessoa filtrando em uma gama de Apelidos então nós devemos ver este índice sendo usado. Por exemplo

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

Devolve o seguinte conjunto de resultados.

 Gama de Chaves 2

e olhando para o plano de execução podemos ver que este índice é usado.

 Gama de chaves 3

agora, se definirmos o nível de isolamento para ser serializável, abrir uma transação e executar a mesma declaração selecionada, devemos ser capazes de ver quais bloqueios estão sendo mantidos.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

agora a partir de outra conexão podemos consultar o sistema DMV. dm_tran_locks para ver as fechaduras atuais que estão sendo mantidas. Só estamos interessados nas fechaduras dos recursos-chave, por isso filtramos de acordo.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

intervalo de Teclas 4

temos sete linhas reformuladas com dois valores diferentes do request_mode. O” S ” request_mode corresponde a bloqueios compartilhados colocados nas três linhas que são devolvidas. Porque estamos a usar o nível de isolamento serializável, estas Fechaduras partilhadas são mantidas até que a transacção se comprometa ou role de volta para evitar leituras não repetíveis. O” RangeS-S ” request_mode corresponde aos bloqueios de chave que foram concedidos. Mas porque é que há quatro filas? Para responder a isso, podemos usar os dados retornados do sys.dm_tran_locks e usar isso para descobrir quais recursos foram realmente bloqueados.

as colunas em que estamos interessados são resource_ associated_entity_id e resource_description. Os dados nestas colunas podem variar com base no resource_type e para mais detalhes sobre isso veja TechNet. Para os recursos-chave, o resource_associated_entity_id corresponde à partition_id do sys.partições

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

intervalo de teclas 5

o object_id devolvido corresponde à pessoa.Quadro de pessoas

SELECT object_name(1765581328)

intervalo de teclas 6

nós podemos ver que os dois valores partition_id diferentes correspondem a índices diferentes em pessoa.Pessoa

SELECT * FROM sys.indexes WHERE object_id = 1765581328

intervalo de teclas 7

o sys.a vista de partições diz-nos que as partições em que temos bloqueios são para os índices Pk_person_ Businessentityid e Ix_person_ Lastname_ Primeirame_middlename.

podemos combinar tudo isso em uma consulta para clareza.

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

intervalo de teclas 8

isto mostra-nos que três fechaduras partilhadas são colocadas nas próprias linhas (o nível das folhas do índice agrupado) e que quatro Fechaduras de gama de chaves são colocadas nas chaves dentro do índice não agrupado. Mas isto não respondeu à questão de por que temos quatro Fechaduras?

Antes de fazermos isso, vou demonstrar duas maneiras de provar que as três fechaduras partilhadas são colocadas nas linhas que foram devolvidas pela consulta original e, em seguida, usar isso para determinar quais os recursos que as quatro Fechaduras “RangeS-S” são colocados.

Em Primeiro Lugar, vamos usar os %physloc % % e% lockres % % valores juntamente com o sistema.fn_physlocformatter () função para nos dizer o arquivo, página e fenda das linhas retornadas pela consulta original. Em segundo lugar, usamos esta informação no comando de página DBCC.

só precisamos atualizar a nossa consulta original da seguinte maneira

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Intervalo Chave 9

agora podemos ver que a linha de Dennis Ware está fisicamente localizado no arquivo de dados 1, na página 2806 e no slot 3. Também podemos ver que é lock resource is (05c585e839d4).

Se olharmos para os dados retornados do sys.dm_tran_locks podemos ver que um dos bloqueios compartilhados tem um resource_description de (05c585e839d4) então, isso nos mostra que o que trava é colocada na linha que o índice clusterizado.

agora podemos executar o seguinte comando de página DBCC para realmente ver informações sobre o que está na página, mas para que isso retorne quaisquer resultados precisamos ligar a trace flag 3604.

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

existem muitas informações textuais aqui apresentadas e você pode especificar um número diferente no parâmetro formato para devolver diferentes quantidades de dados. Neste caso, o primeiro bit é o cabeçalho da página e, em seguida, depois que temos o Status de alocação. Você verá que o primeiro slot usado é o Slot 0. Há um valor de compensação que nos diz onde na página este slot começa e um comprimento valores que nos diz quanto tempo o registro é.

 Gama de Chaves 10

se descermos até encontrarmos o Slot 3, e depois passarmos pelo bloco de dados, podemos ver os dados físicos para cada coluna e confirmar que esta é a linha para Dennis Ware.

Intervalo Chave 11

Agora, se recuarmos até o final do Slot 3, podemos ver

KeyHashValue = (05c585e839d4)

combina com o resource_description em sys.dm_tran_locks e o valor retornado por %%lockres%%.Mas como fazemos algo semelhante para as fechaduras de chave? Como é que descobrimos que recursos estão a bloquear? Uma forma seria usar o sys. dm_ DB_ database_page_allocations para descobrir quais as páginas que pertencem ao índice não agrupado. Nós passamos no banco de dados_id, no object_id e no index_id do IX_Person_LastName_FirstName_Middlename index e ele irá devolver uma linha para cada página usada dentro desse índice. Podemos então executar a página DBCC com os valores alocados_page_page_id fornecidos e encontrar a página que contém Dennis Ware.

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

podemos então executar a página DBCC com o valor fornecido e encontrar a página que contém Dennis Ware.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

a coisa útil é que neste caso a página DBCC retorna os dados na página em um conjunto de resultados para que possamos encontrar rapidamente a página correta, neste caso a página 24745.

 Gama de chaves 12

agora, se usarmos os valores de resource_description para os nossos quatro bloqueios de gama de chaves em sys.dm_tran_locks podemos ver que as fechaduras” RangeS-S ” foram realmente concedidas para as linhas que contêm dados para Dennis Ware, Victoria Ware, Clay Warten e Aaron Washington. Mas Aaron Washington não está incluído no conjunto de resultados, então por que esta quarta chave está presente? É porque quando um intervalo de chaves é tomada em uma chave, ele vai evitar que quaisquer dados sejam adicionados imediatamente antes de uma chave com um bloqueio de alcance de chave. Em outras palavras, não podem ser adicionados dados entre, por exemplo, Victoria Ware e Clay Warthen, por exemplo, Jennifer Warner.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

intervalo de teclas 13

aqui podemos ver a fechadura à espera.Também significa que nenhum dado pode ser adicionado entre Timothy Ward e Aaron Washington e significa que o alcance de chave bloqueia realmente mais dados do que o que poderia satisfazer a consulta na transação aberta. Por exemplo, nós não poderíamos inserir uma linha para Simon Wartz que seria colocada entre Clay Warthen e Aaron Washington, mesmo que não seria devolvido na consulta original na transação aberta.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

intervalo de teclas 14

agora sabemos como as fechaduras de gama de chaves são usadas nós podemos trabalhar para fora que recursos têm as fechaduras de gama de chaves neles sem a necessidade de procurar através das páginas do índice não-clustered. Podemos simplesmente incluir uma dica de tabela para devolver parte dos dados que precisamos.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

Intervalo Chave 18

Como você pode ver isto nos dá o mesmo arquivo, página e dados da ranhura e o mesmo resource_description para as três linhas retornadas pela consulta. Sabemos agora que a quarta fechadura “RangeS-S” é colocada na chave do Índice imediatamente após a chave para a argila Warthen, ou seja, a slot 140. Mais uma vez podemos usar a página DBCC para ver os dados na página 25065.

agora na demonstração que acabei de passar, acontece que temos um índice não agrupado com a primeira coluna na chave de agrupamento sendo aquele que estamos usando em nossa cláusula onde. Mas o que acontece se removermos o índice não agrupado? Ou, em outras palavras, o que acontece se executarmos uma consulta contra outra filtragem de tabela em uma coluna não incluída em uma chave de índice? Para demonstrar isso facilmente, podemos apenas remover o índice não agrupado.

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

recebemos as mesmas linhas de volta como esperado, mas desta vez o servidor SQL teve que usar o índice agrupado para obter os dados.

 Gama de chaves 15

que Fechaduras temos? Desta vez não há nenhuma chave-range lock que possa ser aplicada, então uma fechadura compartilhada tem que ser colocada em toda a mesa.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

intervalo de teclas 16

como este bloqueio compartilhado é mantido até que a transação seja fechada, isso significa que nenhum dado na tabela pode ser atualizado até que a transação completa, mesmo que tenhamos lido apenas três linhas.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

intervalo de teclas 17

isto é algo que você precisa estar ciente se usando o nível de isolamento serializável.

resumo

se uma coluna chave é usada para filtrar dados em uma consulta, então as fechaduras de gama de chaves podem ser tomadas contra as chaves desse índice. Eles serão levados contra todas as chaves que satisfazem a consulta mais a chave sequencialmente seguinte fora da consulta. Isso então impede que os dados sejam adicionados que iria inserir uma nova chave imediatamente antes de uma chave com um bloqueio de gama de chaves.

Deixe uma resposta

O seu endereço de email não será publicado.