Atenção: por essa edição ser muito antiga não há arquivo PDF para download.
Os artigos dessa edição estão disponíveis somente através do formato HTML.

Clique aqui para ler todos os artigos desta edição

Conceitos e Otimização de bloqueios no SQL Server

         A arquitetura de ambientes transacionais é fundamentada no conceito segundo o qual, para que haja consistência de informações, é necessário que se estabeleçam regras rígidas no acesso a dados. Essas regras não permitem, por exemplo, que dois usuários compartilhem a mesma alteração ou excluam o mesmo registro em uma tabela. Para executar essas restrições, o banco utiliza um recurso denominado bloqueio (lock) em transações.

         O ato de abrir uma transação sinaliza para o banco que as tabelas manipuladas deverão ser tratatas com algum tipo de bloqueio para que sejam consistentes.

O SQL Server efetua bloqueios sempre no menor recurso disponível, para viabilizar maior concorrência. Isso evita cenários não coerentes, como bloquear uma tabela para alterar apenas uma linha. A situação ideal é que seja bloqueado somente o set de registros manipulados na transação, liberando, assim, acessos concorrentes a outros registros na mesma tabela.

No entanto, bloqueios consomem recursos - cada um gasta 64 bytes e cada processo aguardando a liberação do recurso bloqueado consome 32 bytes de memória. Por economia, sempre que a quantidade de bloqueios na sessão excede um limite interno, eles são promovidos à níveis mais abrangentes. Dessa forma, bloqueios de linha e/ou página podem ser transformados em bloqueios de tabela. Sempre que acontece o escalonamento, os bloqueios de nível mais baixo são liberados.

         Os recursos que podem ser bloqueados, em ordem crescente de granulidade, estão listados na tabela 1.

NOTA: Bloqueios de linha nunca são promovidos para bloqueios de página.

NOTA: Granulidade diz respeito à abrangência do bloqueio, ou seja, se afeta uma linha, página ou tabela.

Tipos de Bloqueio

         Os bloqueios podem diferir quanto ao recurso bloqueado (linha, página, extent, tabela ou database), duração (se permanecem ou não ativos durante toda a transação) ou tipo (Shared, Update, Exclusive, Intent, Schema ou Bulk Update). Os tipos são detalhados a seguir:

Shared (S): utilizados para processos de leitura. Ao se aplicar um comando SELECT, um bloqueio do tipo shared é efetuado. Sua finalidade é restringir alterações de dados e/ou de estrutura nos objetos que estão sendo lidos. Shared locks são compatíveis entre si; ou seja, é possível executar vários SELECTs para uma mesma tabela sem que uma sessão trave a outra. Quando executados sob o nível de isolamento read committed permanecem ativos somente durante a execução do comando. Em isolamentos repeatable read ou serializable, permanecem ativos até o término da transação.

Update (U): são utilizados em processos de alteração/exclusão de dados. Para entender seu uso, devemos notar que um UPDATE, de forma geral, é antecedido de uma leitura do conjunto de registros que será alterado. Por exemplo:

UPDATE FROM funcionarios

SET salario = salario * 1.10

WHERE depto = 5

         Antes de iniciar a alteração o SQL Server recupera todos os funcionários do departamento 5, promovendo um update lock durante a leitura (e não durante a alteração em si). Update locks não são compatíveis entre si, ou seja, não é permitido mais de um bloqueio para um mesmo recurso em paralelo – o que evita a ocorrência de deadlocks de conversão. Após a leitura dos dados, o bloqueio é promovido para exclusive e a alteração é efetuada. Com esse mecanismo, O SQL Server garante maior concorrência durante a atualização.

Nota: Maiores detalhes sobre deadlocks de conversão estão disponíveis na SQL Magazine 2.

Exclusive (X): acionado no momento em que a alteração/exclusão efetivamente acontece. Um bloqueio exclusivo assegura que um e somente um processo estará manipulando o conjunto de registros definido na transação. Como o nome sugere, eles não são compatíveis com qualquer outro tipo de bloqueio, sendo liberados somente no término da transação.

Intent (I): um intent lock nada mais é do que uma “marca” sinalizando que existem bloqueios de menor nível hierárquico sendo executados. Por exemplo, um processo que detém um lock exclusive de linha gera também dois intent exclusive locks: um para a página onde se encontra o registro e outro para a tabela. A finalidade do intent lock é evitar o conflito de recursos. Imagine que um processo necessite de um bloqueio exclusivo de tabela. Se existem intent locks ativos neste objeto, o bloqueio não poderá ser honrado.

         Além de IS (Intent Share), IX (Intent Exclusive) e IU (Intent Update), existem outras opções de intent locks, que acontecem quando eles são combinados com shared locks (por exemplo, você atualiza um registro e, na mesma transação, efetua uma leitura na página que o contém): SIX (Shared with Intent Exclusive), UIX (Update with Intent Exclusive) e SIU (Shared Intent Update).

