Blog – Fabiano Neves Amorim

Vamos lá, comecei a escrever um texto pra falar sobre bloqueios e vi que ele estava ficando muito grande, então eu assumi que ele ia ficar grande e aproveitei pra fazer um texto mais completo.

Pra entender um pouco mais sobre como funcionam os bloqueios e qual o impacto de um Reorganize, vamos começar com um simples exemplo e detalhar o que o SQL está fazendo.

Preparando o ambiente

Criando uma tabela pra usar nos testes.

USE Northwind

GO

— Preparar ambiente…

— 2 segundos para rodar

IF OBJECT_ID(‘OrdersBig’) IS NOT NULL

DROP TABLE OrdersBig

GO

SELECT TOP 1000000

IDENTITY(Int, 1,1) AS OrderID,

ABS(CheckSUM(NEWID()) / 10000000) AS CustomerID,

CONVERT(Date, GETDATE() – (CheckSUM(NEWID()) / 10000000)) AS OrderDate,

ISNULL(ABS(CONVERT(Numeric(18,2), (CheckSUM(NEWID()) / 1000000.5))),0) AS Value,

CONVERT(VARCHAR(250), NEWID()) + CONVERT(VARCHAR(250), NEWID()) + CONVERT(VARCHAR(250), NEWID()) AS ColNewID

INTO OrdersBig

FROM master.dbo.sysobjects A

CROSS JOIN master.dbo.sysobjects B CROSS JOIN master.dbo.sysobjects C CROSS JOIN master.dbo.sysobjects D

GO

ALTER TABLE OrdersBig ADD CONSTRAINT xpk_OrdersBig PRIMARY KEY(OrderID)

GO

Testes de um select simples utilizando com nível de isolamento padrão (READ COMMITTED)

Agora, suponhamos que eu rode o seguinte comando:

SELECT OrderID FROM Northwind.dbo.OrdersBig WHERE OrderID <= 3

Vou usar o sqlcmd pra rodar o select, o comando fica assim:

sqlcmd -S razerfabiano\sql2019 -U Login1 -P @bc12345 -Q “SELECT OrderID FROM Northwind.dbo.OrdersBig WHERE OrderID <= 3”

Internamente os bloqueios gerados ficam assim:

1. Inicia uma transação implícita pro o comando, ou seja, todos os bloqueios serão controlados no escopo dessa transação. Pra esse caso, vamos considerar que TransactionID é 18935
2. Adquire um S lock no banco Northwind
3. Adquire um IS lock no objeto OrdersBig
4. Adquire um IS lock no arquivo/página (1:85896)
5. Adquire um S lock na linha (KEY) com o OrderID = 1, %%lockres%% = (8194443284a0)
6. Libera o S lock na linha (KEY) com o OrderID = 1
7. Adquire um S lock na linha (KEY) com o OrderID = 2, %%lockres%% = (61a06abd401c)
8. Libera o S lock na linha (KEY) com o OrderID = 2
9. Adquire um S lock na linha (KEY) com o OrderID = 3, %%lockres%% = (98ec012aa510)
10. Libera o S lock na linha (KEY) com o OrderID = 3
11. Libera o IS lock no arquivo/página (1:85896)
12. Libera o IS lock no objeto OrdersBig
13. Libera o S lock no banco Northwind

É possível ver essa sequência de eventos acontecendo utilizando o profiler para capturar os eventos de lock.

Algumas informações importantes em relação aos eventos:

1. Um S lock é utilizado na linha pra garantir que enquanto a linha está sendo lida ninguém pode modifica-la. Ou seja, se enquanto o SQL estiver lendo a linha alguma sessão tentar fazer uma modificação nessa linha, ela ficará bloqueada.
2. Repare que utilizando o nível de isolamento padrão (READ COMMITED), assim que uma linha é lida, o bloqueio é liberado, ou seja, o tempo que um LOCK de leitura é mantido na linha tende a ser muito rápido.
3. Intent lock é um conceito importante e que você precisa conhecer. Se quiserem falar mais sobre isso me avisa que eu preparo algo.

Testes do select com READUNCOMMITTED/NOLOCK

Se eu rodar a mesma query, porém utilizando o hint READUNCOMMITTED ou NOLOCK eu evito que uma sessão tentando alterar a linha fique bloqueada, porém corro o risco de ler dados sujos e etc (todos os outros problemas que já falei sobre NOLOCK, ver treinamento de dicas).

