READ COMMITTED SNAPSHOT ISOLATION – Aprecie com moderação

“Não existe almoço grátis”…

“Muito cuidado com as armadilhas do RCSI”…

O início proposital deste post é para demonstrar alguns dos benefícios e também os pontos de atenção com a possibilidade da alteração do comportamento padrão de uma base de dados no SQL Server, quando se trata de concorrência e isolamento das transações.

Por padrão, o SQL Server tem um comportamento “Pessimista”, onde o nível de isolamento padrão é o “Read Committed”, o que basicamente faz com que o SQL Server tenha um número maior de locks, reduzindo a concorrência entre as transações.

Clique aqui, para um overview dos Níveis de Isolamento no SQL Server.

MUITO BOM,

Resumidamente, o Read Committed Snapshot Isolation (RCSI), trabalha com o versionamento de registros, utilizando-se da tempdb para tal, fazendo com que o comportamento “Pessimista” do nível de isolamento “Read Committed” onde Leitores (Select) bloqueiam Escritores (Insert, Update e Delete), e vice-versa, seja substituído por padrão na base em que foi ativado o RCSI para o comportamento “Otimista”, onde Leitores (Select) e Escritores (Insert, Update, Delete) não se bloqueiam.

Quando há concorrência entre um Updates e Selects submetidos a um mesmo registro, é gerado uma versão comitada do registro, que então esta é lida da pela transação que entrou em segundo lugar.

Esta alteração do corportamento Pessimista para Otimista, pela habilitação do RCSI não acaba com bloqueios entre escritores (update x update) e também não evita bloqueios onde há verificação de constraints (foreign Keys, por exemplo).

Quando leva-se em consideração o Read Committed Snapshot Isolation Level apenas até os pontos aqui exibidos, não há mais o que se pensar, pode se ter a falsa ideia de solução para todos os problemas de concorrência e bloqueios e a ideia de ativá-lo imediatamente em todas as bases SQL Server da instância é demasiadamente tentadora.

Para Exemplificar melhor, segue uma citação de quando o problema é solucionado habilitando-se o RCSI.

Cenário:

• Na padaria do Sr. Joaquim, são produzidos 1.000.000 de pães por dia, e o sistema de panificação automaticamente faz um update na tabela de pães produzidos naquele dia. Esporadicamente o Sr. Joaquim gosta de tirar relatórios para saber a quantidade produzida até então, porém, seus relatórios ficam bloqueados por muito tempo, visto que os updates são constantes e demorados.
  

RCSI no caso do Sr. Joaquim

• Neste caso específico, a habilitação do RCSI pode minimizar drasticamente os problemas do Sr. Joaquim com a lentidão de seus relatórios, uma vez que seus “selects” não mais esperarão pelos “updates” automáticos realizados pelo moderno sistema de fabricação de pães.
  
• Os updates continuam sendo realizados com a mesma frequência, e apenas bloqueando-se entre si quando necessários (lembrando que o RCSI não evita bloqueios entre updates), e quando o Sr. Joaquim submeter um select envolvendo os registros bloqueados, ele receberá o resultados dos últimos comitados e não serão considerados aqueles com transações em aberto.
  

MAS…

Porém, como mencionado no início deste post…

“Não existe almoço grátis”…

“Muito cuidado com as armadilhas do RCSI”…

Adiciono mais uma…

“Coisas estranhas podem acontecer”

Não são raras às vezes em que os bloqueios e espera são benéficos e necessários, para se evitar erros de negócios ou até mesmo inconsistências no valor dos dados de sua base.

O Read Committed Snapshot Isolation, é uma excelente opção, fantástica e que é passível sim, de ser habilitada na maioria das bases de dados da maioria dos ambientes com SQL Server, porém é necessário um levantamento cauteloso dos pontos onde podem incorrer problemas pela habilitação deste “comportamento” de uma base de dados do SQL Server.

