No artigo anterior falamos sobre os níveis de isolamento e tipos de bloqueios. Dando continuidade, falaremos sobre concorrência otimista e pessimista. A intenção é mostrar alguns pontos das duas concorrências. Vale ressaltar que, a concorrência pessimista é o default no SQL Server.

Concorrência Pessimista:

Um leitor (SELECT) bloqueará os escritores (INSERT/UPDATE/DELETE) e o contrário é verdadeiro, uma vez que leitores adquirem SharedLocks (S) e escritores adquirem Exclusive Locks (X), ambos os bloqueios são incompatíveis entre si, ou seja, não se pode ler e gravar um registro ao mesmo tempo, caso isso ocorra, irá ocorrer Blocking.

Nessa concorrência, temos os seguintes níveis de isolamento:

• Read Commited:

Por padrão, o leitor (SELECT) é executado no nível de isolamento Read Committed, o que significa que o SQL Server adquire um Shared Locks (S) no momento que o registro é lido e ao finalizar a leitura o Shared Locks (S) será liberado imediatamente. Quando ocorre Scan numa tabela, apenas um Shared Locks (S) é mantido, dessa forma, é possível que alguma outra transação altere o registro posteriormente. Caso ocorra a leitura do mesmo registro dentro da mesma transação, teremos o isolamento Non-Repeatable Read em ação, onde lemos um registro várias vezes e teremos diferentes valores sendo retornados.

• Read Uncommitted:

É o menos restritivo, pois ele não adquire nenhum Shared Locks (S) no caso de leitura, sendo assim, lemos dados não confirmados de transações em andamento (Dirty Read). Dessa forma, é necessário analisar o quanto esse nível poderá lhe atender sem impactar a regra de negócios.

• Repeatable Read:       

Caso seja necessário um isolamento mais restritivo, podemos utilizar o Repeatable Read, que manterá o Shared Locks (S) até o fim de sua transação, dessa forma, não será possível adquirir um Exclusive Locks (X) e alterar o registro enquanto a leitura estiver dentro da transação. Nesse cenário, teremos valores corretos como retorno, mas aumentaremos os Blockings.

• Serializable:

 É o mais restritivo no SQL Server. Com esse isolamento, pode-se evitar o Phantom Records (Registro Fantasma), que é quando um registro aparece e desaparece ao recuperarmos registros de uma tabela várias vezes e, para evitar isso, o SQL Server utiliza a técnica Key Range Locking, bloqueando o intervalo de registros recuperado inicialmente. Portanto, com esse isolamento teremos bloqueio em qualquer processo de escrita até que a leitura seja finalizada. Caso não exista um índice para atender a sua leitura, a tabela será bloqueada por completo, causando assim impacto em todo processo que a utiliza.

Com todos os pontos indicados acima, temos o seguinte resultado:

Isolamento	Dirty Reads	Non-Repeatable Reads	Phanton Records
Read Uncommitted	Sim	Sim	Sim
Read Committed	Não	Sim	Sim
Repeatable Reads	Não	Não	Sim
Serializable	Não	Não	Não

Concorrência otimista:

Introduzido com o SQL Server 2005, visando melhorar problemas de bloqueio e questões de consistência de dados. Com isso, as consultas leem versões confirmadas de dados.

As versões antigas dos registros ficam armazenadas em uma parte da TempDB chamada de Version Store, onde a linha original é acrescida com ponteiro de 14 bytes, responsável por fazer referência com a versão antiga e, dependendo da situação, podemos ter mais de uma versão de registro armazenado.

Quando habilitado (por database), é recomendável reservar algum espaço nas páginas através de Fillfactor, utilizando um valor menor que 100%, dessa forma reduzimos as divisões das páginas devido os ponteiros de Version Store.

Nessa concorrência, temos os seguintes níveis de isolamentos:

• Read Committed Snapshot:

Altera o comportamento dos leitores no nível de isolamento Read Commited. Para habilitar este nível, é necessário escolher um momento de pouca utilização, pois é requirido um Exclusive Lock (X) no database e será bloqueado no caso de usuários conectados no banco de dados.

Os locks não ocorrem no nível de isolamento Read Commited, pois os leitores usam a versão antiga da linha que está no Version Store. Vale ressaltar que os escritores ainda adquirem Update Locks (U) e Exclusive Locks (X) da mesma forma que a concorrência pessimista. O lock entre escritores de sessões diferentes ainda existem, mas os escritores não bloqueiam leitores.

Nesse nível de isolamento, temos consistência completa, pois não são acessados dados não confirmados e nem os dados confirmados após o início da solicitação. Um ponto de atenção é que utilizar o hint NOLOCK nesse nível de isolamento é desnecessário.

O nível de isolamento Read Commited pode ser uma solução quando o sistema está sofrendo de problemas com locks, pois ele remove os locks entre escritores e leitores.

• Snapshot:

É um nível de isolamento de transação separado, necessitando ser habilitado por database e, diferente do Read Committed Snapshot, não gera lock a nível de database, dessa forma, pode ser habilitado a qualquer momento.

A consistência ocorre a nível da transação e sem locks, pois no momento que uma transação é iniciada, verá um snapshot.

Se tivermos uma transação efetuando um volume alto de escrita, teremos um aumento considerável na tempdb, pois todas as linhas precisam ser copiadas para o Version Store.

Mas nem tudo são flores nessa concorrência, pois a redução de locks tem seus custos, dentre eles, sobrecarga e aumento na tempdb, sobrecarga na utilização de CPU, sobrecarga de I/O e fragmentação de índices, pois quando uma linha é modificada temos um acréscimo de 14 bytes devido o ponteiro de controle de versão. Quando uma página de dados estiver compactada e a nova versão da linha não couber na página, teremos a divisão da página e assim, mais fragmentação. Os 14 bytes permanecerão na linha mesmo que o registro seja removido do Version Store, mas, com o processo rebuild, eles serão removidos.

Por hoje era isso, espero que tenham gostado deste conteúdo.

Até o próximo post, pessoal! 😊