Descrever o bloqueio
O bloqueio é um recurso fundamental dos bancos de dados relacionais, essenciais para manter as propriedades de atomicidade, consistência e isolamento do modelo ACID. Todos os RDBMSs bloqueiam ações que violariam a consistência e o isolamento de gravações de banco de dados. Os programadores de SQL devem iniciar e encerrar transações nos pontos certos para garantir a consistência dos dados. O mecanismo de banco de dados fornece mecanismos de bloqueio para proteger a consistência lógica das tabelas afetadas, que é fundamental para o modelo relacional.
No SQL Server, o bloqueio ocorre quando um processo mantém um bloqueio em um recurso específico (linha, página, tabela, banco de dados) e um segundo processo tenta adquirir um bloqueio com um tipo de bloqueio incompatível no mesmo recurso. Normalmente, os bloqueios são mantidos por um curto período e, uma vez que o processo que mantém o bloqueio o libera, o processo bloqueado pode adquirir o bloqueio e concluir sua transação.
O SQL Server bloqueia a menor quantidade de dados necessária para concluir uma transação, permitindo a simultaneidade máxima. Por exemplo, se o SQL Server bloquear uma única linha, todas as outras linhas da tabela permanecerão disponíveis para outros processos, habilitando o trabalho simultâneo. No entanto, cada bloqueio requer recursos de memória, portanto, não é econômico para um processo manter milhares de bloqueios individuais em uma única tabela. Para equilibrar a simultaneidade com o custo, o SQL Server usa uma técnica chamada escalonamento de bloqueio. Se mais de 5.000 linhas em um único objeto precisarem ser bloqueadas em uma única instrução, o SQL Server escalona os bloqueios de várias linhas para um único bloqueio de tabela.
O bloqueio é um comportamento normal e ocorre com frequência ao longo do dia. Ele só se torna problemático quando causa bloqueios que não são resolvidos rapidamente. Há dois tipos de problemas de desempenho causados pelo bloqueio:
- Um processo mantém bloqueios em um conjunto de recursos por um longo período antes de liberá-los, fazendo com que outros processos bloqueiem e degradam o desempenho e a simultaneidade da consulta.
- Um processo adquire bloqueios em um conjunto de recursos e nunca os libera, exigindo a intervenção do administrador para resolver.
O deadlocking é outro cenário de bloqueio que ocorre quando uma transação mantém um bloqueio em um recurso e outra transação mantém um bloqueio em um recurso diferente. Cada transação tenta adquirir um bloqueio no recurso bloqueado atualmente pela outra transação, levando a uma espera infinita, pois nenhuma transação pode ser concluída. O mecanismo do SQL Server detecta esses cenários e resolve o deadlock matando uma das transações, com base em qual transação executou a menor quantidade de trabalho que precisa ser revertida. A transação encerrada é conhecida como vítima de deadlock. Os deadlocks são registrados na sessão de evento estendido chamada system_health, que está habilitada por padrão.
É importante entender o conceito de uma transação. O compromisso automático é o modo padrão do SQL Server e do Banco de Dados SQL do Azure, o que significa que as alterações feitas pela instrução a seguir seriam registradas automaticamente no arquivo de transações do banco de dados.
INSERT INTO DemoTable (A) VALUES (1);
O SQL Server também permite controlar de modo explícito suas transações para permitir que os desenvolvedores tenham um controle mais granular sobre o código do aplicativo. A consulta a seguir usaria um bloqueio em uma linha na tabela DemoTable que não seria liberada até que um comando subsequente para confirmar a transação fosse adicionado.
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
A maneira correta de gravar a consulta a seguir é a seguinte:
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
COMMIT TRANSACTION
O COMMIT TRANSACTION comando confirma explicitamente um registro das alterações no log de transações. Por fim, os dados alterados chegarão ao arquivo de dados de modo assíncrono. Essas transações representam uma unidade de trabalho para o mecanismo de banco de dados. Se o desenvolvedor esquecer de emitir o comando COMMIT TRANSACTION, a transação permanecerá aberta e os bloqueios não serão liberados. Esse é um dos principais motivos para usar transações de execução prolongada.
Outro método que o mecanismo de banco de dados usa para ajudar a simultaneidade do banco de dados é o controle de versão de linha. Quando um nível de isolamento do controle de versão de linha está habilitado no banco de dados, o mecanismo mantém versões de cada linha modificada no TempDB. Isso geralmente é usado em cargas de trabalho de uso misto para evitar que as consultas de leitura bloqueiem consultas que estão executando uma gravação no banco de dados.
Para monitorar transações abertas aguardando confirmação ou reversão, execute a seguinte consulta:
SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, transaction_type = CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow
, transaction_state = CASE tat.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.' END
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;
Níveis de isolamento
O SQL Server oferece vários níveis de isolamento para permitir a definição do nível de consistência e exatidão que você precisa garantir para seus dados. Os níveis de isolamento permitem encontrar um equilíbrio entre a simultaneidade e a consistência. O nível de isolamento não afeta os bloqueios feitos para impedir a modificação de dados. Uma transação sempre obterá um bloqueio exclusivo nos dados que estão sendo modificados. No entanto, seu nível de isolamento poderá afetar o período de tempo que os bloqueios são mantidos. Os níveis de isolamento mais baixos aumentam a capacidade do processo de vários usuários acessar dados ao mesmo tempo, porém eles aumentam os riscos de consistência de dados que poderão ocorrer. Os níveis de isolamento no SQL Server são os seguintes:
Leitura não confirmada – Nível de isolamento mais baixo disponível. As leituras sujas são permitidas. Isso significa que uma transação poderá conferir as alterações executadas por outra transação que ainda não foram confirmadas.
Leitura confirmada – permite que uma transação leia dados lidos anteriormente, mas não modificados por outra transação sem aguardar a conclusão da primeira transação. Esse nível também poderá liberar bloqueios de leitura assim que a operação de seleção for executada. Esse é o nível padrão do SQL Server.
Leitura repetível – esse nível mantém bloqueios de leitura e gravação adquiridos em dados selecionados até o final da transação.
Serializado – Esse é o nível mais alto de isolamento em que as transações são isoladas. Os bloqueios de leitura e gravação são adquiridos em dados selecionados e não são liberados até o término da transação.
O SQL Server também tem dois níveis de isolamento que incluem o controle de versão de linha.
Instantâneo de Leitura Confirmada – Nesse nível, as operações de leitura não usam bloqueios de linha nem de página. Além disso, o mecanismo apresenta cada operação com um instantâneo consistente dos dados que existiam no início da consulta. Esse nível é normalmente usado quando os usuários executam consultas de relatórios frequentes em um banco de dados OLTP para impedir que operações de leitura bloqueiem operações de gravação.
Instantâneo – Esse nível fornece consistência de leitura no nível de transação por meio do controle de versão de linha. Esse nível é vulnerável a conflitos de atualização. Caso uma transação em execução leia dados modificados por outra transação nesse nível, uma atualização será encerrada e revertida pela transação de instantâneo. Isso não será um problema para o isolamento de instantâneo de leitura confirmada.
Os níveis de isolamento são definidos para cada sessão com o comando T-SQL SET, conforme mostrado abaixo:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
Não há como definir um nível de isolamento global para todas as consultas em execução em um banco de dados ou para consultas executadas por um usuário específico. Essa é uma configuração de nível de sessão.
Monitoramento de problemas de bloqueio
Identificar problemas de bloqueio pode ser desafiador devido à sua natureza esporádica. A DMV sys.dm_tran_locks, quando unida com sys.dm_exec_requests, fornece informações sobre os bloqueios mantidos por cada sessão. Uma maneira mais eficaz de monitorar problemas de bloqueio é usar o mecanismo de Eventos Estendidos continuamente.
Os problemas de bloqueio normalmente se enquadram em duas categorias:
- Design transacional ruim: por exemplo, uma transação sem um
COMMIT TRANSACTIONnunca terminará. Tentar fazer muito trabalho em uma única transação ou ter uma transação distribuída usando uma conexão de servidor vinculado pode levar a um desempenho imprevisível. - Transações de execução longa causadas pelo design de esquema: isso geralmente envolve uma atualização em uma coluna com um índice ausente ou uma consulta de atualização mal projetada.
O monitoramento de problemas de desempenho relacionados ao bloqueio permite identificar rapidamente a degradação do desempenho relacionada ao bloqueio.
Para obter mais informações sobre como monitorar o bloqueio, consulte Entenda e resolva problemas de bloqueio do SQL Server.