Um dos maiores pontos de atenção é que o RCSI é habilitado para a base inteira, fazendo com que o nível de isolamento das transações para esta base, seja por padrão alterado. Para que se tenha níveis de isolamento diferentes, é necessário que estes sejam informados dentro das transações submetidas.

Vale ressaltar que apesar dos cuidados, custos e advertências supracitados, normalmente a relação CUSTO X BENEFÍCIO vale a pena e os problemas em potencial são poucos, em relação aos benefícios com o uso do RCSI.

Para facilitar um pouco o entendimento, segue uma descrição de um caso onde o RCSI pode causar um grande problema.

 Cenário:

• Uma empresa tem um programa conjunto entre as áreas Financeira e RH, de empréstimo de dinheiro da própria empresa a seus empregados, com desconto em folha de pagamento. As regras para que o colaborador possa pegar dinheiro, são bem simplistas e a principal delas, é que o e mesmo esteja empregado.
  

O problema:

• João Castro, trabalhou por 10 anos na empresa referida, e um belo dia foi chamado pelo RH que lhe agradeceu pelos serviços e o demitiu. Sabendo João que a empresa tinha o programa de empréstimos e que o Depto Financeiro era logo na sala ao lado, passou diretamente no financeiro e pegou 20 mil reais que foi prontamente liberado. João acabava de ser demitido e receber 20 mil reais em empréstimo, que não pagaria nunca, visto que não teria mais vínculo com a empresa.
  

RCSI neste caso:

• Supondo que existam 2 Tabelas envolvidas no processo:
  
  • TB_PESSOA
    
  • TB_EMPRESTIMO
    
• O processo de empréstimo verifica se a pessoa está empregada ou não (TB_PESSOA) para liberar o dinheiro (TB_EMPRESTIMO).
  
• Hipoteticamente falando, João por demitido por alguém do RH que abriu o sistema para atualizar o status para “DEMITIDO”, mas antes de executar o commit da transação, resolveu ir tomar um café e a deixou aberta. João foi ao Depto Financeiro, alguém deste departamento abriu uma transação para liberar empréstimo, foi consultar na tabela TB_PESSOA se João estava empregado e recebeu como resultado “EMPREGADO”, visto que uma transação de update já estava aberta, porém não havia sido comitada. Quando o alguém do RH voltou, salvou via sistema a demissão de João, alterando seu status para “DEMITIDO”, porém o campo SITUACAO_EMPRESTIMO na tabela TB_EMPRESTIMO estava como “LIBERADO”.
  

Neste caso específico, o bloqueio deveria acontecer e a transação de liberação do empréstimo obrigatoriamente deveria ser bloqueada pela alteração na tabela que informa se a pessoa está empregada ou não.

Ainda assim, este não seria um empecilho para a habilitação do RCSI na base, visto que é possível forçar com um HINT que a espera aconteça.

Foram criados Scripts para demonstração do caso citado que podem ser baixados – CLIQUE AQUI.

Ou visualizados no trecho de código abaixo.

–CRIAÇÃO DO AMBIENTE PARA OS TESTES

–CRIAÇÃO DA BASE

USE master

GO

IF db_id(‘DEMO_RCSI’) IS NOT NULL
DROP DATABASE DEMO_RCSI
GO

CREATE DATABASE DEMO_RCSI
ON PRIMARY

(NAME = ‘DEMO_RCSI_Data’,FILENAME = ‘C:TEMPDEMO_RCSI.mdf’,SIZE = 128 MB) 

LOG ON

(NAME = ‘DEMO_RCSI_Log’, FILENAME = ‘C:TEMPDEMO_RCSI.ldf’,SIZE = 128 MB)

GO

USE DEMO_RCSI
GO

–CRIA A TABELA PESSOA

IF object_id(‘TB_PESSOA’) IS NOT NULL

DROP TABLE TB_PESSOA
GO