Schema: controla atualizações de estrutura (também conhecida como schema) das tabelas. Evitam, por exemplo, que uma tabela que está sendo utilizada em produção seja alterada (Schema Modification Locks ou Sch-M), ou que algum processo acesse uma tabela cuja estrutura está em modificação (Schema Stability Locks ou Sch-S).

Bulk Update: utilizado para agilizar processos envolvendo a transferência de arquivos texto com o utilitário BCP. Esse bloqueio é acionado quando a opção TabLock estiver ativa ou quando com a opção Table lock on bulk load estiver setada para a tabela através do comando sp_TableOption.

NOTA: BCP ou Bulk Copy Program é um utilitário de linha de comando disponível no SQL Server para importação/exportação de arquivos texto.

Dependendo da operação realizada no banco, o SQL Server opta automaticamente por um tipo de bloqueio. Observe que diversos tipos podem ser combinados em uma mesma transação. Para verificar a compatibilidade entre tipos de bloqueio, acesse

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_8um1.asp

Hints

         O comportamento de bloqueios está diretamente ligado ao nível de isolamento adotado na transação. Conforme visto no artigo anterior (edição 4), o SQL Server reconhece os seguintes níveis de isolamento:

Read Uncommitted - Permite a leitura de dados não comitados, desrespeitando os bloqueios exclusivos mantidos nas transações.

Read Committed - Nível de isolamento padrão. Significa que processos de leitura só podem ser efetuados sobre dados comitados. Bloqueios realizados em comandos SELECT são liberados no término do comando.

Repeatable Read - Incorpora as características de Read Committed. Assegura que registros lidos não serão alterados por outros processos, garantindo releituras idênticas. Bloqueios realizados em comandos SELECT são liberados somente no término da transação.

Serializable - Incorpora as características de Repeatable Read, não permitindo, adicionalmente, que outras sessões efetuem inserções no conjunto de registros lidos.

         Às vezes pode ser interessante especificar, em um determinado comando, um nível de isolamento diferente do adotado para a sessão. Para isso, usa-se diretivas conhecidas como hints, que são declaradas junto aos comandos de manipulação de dados. Observe a listagem a seguir:

BEGIN TRAN                                                    

        INSERT x …

        UPDATE y …

        …

        SELECT * FROM z (holdlock)

        ..

        SELECT * FROM w

COMMIT

O hint holdlock altera o nível de isolamento adotado na sessão (read committed), para Repeatable Read. Se o hint não fosse utilizado, o bloqueio seria liberado logo após a operação de leitura (antes da execução de SELECT * FROM w).

         Em geral, hints podem ser utilizados para modificar as seguintes propriedades:

·         Alterar o nível de isolamento do comando:

Ex: SELECT OrderId FROM Orders (HoldLock)

·         Alterar a granulidade do comando:

Ex: SELECT OrderId FROM Orders (PagLock)

·         Alterar o tipo padrão de bloqueio:

Ex: SELECT OrderId FROM Orders (UpdLock)

·         Selecionar um índice específico para resolver uma query:

Ex: SELECT OrderId FROM Orders (index=OrderDate)

·         Forçar um tipo de join para o plano de execução (Loop, Merge ou Hash):

Ex: SELECT o.OrderId  FROM Orders o

             inner LOOP JOIN [Order Details] d on o.OrderId = d.OrderId

         Veja uma descrição detalhada dos tipo de hint no box “Tipos de Hint”. A seguir, algumas regras na utilização de hints:

·                     Operações que modificam dados exigem bloqueios exclusivos e não podem ter seu nível de isolamento alterado para NoLock, ReadUncommitted ou ReadPast.

·                     Só é possível combinar hints de origens diferentes (por exemplo, combina-se holdlock com updlock, mas nunca holdlock com nolock). A mensagem de erro 1047: Conflicting locking hints specified traduz essa regra.

·                     Avaliar bem se a utilização de hints é necessária. Alguns hints têm o poder de “mascarar” problemas latentes - por exemplo, o hint NoLock otimiza os processos ao extremo. Alguns administradores ficam tentados a usar esse recurso, ao invés de investigar as queries com baixa performance. O problema é que NoLock pode gerar “registros fantasmas”, resultantes da leitura não comitada. Como normalmente a performance não é mais importante do que a consistência das informações, o uso desse hint deve ser avaliado com cuidado.

