Configuração do Controle de Transações

Como padrão do Servidor do Banco de Dados ao criar-se um novo banco de dados o padrão do SQL Server é que o Lock em um simples select, enquanto que em outra transação não consegue acessar o dados até a transação que requisição inicial (select) seja liberada pelo servidor/cliente. Nas próximas versões do sistema o banco de dados será criado com o comando (descrito abaixo) e não há a necessidade de execução desse comando.

Para exemplificar os problemas que podem ocorrer com a abordagem podem ser mostrado a seguir:

Imagina uma tabela de nome de Entidade com cinco registros e existem duas transações... Uma está no processo de atualização e inserção de uma nova entidade. Antes de a transação concluir outro usuário acessa a entidade através do Select. Para simular inicie um debug em uma aba do SQL Server Management Studio e pare em Insert.. e depois entre abra uma nova aba e execute o comando da transação 2.

Transação 1

Begin transaction

        begin transaction
                update Entidade Set NomeFantasia = 'Alterado 123' where ID =1
        commit

        Insert Into Entidade (Id,NomeFantasia) values (70, 'Test')

         update Entidade Set NomeFantasia = 'Outra alteração' where ID = 2

commit

Transação 2

Select * From Entidade -- (Isso deve bloquear (lock) até que a atualização da transação 1 for resolvido )

A segunda transação irá trazer TODOS registros do banco de dados, mas somente quando a primeira transação for finalizada e isso causa lentidão e diversos outros problemas de performance no banco. Alguns para poder resolver isso usando o HINT with(lock) como alguns softwares fazem.

Mas existem uma outra forma usando um comando de configuração do próprio banco, alterando o ISOLATION LEVEL definindo no banco de dados.

Para realização desse commando se faz necessário que somente um usuário esteja conectado, o usuário da transação corrente, para saber se existem mais usuários use a procedure :

exec sp_who

Esse comando retorna todos os usuários conectados no sercidor.

Depois execute o segundo para alterar as configurações :

ALTER DATABASE [NomeDoBancoDeDados] SET READ_COMMITTED_SNAPSHOT ON;

Depois disso realize o mesmo procedimento da executado inicial em Transação 1 e Transação 2 e veja o SqlServer não irá bloquear o resultado do comando Transação 2.

Esse procedimento é aconselhado para TODOS os bancos de dados que existem no servidor, essa configuração ativa o modo ROW VERSION no controle de transações que é comum em outros servidores de banco de dados.

Para saber qual o nível que está configurado e execute o seguinte comando :

SELECT is_read_committed_snapshot_on,* FROM sys.databases WHERE name= 'NomeDoBancoDeados'

O resultado mostra as configurações do banco e uma das linhas mostram o ISOLATION LEVEL do banco de dados:

1 = READ_COMMITTED_SNAPSHOT opção está ATIVA.
0 = READ_COMMITTED_SNAPSHOT a opção está DESATIVADA (default).