CREATE TABLE TB_PESSOA
(
ID_PESSOA INT IDENTITY CONSTRAINT PK_TB_PESSOA_ID_PESSOA PRIMARY KEY 

,DATA_CONTRATACAO DATE DEFAULT GETDATE()

,NOME_PESSOA VARCHAR(60)

,BOL_EMPREGADO CHAR(1) 

)
GO

–CRIA A TABELA EMPRESTIMO
IF object_id(‘TB_EMPRESTIMO’) IS NOT NULL

DROP TABLE TB_EMPRESTIMO

GO

CREATE TABLE TB_EMPRESTIMO

(

ID_EMPRESTIMO INT IDENTITY CONSTRAINT PK_TB_EMPRESTIMO_ID_EMPRESTIMO PRIMARY KEY 
,ID_PESSOA INT –CONSTRAINT FK_TB_PESSOA_TB_EMPRESTIMO FOREIGN KEY REFERENCES TB_PESSOA (ID_PESSOA) 
,VALOR_EMPRESTIMO DECIMAL(10,2)
,LIBERACAO_EMPRESTIMO CHAR(1)

)

–EXISTE O NÍVEL DE ISOLAMENTO

–EXIBE INFORMAÇÃO SOBRE O NIVEL DE ISOLAMENTO ATUAL 

SELECT    NAME

,CASE is_read_committed_snapshot_on

             WHEN 1 THEN ‘ENABLED’

             WHEN 0 THEN ‘DISABLED’

         END
AS ‘Read_Committed_Snapshot’

FROM SYS.DATABASES

WHERE NAME = ‘DEMO_RCSI’

–CARREGA DADOS NA TABELA PESSOA
INSERT INTO TB_PESSOA (NOME_PESSOA, BOL_EMPREGADO)

VALUES (‘JOAO CASTRO’,‘S’)

      ,(‘MANOEL MAIA’,‘S’)

      ,(‘BENEDITO LIMA’,‘N’)

      ,(‘MARIA CASTRO’,‘N’)

      ,(‘CLEONTINA OLIVEIRA’,‘S’)

      ,(‘CANDIDO DUARTE’,‘S’)

–ALTERA O NÍVEL DE ISOLAMENTO PARA RCSI
USE master

ALTER DATABASE DEMO_RCSI SET READ_COMMITTED_SNAPSHOT ON

–EXIBE DADOS DA TABELA PESSOA

SELECT * FROM TB_PESSOA

–ID_PESSOA    DATA_CONTRATACAO    NOME_PESSOA            BOL_EMPREGADO
–1            2013-07-27          JOAO CASTRO            S
–2            2013-07-27          MANOEL MAIA            S
–3            2013-07-27          BENEDITO LIMA          N
–4            2013-07-27          MARIA CASTRO           N
–5            2013-07-27          CLEONTINA OLIVEIRA     S
–6            2013-07-27          CANDIDO DUARTE         S

–ALTERA NÍVEL DE ISOLAMENTO
–ALTER DATABASE DEMO_RCSI SET READ_COMMITTED_SNAPSHOT ON
–ALTER DATABASE DEMO_RCSI SET READ_COMMITTED_SNAPSHOT OFF

/*——————————————————————————*/
TRANSAÇÃO T1

–TRANSAÇÃO DE DEMISSÃO (T1)
–EXIBE INFORMAÇÃO SOBRE O NIVEL DE ISOLAMENTO ATUAL

SELECT    NAME

,CASE is_read_committed_snapshot_on

      WHEN 1 THEN ‘ENABLED’

      WHEN 0 THEN ‘DISABLED’

END AS ‘Read_Committed_Snapshot’

FROM
SYS.DATABASES
WHERE NAME = ‘DEMO_RCSI’

— PARA ALTERAR PARA O RCSI
–USE MASTER
–ALTER DATABASE DEMO_RCSI SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

–INICIO DA TRANSAÇÃO DE UPDATE

BEGIN TRAN T1
SELECT * FROM TB_PESSOA
WHERE NOME_PESSOA = ‘JOAO CASTRO’

