Nesta atividade de laboratório você irá explorar os mecanismos de controle de concorrência fornecidos pelo SQL Server.
O SQL Server implementa protocolos de controle de concorrência usando bloqueio. O protocolo utilizado 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 }
Graus de isolamento também podem ser definidos para cada comando de uma transação, usando a cláusula WITH ao final do comando. Os valores aceitos são:
<comando SQL> WITH( { SERIALIZABLE | REPEATABLEREAD | READCOMMITTED | READUNCOMMITTED } )
Os graus de isolamento disponíveis são os seguintes:
É possível indicar ao SQL Server como este deve efetuar os bloqueios referentes a um comando SQL passando dicas (hints) ao SGBD da seguinte forma:
<comando SQL> WITH ( { NOLOCK | HOLDLOCK | NOWAIT | XLOCK } )
O SQL Server permite também que o usuário configure o tempo máximo que uma transação pode ficar bloqueada esperando para acessar um dado (timeout). Por default, o tempo máximo de espera é 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 indicará que ocorreu um erro de procesamento.
O valor atual do timeout pode ser verificado com o comando:
PRINT @@LOCK_TIMEOUT
Diferentes granularidades de bloqueio podem ser utilizadas para acessar o banco de dados. Para cada consulta processada, o SQL Server define automaticamente uma granularidade de bloqueio a ser usada. Em geral, quanto menores as porções bloqueadas no banco de dados, maior a concorrência, e maior o overhead causado por bloqueios na execução de transações. O nível de granularidade de bloqueio utilizado ao executar um comando pode ser definido da seguinte maneira:
<comando SQL> WITH ( { ROWLOCK | TABLOCK | PAGLOCK } )
Os bloqueios ativos em um determinado instante podem ser visualizados executando a stored procedure sp_lock, ou através do Management Studio, abrindo o Activity Monitor (na pasta Management do servidor, no Object Explorer).
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 de consulta diferentes do Management Studio: uma que altere e outra que leia um mesmo dado de uma tabela. Execute as duas transações concorrentemente - primeiro a transação de escrita, sem fazer o commit (ver figura abaixo) e depois a transação de leitura. Como o dado foi bloqueado pela primeira transação, a segunda transação não conseguirá acessar o dado e ficará bloqueada até que você volte à primeira janela e faça o commit.
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.
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.
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.
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.