Um caso prático

         Vejamos um exemplo prático envolvendo hints e transações. Criaremos uma procedure para inserir linhas na tabela Pedido (figura 1) e retornar o número do pedido para a aplicação. Os parâmetros da procedure são o código do cliente (cod_cliente) e o valor do pedido (vlr_pedido). O número do pedido será obtido da tabela Parametro, que contém somente um registro informando o número do próximo pedido. Após ler o valor corrente, o número do pedido nesta tabela será incrementado em uma unidade. A operação acontece em um ambiente multi-usuário, com geração de pedidos simultânea. Deve-se, portanto, identificar o nível de isolamento adequado para que não existam problemas de violação de chave primária na tabela Pedido. Na listagem 1 vemos alguns cenários para resolução do problema.

Figura 1: Tabelas utilizadas no estudo de caso sobre hints

Listagem 1: Cenários envolvendo criação de procedure para atualização do Pedido

         A procedure do cenário 1 está sujeita a violação de chave primária na tabela Pedido. Nesse exemplo, dois processos concorrentes podem resgatar o mesmo número de pedido da tabela Parametro, pois não existe bloqueio para SELECTs rodando sobre transações com nível de isolamento read committed (o nível default).

         Os cenários 2 e 3 apresentam um caso propício para deadlocks de conversão. Para relembrar, um deadlock é uma espera cíclica, que acontece quando um processo X fica aguardando a liberação do bloqueio de um processo Y, e vice-versa.

         Observe que o bloqueio holdlock (ou Repeatable Read) realizado na tabela Parâmetro impede que outras sessões modifiquem o registro, mas não proíbe que seu conteúdo seja acessado. Além disso, bloqueios holdlock são compatíveis entre si, ou seja, permitem que outras sessões também repitam o bloqueio (bloqueios holdlock são do tipo Shared).

Dessa forma, se duas sessões executarem a procedure no mesmo instante, o UPDATE não conseguirá promover um bloqueio exclusive. Como exclusive não permite a existência de nenhum outro bloqueio para o mesmo recurso, a primeira sessão ficará aguardando a liberação do bloqueio holdlock estabelecido na sessão 2. No momento em que a segunda sessão tentar realizar o bloqueio exclusivo, ela ficará aguardando a conclusão de holdlock da sessão 1, fechando o ciclo que ocasiona o deadlock.

         Os cenários 4, 5 e 6 estão corretos. Bloqueios Update (updlock) não são compatíveis entre si, ou seja, apenas uma sessão pode obter este bloqueio para o mesmo recurso – sessões concorrentes permanecerão aguardando a execução completa da primeira. O cenário 6 apresenta uma maneira interessante de se realizar “dois comandos em um”; observe que a variável @o_id_pedido é inicializada no próprio comando UPDATE. Num teste intenso de inserção de registros (quatro sessões inserindo um montante de 50.000 registros cada), o cenário 4 mostrou-se o mais bem sucedido.

         Cenários à parte, a verdade é que situações como essa podem e devem ser evitadas. Por exemplo, definir a coluna id_pedido na tabela Pedido como auto-incremento resolveria o problema de forma mais simples (elimina-se a tabela Parametro) e limpa (menos código).

Uma alternativa otimista

         Uma opção ao uso de hints para contornar o efeito de bloqueios em comandos SELECT, pode ser obtida com colunas TimeStamp. Esse tipo de dado possui a característica especial de versionar registros: sempre que uma linha é modificada, a coluna TimeStamp recebe automaticamente um novo valor. A Microsoft garante que valores TimeStamp não se repetem num mesmo database. Portanto, um meio eficiente de verificar se um registro sofreu algum tipo de alteração por sessões concorrentes é armazenar, no ínicio da transação, o valor de TimeStamp em uma variável. No momento do UPDATE, podemos comparar o valor lido com o atual, verificando se o registro foi modificado por outro processo. Nesse caso, invalidamos a transação com um rollback e a reiniciamos. Essa abordagem (também conhecida por bloqueio otimista) subentende que processos conflitantes são exceção e considera que submeter novamente a transação é mais interessante do ponto de vista de concorrência e economia de recursos do que forçar um bloqueio mais rigoroso.

A listagem 2 demonstra um exemplo desse cenário. Observe que utilizamos a função não documentada TSEqual, que retorna o erro #532 se os valores de TimeStamp passados como parâmetro forem diferentes. Note que a transação na sessão 1 é submetida duas vezes, em função da alteração concorrente realizada na sessão 2.

Listagem 2: Simulação de transação com bloqueios otimistas

