Atividade de Laboratório - Controle de Concorrência

Nesta atividade de laboratório você irá aprender a utilizar os mecanismos de controle de concorrência fornecidos pelo SQL Server.

Uso de Protocolo de Bloqueio no SQL Server

O SQL Server implementa automaticamente um protocolo de controle de concorrência usando bloqueio. Este protocolo pode ser configurado pelo usuário de acordo com suas necessidades. O usuário pode, por exemplo, indicar o grau de isolamento desejado entre transações, definindo como será feito o bloqueio dos dados:

SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE      
                                | REPEATABLE READ    
                                | READ COMMITTED    
                                | READ UNCOMMITTED }

• SERIALIZABLE: Só permite que escalas serializáveis sejam executadas. Bloqueia os dados até o final da transação, e impede a inserção de novas tuplas nas tabelas em uso. É o modo mais restritivo, ou seja, o que resulta em menor concorrência.
• REPEATABLE READ: Bloqueia os dados até o final da transação, garantido que os valores dos dados acessados não serão modificados por outras transações, mas permite a inserção de tuplas na tabela, podendo originar "tuplas fantasma".
• READ COMMITED: Bloqueios são usados durante o acesso ao dado. Com isso, os valores dos dados acessados podem ser modificados por outras transações, e podem surgir "tuplas fantasma". É a opção default.
• READ UNCOMMITED: Não usa bloqueio, podendo resultar em leitura de dados sendo escritos (dirty read), valores dos dados acessados podem mudar durante a transação, e tuplas podem ser inseridas ou removidas durante a transação. É o modo menos restritivo, que resulta em maior concorrência, mas que deve ser usado somente quando não se tem nenhum compromisso com a consistência do resultado.

O SQL Server permite também que o usuário configure o tempo que uma transação pode ficar bloqueada esperando para acessar um dado. Por default, o tempo de espera (timeout) é ilimitado (valor -1). Você pode modificá-lo com o comando:

SET LOCK_TIMEOUT 1000

Agora qualquer transação que ficar bloqueada por mais de 1 segundo será abortada automaticamente, e o SQL Server retornará um erro.

O valor atual do timeout pode ser verificado com o comando:

SELECT @@LOCK_TIMEOUT

Os bloqueios ativos em um determinado instante podem ser visualizados executando a Stored Procedure sp_lock, ou através do Enterprise Manager, conectando ao servidor e selecionando Management -> Current Activity -> Locks / Object.

Exercício 1

Simule uma situação de bloqueio usando a base de dados com clientes de um banco que foi criada anteriormente. Para tal, escreva duas transações em janelas diferentes do Query Analyzer: uma que altere e outra que leia um mesmo dado de uma tabela. Execute as duas transações concorrentemente - primeiro a de escrita, sem fazer o commit, e depois a de leitura - de modo a ocasionar o bloqueio desta última.

Execute novamente as transações especificando um timeout para o bloqueio, e verifique o que ocorre. Tente também modificar o grau de isolamento e observe o comportamento das transações.

Registre na folha de respostas as transações usadas, a ordem na qual estas foram executadas e descreva o que aconteceu ao estipular o timeout e ao modificar o grau de isolamento.

Ao final do exercício, restaure os valores default do grau de isolamento (READ COMMITTED) e do timeout (-1) para não afetar o resultado do próximo exercício.

Detecção e Recuperação de Deadlocks

O protocolo de bloqueio usado pelo SQL Server não impede que ocorram deadlocks. No entanto, o SQL Server detecta e recupera deadlocks automaticamente, escolhendo a transação que está em espera a menos tempo para ser abortada.

O usuário pode modificar a forma como o SQL Server escolhe a transação a ser abortada atribuindo prioridades às transações. Prioridades são usadas para definir a transação que será abortada caso ocorra um deadlock.

Prioridades são atribuídas com o comando:

SET DEADLOCK_PRIORITY { LOW | NORMAL }

O valor default é NORMAL. A transação de prioridade mais baixa é abortada em caso de deadlock.

Exercício 2

Simule uma situação de deadlock usando o banco de dados dos clientes do banco. Observe qual transação é abortada. Atribua prioridades às transações de modo a trocar a transação que será abortada pelo SQL Server.

Registre na folha de respostas as transações utilizadas para simular o deadlock e descreva o ocorrido durante a execução.

Recuperação de Transações

O SQL Server usa logs com checkpoints para fazer a recuperação da base de dados. Para otimizar o acesso ao disco, as modificações nas bases de dados efetuadas pelas transações são adiadas, sendo efetuadas apenas quando ocorrer o próximo checkpoint.

Quando uma transação é efetivada, o SQL Server grava no log um registro de início da transação e as alterações nos valores dos dados efetuadas pela transação, seguidas de uma indicação de final da transação.

No instante em que ocorre um checkpoint, as alterações nos valores dos dados feitas por transações efetivadas desde o checkpoint anterior são gravadas no disco, e o checkpoint é marcado no log. Checkpoints ocorrem periodicamente e quando os comandos ALTER DATABASE, CHECKPOINT e SHUTDOWN são executados.

Em caso de falha, a base de dados é recuperada, refazendo as transações efetivadas logo após o último checkpoint e ignorando as alterações registradas no log referentes a transações não efetivadas. Este procedimento mantém a consistência das bases de dados.