Descrever bloqueio e 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 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.
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 que é morta é conhecida como a vítima do 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 log de transações do banco de dados.
INSERT INTO DemoTable (A) VALUES (1);
Para permitir que os desenvolvedores tenham controle mais granular sobre o código do aplicativo, o SQL Server também permite que você controle explicitamente suas transações. 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. Os dados alterados acabarão entrando no arquivo de dados de forma assíncrona. Essas transações representam uma unidade de trabalho para o mecanismo de banco de dados. Se o desenvolvedor esquecer de emitir o COMMIT TRANSACTION comando, a transação permanecerá aberta e os bloqueios não serão liberados. Esse é um dos principais motivos para transações de execução prolongada.
O outro mecanismo 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 de controle de versão de linha é habilitado para o banco de dados, o mecanismo mantém versões de cada linha modificada no TempDB. Normalmente, isso é usado em cargas de trabalho de uso misto, a fim de impedir que consultas de leitura bloqueiem consultas que estão gravando 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 que você defina o nível de consistência e correção necessários garantidos para seus dados. Os níveis de isolamento permitem encontrar um equilíbrio entre simultaneidade e 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 pode afetar o período de tempo que seus bloqueios são mantidos. Níveis de isolamento mais baixos aumentam a capacidade de vários processos do usuário de acessar dados ao mesmo tempo, mas aumentam os riscos de consistência de dados que podem 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. Leituras sujas são permitidas, o que significa que uma transação pode ver alterações feitas por outra transação que ainda não foi confirmada.
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 libera bloqueios de leitura assim que a operação de seleção é 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. Bloqueios de leitura e gravação são adquiridos em dados selecionados e não liberados até o final da transação.
O SQL Server também inclui 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 estão executando consultas de relatórios frequentes em um banco de dados OLTP, a fim de impedir que as operações de leitura bloqueiem as 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. Se uma transação em execução nesse nível ler os dados modificados por outra transação, uma atualização da transação de instantâneo será encerrada e revertida. Isso não é um problema com o isolamento de instantâneo confirmado por leitura.
Os níveis de isolamento são definidos para cada sessão com o comando T-SQL #D0, conforme mostrado:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
Não há como definir um nível de isolamento global em todas as consultas em execução em um banco de dados ou para todas as consultas executadas por um usuário específico. É 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 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.
Normalmente, os problemas de bloqueio 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.