Só pra deixar a sequencia de eventos, a query com NOLOCK fica assim:

Como podemos ver no print do profiler, temos apenas um S lock no banco e um Sch-S lock na tabela pra evitar que a estrutura dela seja modificada enquanto a leitura está ocorrendo. Ou seja, uma query com NOLOCK pode gerar bloqueios de comandos DDL.

Testes do select com REPEATABLE/SERIALIZABLE READ

Com REPEATABLE/SERIALIZABLE READ o SQL precisa garantir que a leitura de uma linha terá o mesmo valor até o termino da query, ou seja, o lock na linha só será liberado quando todas as linhas forem lidas, diferente do comportamento em READ COMMITTED que libera o lock assim que a linhas é lida.

Consideração importante em relação aos níveis de isolamento mais pessimistas como REPEATABLE READ e SERIALIZABLE:

• Infelizmente, é muito comum os desenvolvedores utilizarem o nível de isolamento equivocado, ou seja, uma conexão que deveria utilizar um READ COMMITTED é aberta com SERIALIZABLE e isso passa a gerar uma série de locks (que podem gerar um lock escalation pra tabela) totalmente desnecessários e mais demorados, gerando bloqueios.

Exemplo de problema com Entity Framework

Criei uma aplicaçãozinha tosca que usa EF pra ler a tabela OrdersBig, a tela ficou assim:

O botão “button2” faz o seguinte:

private void button2_Click(object sender, EventArgs e)

{

using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(TransactionScopeOption.RequiresNew))

{

var v1 = new NorthwindEntities();

// Te dou um prêmio se você adivinhar qual é o IsolationLevel default…

// MessageBox.Show(Transaction.Current.IsolationLevel.ToString());

dataGridView1.DataSource = v1.OrdersBigs.Take(20000).ToList();

scope.Complete();

v1.Database.Connection.Close();

}

}

Estou usando tudo default e nada complexo, só iniciando uma transação e lendo as primeiras 20 mil linhas da tabela OrdersBig. Adivinha qual o nível de isolamento será utilizado?

Vamos ver o que o SQL nos diz em relação a essa sessão aberta pelo EF.

SELECT session_id, login_time, host_name, program_name, open_transaction_count,

CASE transaction_isolation_level

WHEN 0 THEN ‘Unspecified’

WHEN 1 THEN ‘Read Uncommitted’

WHEN 2 THEN ‘Read Committed’

WHEN 3 THEN ‘Repeatable Read’

WHEN 4 THEN ‘Serializable’

WHEN 5 THEN ‘Snapshot’

END AS [Transaction Isolation Level]

FROM sys.dm_exec_sessions

WHERE program_name LIKE ‘Entity%’

GO

Pois é, como eu disse, é preciso cuidado por parte dos desenvolvedores e atenção no monitoramento dos DBAs.

Olha o que esse select gerou de locks.

Além dos locks nas linhas até a query terminar, um lock escalation, tenso.

Testes com comando gerando modificações na tabela

Vamos supor o seguinte update pra atualizar 3 linhas da tabela OrdersBig:

UPDATE OrdersBig SET ColNewID =
‘Fabiano’
WHERE OrderID <= 3

Vou usar o sqlcmd pra rodar o select, o comando fica assim:

sqlcmd -S razerfabiano\sql2019 -U Login1 -P @bc12345 -Q “UPDATE Northwind.dbo.OrdersBig SET ColNewID = ‘Fabiano’ WHERE OrderID <= 3”

Internamente os bloqueios gerados ficam assim:

1. Inicia uma transação implícita pro o comando, ou seja, todos os bloqueios serão controlados no escopo dessa transação. Pra esse caso, vamos considerar que TransactionID é 566400
2. Adquire um S lock no banco Northwind
3. Adquire um IX lock no objeto OrdersBig
4. Adquire um IX lock no arquivo/página (1:85896)
5. Adquire um X lock na linha (KEY) com o OrderID = 1, %%lockres%% = (8194443284a0)
6. Adquire um X lock na linha (KEY) com o OrderID = 2, %%lockres%% = (61a06abd401c)
7. Adquire um X lock na linha (KEY) com o OrderID = 3, %%lockres%% = (98ec012aa510)
8. Libera o X lock na linha (KEY) com o OrderID = 1
9. Libera o X lock na linha (KEY) com o OrderID = 2
10. Libera o X lock na linha (KEY) com o OrderID = 3
11. Libera o IX lock no arquivo/página (1:85896)
12. Libera o IX lock no objeto OrdersBig
13. Libera o S lock no banco Northwind