USE DEMO_RCSI
GO

UPDATE P
SET P.BOL_EMPREGADO = ‘N’
FROM TB_PESSOA P
WHERE NOME_PESSOA = ‘JOAO CASTRO’
GO

SELECT * FROM TB_PESSOA
WHERE NOME_PESSOA = ‘JOAO CASTRO’

–ROLLBACK TRAN T1
–COMMIT TRAN T1

–ABRA A SEGUNDA TRANSAÇÃO EM UMA NOVA JANELA (NEW QUERY)E FAÇA A OPERAÇÃO DE EMPRESTIMO (T2)

/*——————————————————————————*/

TRANSAÇÃO T2

–TRANSAÇÃO DE EMPRÉSTIMO (T2)
—- GARANTIR QUE ESTEJA COM RCSI HABILITADO
–USE MASTER
–ALTER DATABASE DEMO_RCSI SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
–GO
——————————————————————————————

–INICIO DA TRANSAÇÃO DE UPDATE

BEGIN TRAN T2
USE DEMO_RCSI
GO

INSERT INTO TB_EMPRESTIMO
SELECT ID_PESSOA, ‘20000’,‘S’
FROM TB_PESSOA P
WHERE NOME_PESSOA = ‘JOAO CASTRO’
COMMIT TRAN T2

——————————————————————————————
SELECT * FROM TB_EMPRESTIMO
SELECT * FROM TB_PESSOA
——————————————————————————————

–VEJA O RESULTADO ANTES DO COMMIT DA T1 (TRANSAÇÃO DE DEMISSÃO)
SELECT P.ID_PESSOA, P.NOME_PESSOA, E.VALOR_EMPRESTIMO, P.BOL_EMPREGADO, E.LIBERACAO_EMPRESTIMO
FROM TB_EMPRESTIMO E
JOIN TB_PESSOA P
ON E.ID_PESSOA = P.ID_PESSOA 

——————————————————————————————

–EXECUTE O COMMIT DA TRANSAÇÃO T1 E RODE NOVAMENTE O RESULTADO. OBSERVER QUE HÁ ERRO DE NEGÓCIO
–POIS FOI LIBERADO EMPRÉSTIMOI PARA UMA PESSOA QUE NÃO ESTÁ EMPREGADA.

SELECT P.ID_PESSOA, P.NOME_PESSOA, E.VALOR_EMPRESTIMO, P.BOL_EMPREGADO, E.LIBERACAO_EMPRESTIMO
FROM TB_EMPRESTIMO E
JOIN TB_PESSOA P
ON E.ID_PESSOA = P.ID_PESSOA

/*——————————————————————————*/

RESUMINDO:

Prós do Read Committed Snapshot Isolation:

• Reduz consideravelmente problemas provenientes de bloqueios.
• Habilitação fácil e rápida.

Contras do Read Committed Snapshot Isolation:

• Não é útil em bloqueios entre transações de update.
• Bloqueios com verificação de constraints também não são beneficiados.
• Pode resultar com algum erro negocial não esperado, caso não seja bem mapeado.

Conclusão:

O RCSI é passível de utilização em qualquer ambiente, desde que os “porens ” sejam bem mapeados e os erros provenientes sejam mitigados, com o uso de HINTS ( Ex: (SELECT * FROM TABELA WITH (READCOMMITTED)), Alterando o nível de isolamento nas transações que necessitam que os bloqueios aconteçam entre outros meios que podem ser utilizados.

READ COMMITTED SNAPSHOT ISOLATION – APRECIE COM MODERAÇÃO

Referências

• http://msdn.microsoft.com/pt-br/library/ms173763.aspx
  
• http://msdn.microsoft.com/en-us/library/tcbchxcb(v=VS.80).aspx
  

Grande abraço,

Edvaldo Castro

MCITP | MCTS | MCT | MTA | MTAC | CCSQLA | TOEIC