Sessão 1: Executar o batch abaixo                

use NorthWind

go

alter table Orders add TS timestamp not null

go

declare @ts timestamp

declare @contador_trans int

set @contador_trans=1

SUBMETER_TRANS:

begin tran

  select @ts=TS from Orders where OrderId=10248

  waitfor delay '00:00:30' -- tempo de espera, para que a sessão 2 termine primeiro

  update Orders

    set OrderDate=getdate()

  where OrderId = 10248 and TSEqual(ts,@ts)

  if @@error = 532

  begin

     rollback tran

     set @contador_trans=@contador_trans+1

     if @contador_trans <=3

     begin

        print 'Submetendo novamente a transação ...'

        goto SUBMETER_TRANS

     end

     else goto FIM_ROLLBACK

  end

FIM_COMMIT:

if @@trancount > 0 COMMIT

RETURN

FIM_ROLLBACK:

  raiserror ('Processo de atualização da tabela Orders abortado!',11,1)

RETURN

Sessão 2: Executar em paralelo o batch

  update Orders

    set OrderDate=getdate()

  where OrderId = 10248

Em termos práticos, podemos substituir hints que degradam performance (holdlock, updlock, xlock)  pela checagem adicional da versão de colunas TimeStamp em comandos UPDATE.

         Exceção deve ser feita à utilização de hints em operações modificativas que afetam grande número de registros. Nesse caso, o uso de hints apropriados pode trazer ganhos significativos de performance. Por exemplo, veja o comando abaixo:

UPDATE Orders SET OrderDate = getdate()

Se paralelamente executarmos o comando sp_lock, veremos uma infinidade de bloqueios produzidos pela sentença (tabela 2). Nesse caso, seria mais econômico utilizar o hint TabLock, conforme a linha a seguir:

UPDATE orders with (tablock) SET orderdate=getdate()

Com este hint, apenas um bloqueio de tabela é gerado, conforme mostra a tabela 3.

Tabela 2: UPDATE executado para todos os registros da tabela Orders

Tabela 3: UPDATE realizado com hint

Monitorando Bloqueios

         Monitorar bloqueios significa estar atento a processos que impedem a execução normal de outros processos. Algumas características que tornam os processos lentos são:

·         Uso inadequado de predicados nas queries, impossibilitando a utilização de índices eficientes;

·         Níveis de isolamento inadequados, que prolongam a duração de bloqueios;

·         Transações excessivamente longas;

·         Incidência de deadlocks.

         O SQL Server disponibiliza duas procedures e dois utilitários para ajudar na identificação de processos causadores de gargalo, conforme descrito a seguir.

Procedure SP_LOCK

         Fornece um retrato detalhado dos locks ativos no servidor. Veja a tabela 4.

Tabela 4

spid   dbid   ObjId     IndId  Type Resource         Mode      Status

------ ------ ----------- ------   ------- ---------------- --------    -----------

55     6      0                  0      DB                                   S     GRANT

55     6      21575115    0      RID   1:205:0                  S      WAIT

55     6      21575115    0      PAG  1:205                    IS      GRANT

55     6      21575115    0      TAB                               IS      GRANT

56     22     0                 0      DB                                   S      GRANT

57     6       0                 0      DB                                   S      GRANT

57     6      21575115    0      TAB                               IX     GRANT

57     6      21575115    6      KEY  (430259d8c611)    X     GRANT

57     6      21575115    0      PAG  1:205                    IX      GRANT

57     6      21575115    6      PAG  1:312                    IX      GRANT

57     6      21575115    0      PAG  1:313                    IX      GRANT

57     1      85575343    0      TAB                               IS      GRANT

57     6      21575115    6      KEY  (7e01d7d31932)   X       GRANT

57     6      21575115    0      RID  1:205:0                   X      GRANT

Significado das colunas retornadas pela execução do comando:

·         spid: id da sessão relacionada ao bloqueio, pode estar ativo ou passivo. O spid ativo é o responsável pelo bloqueio, sinalizado com Status=GRANT. Ele será passivo quando estiver aguardando pela liberação do recurso bloqueado (Status=WAIT);

·         dbid: id do database onde o bloqueio está sendo mantido. O id de um database pode ser obtido pela função db_id(‘<NOME-DO-DATABASE>’);</NOME-DO-DATABASE>

·         ObjId: id do objeto que detém o bloqueio. Para saber o nome do objeto bloqueado utilize a função object_name(<ID>)</ID> no database responsável pelo bloqueio;

·         IndId: id do índice onde o bloqueio está sendo executado;

·         Type: sigla do recurso onde o bloqueio está sendo executado (tabela 1);