Novamente, conseguimos ver essa sequência de eventos acontecendo no profiler.

Até aqui, não temos novidades, o SQL pega um X lock em cada linha modificada e só libera quando todas as linhas forem modificadas, e tudo isso ocorre dentro do contexto de uma transação.

Mas e como ficam os bloqueios das leituras e as modificações geradas por um Reorganize?

Locks gerados por um reorganize

Bom, diz a lenda que um reorganize é lindo, é online e pode ser parado a qualquer momento sem crise.

Utilizando palavras da MS
“Reorganizing an index uses minimal system resources and is an online operation. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.”.

O truque pra que o reorganize não gere bloqueios é que ele não roda no contexto de uma única transação. (a não ser que você use uma transação explicita, ver https://github.com/MicrosoftDocs/sql-docs/pull/4011 do Mr. Rodrigo)

Vejamos isso na prática.

Dessa vez, pra monitorar os bloqueios gerados, além do profiler, vou usar também a “sp_whoisactive @get_locks = 1”.

Considerando então um:

ALTER
INDEX
ALL
ON Northwind.dbo.OrdersBig REORGANIZE

    Enquanto o reorganize estava rodando, chamei a “sp_whoisactive @get_locks = 1” e o xml da coluna locks é o seguinte:

E no profiler temos o seguinte (estou exibindo apenas os dados relevantes).

SQL começa fazendo bloqueios internos, repare que o TransactionID é o 4670676.

Depois já podemos ver que ele cria várias pequenas transações, pra evitar longos bloqueios. Repare no TransactionID mudando.

Por fim, antes de terminar ele libera os bloqueios inicias do TransactionID 4670676.

Alguns comentários em relação a isso.

Aaaa, Fabiano, mas ele pegou um IX na tabela, isso ae não pode gerar problema? Vamos considerar os cenários que vimos nesse artigo.

Vamos pegar a tabela de compatibilidade de locks do help pra nos ajudar.

Eu realcei em vermelho os locks que o reorganize vai gerar, são eles: IS + IX na tabela e um X na página. Considerando esses locks operações ficariam bloqueadas?

“Select simples” – Primeiro exemplo que vimos nesse artigo.

Conforme vimos, um select simples gerou um IS na tabela, IS na página e alguns S nas linhas lidas. Olhando na tabela, podemos ver que quando o select tentar fazer um IS na página que já tem um X (já obtido pelo reorganize), ele vai ficar bloqueado. Portanto, sim o reorganize pode gerar bloqueios, podem na minha opinião eles serão extremamente rápidos, pois, lembre-se, o reorganize vai liberar o lock X na página assim que terminar a pequena transação que ele criou.

“Select com nolock”

O select com nolock não gerou locks na tabela, página ou linha, apenas um schema lock. Portanto o reorganize não causaria bloqueio.

“Select com serializable gerando lock escalation”

A demo com o aplicativo .net consultando o banco via EF gera os seguintes locks: IS na tabela, IS na página e um S nas linhas sendo lidas, também após atingir o threshold ( +- 5 mil locks) necessário pra disparar o lock escalation, o SQL “promoveu” o IS na tabela já obtido pra S.

Nesse cenário, a sessão ficará bloqueada quando tentar fazer IS na página que já tem um X (já obtido pelo reorganize), porém conforme eu já mencionei, esse bloqueio será muito rápido. Mas, o que vai acontecer quando o lock escalation ocorrer e ele tentar fazer um S na tabela? De acordo com a tabela de compatibilidades, um S na tabela
não é compatível com o IX na tabela já obtido pelo reorganize, sendo assim, o escalation não vai acontecer e o SQL vai continuar fazendo os locks nas linhas, uma nova tentativa de promover o IS pra S (lock escalation) será efetuada a cada 1250 novos locks obtidos. No nosso caso, ele não vai conseguir fazer o escalation nunca, pois o IX na tabela só será liberado quando o reorganize terminar.

Conclusão

Eu queria fazer outros testes, e ver outros cenários, mas já estou cansado e deu por hoje.