SQL Magazine Edição 20

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

Gerenciamento de locks no SQL Server 2000

Eduardo Terra Morelli

Uma das grandes características do SQL Server em sua versão 2000 é a automatização de praticamente todos os ajustes internos. Desta forma, o DBA supostamente não deve se preocupar com questões como alocação de memória para páginas oriundas dos arquivos, dimensionamento de áreas para armazenamento de transações e controle de mecanismos de proteção de dados (para que duas transações não gravem o mesmo dado ao mesmo tempo), dentre outras. Entretanto, dois problemas são bastante comuns em ambientes onde muitos usuários trabalham de forma concorrente: espera e deadlocks (ler Nota 1). Para esses casos, se deixarmos a resolução por conta do gerenciador, podem ocorrer demoras indesejáveis e, conseqüentemente, grande volume de retrabalho.

 

Nota 1. Deadlock

Trata-se de um termo largamente utilizado em publicações técnicas em português, cuja tradução literal seria algo como “espera até a morte”, significando um impasse sem solução.

 

Este artigo visa esmiuçar a gerência de locks realizada pelo SQL Server 2000 para que seja possível tomar medidas que minimizem os problemas citados acima.

Espera-se que o leitor já tenha alguma vivência em SQL Server, conhecendo suas principais ferramentas tais como Query Analyzer ou Enterprise Manager.

Fundamentos

Os dois problemas que motivaram esta matéria, a espera (blocking) e deadlocks, costumam ser confundidos. O primeiro acontece quando um usuário deve aguardar enquanto outro “prende” recursos que ele precisa. Por exemplo, suponha dois usuários, Zebedeu e Epaminondas, que precisem alterar o mesmo dado ao mesmo tempo. Enquanto o primeiro atualiza, o segundo aguarda sua vez, já que um dado não pode ser alterado por mais de um usuário ao mesmo tempo.

O fato de um usuário esperar não chega a ser um problema, já que decorre da convivência entre vários usuários, ou mais tecnicamente, da concorrência. Mas, caso essas esperas comecem a acontecer com muita freqüência e com grandes durações, os usuários passarão a reclamar. Uma analogia para o fenômeno descrito seria um sinal de trânsito em um cruzamento movimentado. Enquanto os veículos de uma via passam, os da outra aguardam.

Já deadlock resulta em espera mútua sem perspectiva de solução. Por exemplo, analise o código proposto na Tabela 1.

 

Tabela 1. Duas conexões em espera mútua.

Conexão 1

Conexão 2

begin transaction

   update historico set nota = 10

   select * from alunos

begin transaction

   update alunos set tel_aluno = ''

   select * from historico

 

Supondo que cada comando dure o mesmo tempo e que, para cada instrução do primeiro usuário ocorra uma do segundo, a primeira conexão entrará em compasso de espera, pois a tabela ALUNOS encontra-se “presa” pela segunda conexão. O mesmo acontecerá com a segunda conexão, já que a tabela HISTORICO não está disponível. Este mesmo problema pode ser visualizado conforme demonstrado na Tabela 2.

Tabela 2. Ocorrência de deadlock.

Comando

Conexão

Observações

begin transaction

1

Inicia uma transação explicita (ler Nota 2). Futuras atualizações provocarão locks enquanto ela não for concluída.

begin transaction

2

Inicia outra transação.

update historico ...

1

Como são atualizadas todas as linhas, a tabela HISTORICO será bloqueada. Nenhuma conexão poderá atualizar esta tabela enquanto a transação não for concluída.

update alunos...

2

Bloqueia toda a tabela ALUNOS.

select * from alunos

1

Tenta ler a tabela ALUNOS, mas a tabela encontra-se bloqueada pela conexão 2.

Select * from historico

2

Tenta ler a tabela HISTORICO, mas a tabela encontra-se bloqueada pela conexão 1.

 

 

Nota 2. Transação explícita.

Cabe aqui uma observação quanto ao termo “explicito”, já que este não é o comportamento padrão do SQL Server. Normalmente, cada comando representa uma transação e, caso seja necessário reunir mais de um comando em uma transação, somos obrigados a utilizar a estrutura BEGIN TRANSACTION/ COMMIT ou ROLLBACK. Enquanto a primeira instrução inicia uma transação de forma explícita, a segunda e terceira finalizam, uma confirmando as alterações e outra desfazendo-as.

 

Após alguns instantes, o SQL Server identificará o problema e decidirá por desfazer uma das transações envolvidas no conflito. Os critérios para escolha da transação-vítima são norteados pela quantidade de esforço computacional envolvido para desfazê-la. Por exemplo, imagine que a primeira transação tenha atualizado duas linhas e a segunda, um milhão. Naturalmente, a segunda transação exigirá menos trabalho para ser desfeita.

A conexão cuja transação tenha sido desfeita receberá o seguinte erro:

 

Server: Msg 1205, Level 13, State 50, Line 1

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 

O deadlock acima é do tipo cycle, o mais comum. Ainda existe o conversion, mais raro, acontecendo quando duas transações possuindo locks de leitura sobre o mesmo recurso (linha, chave, página, tabela) tentam convertê-los, ao mesmo tempo, para lock de gravação (bloqueio exclusivo). Por exemplo, imagine que duas conexões executem ao mesmo tempo, o comando a seguir:

 

update historico set nota = 10 where matricula = 1

 

Ao executar um UPDATE com cláusula WHERE ocorre um SELECT implícito para localizar as linhas que devem ser alteradas. Enquanto consultas estiverem acontecendo em paralelo, não há conflitos. Mas quando acontecer a mudança de bloqueio do tipo leitura para o de gravação, ocorrerá o deadlock do tipo cycle, já que uma transação não poderá bloquear para atualização linhas que estejam com bloqueios de leitura causados pela outra transação. O impasse surge quando nenhuma das duas transações consegue obter seu tipo de bloqueio desejado.

Vale ressaltar que problemas de espera ou deadlock acontecem entre transações e não, como pôde parecer, entre usuários ou conexões. Também se deve frisar que, ao contrário do Oracle, o SQL Server trata os comandos como transações. Caso seja necessário agrupar mais de um comando em uma transação, deve-se utilizar os comandos BEGIN TRANSACTION, COMMIT/ROLLBACK. ...

Quer ler esse conteúdo completo? Tenha acesso completo