Descrever o bloqueio
Um dos recursos dos bancos de dados relacionais é o bloqueio. Ele é essencial para manter itens como atomicidade, consistência e propriedades de isolamento do modelo ACID. Todos os RDBMSs bloquearão ações que violarem a consistência e o isolamento de gravações em um banco de dados. Os programadores de SQL são responsáveis por iniciar e encerrar transações no ponto correto a fim de garantir a consistência lógica dos dados. O mecanismo de banco de dados por sua vez fornece mecanismos de bloqueio que também protegem a consistência lógica de tabelas afetadas por essas consultas. Essas ações são uma parte fundamental do modelo relacional.
No SQL Server, o bloqueio ocorre quando um processo mantém o bloqueio de um recurso específico (linha, página, tabela ou banco de dados) e um segundo processo tenta adquirir um bloqueio usando um tipo de bloqueio incompatível no mesmo recurso. Os bloqueios normalmente são mantidos por um período curto. Além disso, quando o processo que mantém o bloqueio executa a liberação dele, o processo bloqueado poderá adquirir o bloqueio e concluir a transação.
O SQL Server bloqueia a menor quantidade de dados necessários para concluir a transação com êxito. Esse comportamento permite obter uma simultaneidade máxima. Por exemplo, caso o SQL Server esteja bloqueando uma linha, todas as outras linhas da tabela estarão disponíveis para que outros processos as usem, de modo que trabalhos simultâneos possam continuar sendo executados. Contudo, cada bloqueio requer obter recursos de memória. Portanto, não é econômico que um processo tenha milhares de bloqueios individuais em uma tabela única. O SQL Server tenta balancear a simultaneidade com o custo. Uma técnica usada é o escalonamento de bloqueios. Caso o SQL Server precise bloquear mais de 5.000 linhas em um objeto de uma instrução, ele escalará vários bloqueios de linhas em um bloqueio de tabela única.
O bloqueio é um comportamento regular e ocorre várias vezes durante um dia normal. O bloqueio se tornará um problema somente quando ele não for resolvido de modo rápido. Há dois tipos de problemas de desempenho que podem ser causados pelo bloqueio:
Um processo mantém bloqueios em um conjunto de recursos por um período de tempo estendido antes de liberá-los. Esses bloqueios fazem com que outros processos sejam bloqueados, o que pode prejudicar o desempenho e a simultaneidade da consulta.
Um processo obtém bloqueios em um conjunto de recursos e nunca os libera. Esse problema requer a intervenção do administrador para ser solucionado.
Outro cenário de bloqueio é o deadlock, que ocorre quando uma transação tem um bloqueio em um recurso e outra transação tem um bloqueio em outro recurso. Depois, cada transação tenta executar um bloqueio no recurso que atualmente está bloqueado pela outra transação. Na teoria, esse cenário levará a uma espera infinita, pois nenhuma transação será concluída. No entanto, o mecanismo do SQL Server tem um método para detectar esses cenários. Ele eliminará uma das transações para aliviar o deadlock com base em qual transação executou a menor quantidade de trabalhos que precisarão ser revertidos. 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 é habilitada por padrão.
É importante entender o conceito de uma transação. A confirmação automática é o modo padrão do SQL Server e do Banco de Dados SQL do Azure, o que significa que as alterações executadas pela instrução abaixo seriam gravadas automaticamente no log 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 abaixo bloquearia uma linha na tabela DemoTable que não seria liberada até que um comando posterior fosse adicionado para fazer commit da transação.
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
A maneira adequada de gravar a consulta acima é a seguinte:
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
COMMIT TRANSACTION
O comando COMMIT TRANSACTION confirma um registro das alterações no log de transações de modo explícito. 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. Caso o desenvolvedor esqueça de acionar 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 afetará bloqueios obtidos para impedir a modificação de dados. Além disso, 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 anteriormente lidos, porém não modificado por outra transação sem precisar 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 repetida – este nível mantém os bloqueios de leitura e gravação adquiridos em dados selecionados até o término da transação.
Serializado – este é 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 – neste 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 – este 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 difícil, pois eles podem ser esporádicos por natureza. Há uma DMV chamada sys.dm_tran_locks, que pode ser associada à sys.dm_exec_requests para fornecer mais informações sobre os bloqueios que cada sessão está mantendo. Usar o mecanismo de Eventos Estendidos de maneira contínua é um modo mais adequado de monitorar problemas de bloqueio.
Os problemas de bloqueio normalmente se enquadram em duas categorias:
Design transacional inadequado. Conforme mostrado acima, uma transação sem
COMMIT TRANSACTIONnunca será encerrada. Embora esse seja um exemplo simples, tentar executar vários trabalhos em uma transação ou ter uma transação distribuída que usa uma conexão de servidor vinculada poderá gerar um desempenho imprevisível.Transações de execução prolongada criadas pelo design de esquema. Muitas vezes, isso pode ser uma atualização em uma coluna com um índice ausente ou uma consulta de atualização projetada de modo inadequado.
Monitorar problemas de desempenho relacionados ao bloqueio permite identificar de modo rápido uma degradação do desempenho relacionada ao bloqueio.
Para obter mais informações sobre como monitorar o bloqueio, confira Entender e resolver problemas de bloqueio de SQL Server.