Descrever o bloqueio e o bloqueio
Uma característica dos bancos de dados relacionais é o bloqueio, que é essencial para manter as propriedades de atomicidade, consistência e isolamento do modelo ACID. Todos os RDBMSs bloquearão ações que violem a consistência e o isolamento de gravações em um banco de dados. Os programadores SQL são responsáveis por iniciar e terminar as transações no ponto certo, a fim de garantir a consistência lógica de seus dados. Por sua vez, o mecanismo de banco de dados fornece mecanismos de bloqueio que também protegem a consistência lógica das 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 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, quando o processo que detém o bloqueio o libera, o processo bloqueado pode então adquirir o bloqueio e concluir sua transação.
O SQL Server bloqueia a menor quantidade de dados necessária para concluir a transação com êxito. Esse comportamento permite simultaneidade máxima. Por exemplo, se o SQL Server estiver bloqueando uma única linha, todas as outras linhas da tabela estarão disponíveis para outros processos usarem, para que o trabalho simultâneo possa continuar. No entanto, cada bloqueio requer recursos de memória, portanto, não é econômico para um processo ter milhares de bloqueios individuais em uma única mesa. O SQL Server tenta equilibrar a simultaneidade com o custo. Uma técnica usada é chamada de escalonamento de bloqueio. Se o SQL Server precisar bloquear mais de 5000 linhas em um único objeto em uma única instrução, ele escalará os vários bloqueios de linha para um único bloqueio de tabela.
O bloqueio é um comportamento normal e acontece muitas vezes durante um dia normal. O bloqueio só se torna um problema quando causa um bloqueio que não é resolvido rapidamente. 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 longo período de tempo antes de liberá-los. Esses bloqueios fazem com que outros processos sejam bloqueados, o que pode degradar o desempenho e a simultaneidade da consulta.
Um processo fica bloqueado em um conjunto de recursos e nunca os libera. Este problema requer a intervenção do administrador para ser resolvido.
Outro cenário de bloqueio é o deadlocking, que ocorre quando uma transação tem um bloqueio em um recurso e outra transação tem um bloqueio em um segundo recurso. Em seguida, cada transação tenta bloquear o recurso, que está atualmente bloqueado pela outra transação. Teoricamente, esse cenário levaria a uma espera infinita, já que nenhuma transação poderia ser concluída. No entanto, o mecanismo do SQL Server tem um mecanismo para detetar esses cenários e matará uma das transações para aliviar o impasse, com base em qual transação executou a menor quantidade de trabalho que precisaria ser revertida. A transação que é morta é conhecida como a vítima do impasse. Os deadlocks são registrados na sessão de evento estendida system_health, que é habilitada por padrão.
É importante entender o conceito de 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 feitas pela instrução abaixo seriam automaticamente registradas no log de transações do banco de dados.
INSERT INTO DemoTable (A) VALUES (1);
Para permitir que os desenvolvedores tenham um controle mais granular sobre o código do aplicativo, o SQL Server também permite que você controle explicitamente suas transações. A consulta abaixo teria um bloqueio em uma linha na tabela DemoTable que não seria liberado até que um comando subsequente para confirmar a transação fosse adicionado.
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
A maneira correta de escrever a consulta acima é 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 por entrar no ficheiro 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. Esta é uma das principais razões para transações de longa duração.
O outro mecanismo que o mecanismo de banco de dados usa para ajudar na 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. Isso geralmente é usado em cargas de trabalho de uso misto, para evitar que as consultas de leitura bloqueiem as 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 que você precisa garantir 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 receberá um bloqueio exclusivo nos dados que estão modificando. No entanto, o nível de isolamento pode afetar o período de tempo em que as fechaduras são mantidas. Níveis de isolamento mais baixos aumentam a capacidade do processo de vários usuários 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:
Ler não confirmado – 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 esperar que a primeira transação seja concluída. Este nível também libera bloqueios de leitura assim que a operação de seleção é executada. Este é o nível padrão do SQL Server.
Leitura repetível – Este nível mantém bloqueios de leitura e gravação que são adquiridos em dados selecionados até o final da transação.
Serializável – Este é o nível mais alto de isolamento onde 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 final da transação.
O SQL Server também inclui dois níveis de isolamento que incluem o controle de versão de linha.
Ler instantâneo confirmado – Neste nível, as operações de leitura não têm bloqueios de linha ou página, e o mecanismo apresenta a cada operação um instantâneo consistente dos dados como existiam no início da consulta. Esse nível é normalmente usado quando os usuários estão executando consultas de relatório freqüentes em um banco de dados OLTP, a fim de evitar que as operações de leitura bloqueiem as 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. Este nível é vulnerável a conflitos de atualização. Se uma transação executada abaixo desse nível ler dados modificados por outra transação, uma atualização pela 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 SET , 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 para 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.
Monitorização de problemas de bloqueio
Identificar problemas de bloqueio pode ser problemático, pois eles podem ser esporádicos por natureza. Há um Detran chamado sys.dm_tran_locks, que pode ser associado sys.dm_exec_requests para fornecer mais informações sobre bloqueios que cada sessão está realizando. Uma maneira melhor de monitorar problemas de bloqueio é fazê-lo continuamente usando o mecanismo de eventos estendidos.
Os problemas de bloqueio normalmente se enquadram em duas categorias:
Design transacional ruim. Como mostrado acima, uma transação que não
COMMIT TRANSACTIONtem nunca vai acabar. Embora esse seja um exemplo simples, tentar fazer muito trabalho em uma única transação ou ter uma transação distribuída, que usa uma conexão de servidor vinculado, pode levar a um desempenho imprevisível.Transações de longa execução causadas pelo design do esquema. Frequentemente, isso pode ser 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 que você identifique rapidamente a degradação do desempenho relacionada ao bloqueio.
Para obter mais informações sobre como monitorar o bloqueio, consulte Compreender e resolver problemas de bloqueio do SQL Server.