·         Resource: nome interno do recurso;

·         Modo: tipo de bloqueio que está sendo aplicado (S: Shared, IS: intent shared, X: exclusive, IX: intent exclusive, SIX: intent with shared exclusive);

·         Status: posição do spid em relação ao bloqueio; se estiver aguardando a liberação de um recurso o status será WAIT; se for o responsável pelo bloqueio o status será GRANT

Acionar a procedure sp_lock para rastrear processos bloqueados pode se tornar uma tarefa

complexa, principalmente quando você recebe ligações de vários setores da empresa

informando que “o sistema está travado”.

De forma geral, é necessário identificar as sessões cujo status seja wait,  procurar por

sessões com status grant para o mesmo objeto e finalizá-las, para liberação do bloqueio.

Procedure  SP_WHO

     A procedure sp_who (existe também uma versão não documentada com pequenas variações – sp_who2) fornece uma maneira mais amigável para se identificar sessões responsáveis por travamentos, apresentando uma relação de todos os processos ativos no servidor. Valores diferentes de zero na coluna blk sinalizam o spid responsável pelo bloqueio. Na tabela 5, vemos o spid 55 travado pela sessão 57. Repare que, na linha da sessão 57, o status do processo é Runnable e a coluna Blk apresenta-se zerada, informando que ela possui bloqueios ativos. 

Tabela 5: Resultado da execução do comando sp_who

     No contexto representado nesta tabela, o problema pode ser facilmente resolvido: basta identificar na coluna Cmd o processo que está em andamento e, se necessário, finalizá-lo com o comando kill (kill 57). Imagine agora um ambiente mais robusto, com 100 usuários ativos no sistema e o travamento da sessão 55 causado pela sessão 57, a sessão 57 bloqueada pela 198, que está bloqueada pela 177, que está bloqueada pela 45 ...; enfim, existe um sem número de sessões travadas.

     Em situações desse tipo, uma alternativa eficaz para identificar sessões responsáveis por bloqueios em cadeia é executar uma consulta à tabela SysProcesses, localizada no database master (responsável pelo registro de todos processos ativos no servidor). Com base nessa tabela, basta formular uma query que liste os processos em execução (blocked=0) responsáveis por bloqueios em outras sessões (blocked <> 0) (listagem 3).

Listagem 3

   select spid, blocked, left (hostname,20) as HostName_SPID,

             waitTime_Seg = convert(int,(waittime/1000)), open_tran, status,

             left(program_name,30) as program_name

   from master.dbo.sysprocesses

   where spid in (select blocked from master.dbo.sysprocesses where blocked > 0 )

      and blocked = 0

   order by spid

Monitorando bloqueios com Windows 2000 System Monitor

     O Windows 2000 System Monitor (ou Performance Monitor nas versões anteriores ao Windows 2000) fornece alguns contadores para análise geral de bloqueios. Pode-se saber, por exemplo, que o tempo médio para espera de bloqueios aumentou em 1 segundo desde a última verificação. Para uma análise nesse nível torna-se fundamental a manutenção de um baseline, sem o qual é impossível estabelecer parâmetros razoáveis de boa performance. Os contadores mais interessantes são Average Wait Time (tempo médio de espera, em milissegundos, para uma requisição de bloqueio) e Number of DeadLocks /seg (número de deadlocks por segundo). Esse utilitário está disponível no menu Start\Programs\Administrative Tools\Performance (figura 2).

NOTA: Manter um baseline implica definir uma série de índices para controle de performance, identificando variações em análises periódicas.

Monitorando bloqueios com o Profiler do SQL Server

     O SQL Server Profiler mantém a classe Locks para monitoramento de bloqueios. É adequada a utilização dos eventos Lock: DeadLock e Lock:DeadLock Chain para rastrear sessões envolvidas em deadlocks e Lock:TimeOut para investigar quais processos estão relacionados ao erro #1222: “Lock request time out period exceeded.”. Esse utilitário está disponível em Start\Programs\Microsoft SQL Server\Profiler (figura 3).

Conclusão

O uso racional de bloqueios é equivalente a performance. Entender como agem os bloqueios no servidor é um passo muito importante para que as aplicações se tornem mais leves e aptas a tratar processos concorrentes de maneira eficaz.

Figura 2: Windows 2000 System Monitor

Figura 3: Profiler do SQL Server 2000

Tabela 1

BOX: Problemas decorrentes do uso incorreto de isolamento

BOX: Tipos de Hint

Hints que alteram o nível de isolamento de uma transação

Hints que alteram a granulidade das transações

Hints que alteram o tipo de bloqueio nas transações