Mais um velho problema de concorrência…

Boa Noite Pessoal,

Participando do fórum MSDN, vi uma Thread que me chamou atenção. O título da Thread é "Como retornar uma mensagem qdo um registro está lockado!" e reflete um velho problema de concorrência (http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=3634760&SiteID=21)

Como podemos ter o controle sobre os bloqueios em nível de registro ? Sabemos que o SQL Server gerencia bloqueios em vários níveis (banco, arquivos, tabelas, índices, páginas, etc) e que alguns objetos como sysprocesses, sp_lock, sys.dm_exec_requests, sys.dm_tran_locks, etc podem ajudar mas servem apenas para monitoramente e não para controle. Imaginemos a seguinte situação:

Uma aplicação Web recupera e atualiza dados de vários clientes simultâneos. Pode acontecer de dois atendentes (A1 e A2) recuperarem os dados do um Cliente (C1), fazerem atualizações divergentes e apenas um deles sairá vencedor. Suponha que A1 e A2 recuperem os dados e que A1 atualize o estado civil do Cliente C1 de solteiro para casado e que A2 atualize a renda do cliente de R$ 2.000,00 para R$ 3.000,00.

Se A1 vencer, C1 será casado mas possuirá renda de R$ 2.000,00. Se A2 vencer, C1 será solteiro mas possuirá renda de R$ 3.000,00. Nenhuma das situações é a ideal e revelam um problema clássico de concorrência.

Se a aplicação fosse Desktop, enquanto A1 estiver atualizando A2 fica bloqueado. Se A2 chegasse primeiro, A1 ficaria bloqueado. Seria possível detectar que existe um bloqueio, mas ainda que isso fosse possível não teríamos como saber que o bloqueio se deve a um registro específico (C1) e mesmo que conseguíssemos. Em uma situação WEB a conexão a banco de dados não pode ser mantida por muito tempo como uma aplicação Desktop.

Esse impasse é natural já que bloqueios no banco de dados são gerenciados pelo próprio SGBD e não podemos (e nem devemos) a priori influenciar em como o SGBD faz esse controle. O que é necessário não utilizar o mecanismo de bloqueios do SGBD mas sim gerenciar bloqueios em nível de aplicação.

O SQL Server possui duas procedures para tratar isso e que comumente são ignoradas (na verdade até certo tempo atrás eu as desconhecia também). Essas procedures conseguem impor um bloqueio em nível lógico podendo ser trabalhado no TSQL mas sem influenciar nos bloqueios impostos nas estruturas físicas do SQL Server. Elas funcionam como um bit lógico popularmente conhecido como flag. Essas procedures são a sp_getapplock e sp_releaseapplock. São boas para resolver problemas desse tipo, mas prefiro não trabalhar com elas já que serão removidas futuramente (Deprecated Features).

Uma das formas de resolver esse problema é a utilização de um tipo de dados que muita gente conhece mas pouca gente sabe pra que serve. O tipo de dados TIMESTAMP é ótimo para uma situação dessas. Esse tipo de dados é criado automaticamente quando o registro é inserido e alterado automaticamente quando o registro é alterado. Explicarei como ele pode nos ajudar para resolver esse problema. Vamos simular a situação descrita anteriormente. Primeiro rodemos um script para criar a referida tabela. Adicionei uma coluna chamada versão com tipo TimeStamp para utilizarmos para controle.

CREATE TABLE tblClientes (
    Codigo INT, Nome VARCHAR(20),
    EstadoCivil VARCHAR(20), Renda SMALLMONEY,
    Versao TIMESTAMP)

Logo em seguida, vamos popular a tabela com apenas um registro. Ele é o suficiente para que possamos trabalhar a situação que descrevi.

INSERT INTO tblClientes (Codigo, Nome, EstadoCivil, Renda)
VALUES (1, ‘Cliente 1’, ‘Solteiro’, 2000.00)

Para deixar um pouco mais profissional, serão criadas duas SPs. Uma para recuperar o registro e outra para alterá-lo. O código pode parecer um pouco estranho, mas as explicações virão até o término desse post.

— Recuperar o Cliente
CREATE PROCEDURE usp_RecuperaCliente
    @Codigo INT,
    @Versao TIMESTAMP OUTPUT
AS
SET
@Versao = (SELECT Versao FROM tblClientes WHERE Codigo = @Codigo)
SELECT
    Codigo, Nome, EstadoCivil, Renda
FROM
    tblClientes
WHERE
    Codigo = @Codigo
GO

— Alterar o Cliente
CREATE PROCEDURE usp_AlteraCliente
    @Codigo INT,
    @Nome VARCHAR(20) = NULL,
    @EstadoCivil VARCHAR(20) = NULL,
    @Renda SMALLMONEY = NULL,
    @Versao TIMESTAMP
AS
UPDATE
tblClientes SET
    Nome = ISNULL(@Nome,Nome),
    EstadoCivil = ISNULL(@EstadoCivil, EstadoCivil),
    Renda = ISNULL(@Renda,Renda)
WHERE
    Codigo = @Codigo AND Versao = @Versao
GO

Agora abra duas janelas no SQL Server Management Studio. Chamarei-as de J1 e J2 respectivamente. Utilizaremos para simular uma concorrência. Inicialmente utilizarei comandos SQL simples sem utilizar as SPs. Na janela J1 coloque o seguinte código:

UPDATE tblClientes SET EstadoCivil = ‘Casado’
WHERE Codigo = 1

Na janela J2 coloque o seguinte código:

UPDATE tblClientes SET Renda = 3.000
WHERE Codigo = 1

Se você executou o código de J1 primeiro, o cliente mudará o status para casado mas terá a renda de R$ 2.000,00. Se você executou o código de J2 primeiro, o cliente mudará a renda para R$ 3.000,00 mas permanecerá com o status civil de solteiro. O ideal seria que uma das janela (J1 ou J2) fosse avisada de alguma forma que não pode ou não deve prosseguir com a atualização. Vejamos como poderemos resolver esse impasse usando as procedures criadas. Tanto em J1 quanto em J2 execute o seguinte código:

DECLARE @VersaoRecuperada TIMESTAMP
EXEC usp_RecuperaCliente @Codigo = 1, @Versao = @VersaoRecuperada OUTPUT
SELECT @VersaoRecuperada

O resultado em ambas as janelas é exatamente o mesmo. A procedure recupera os dados do Cliente 1 e retorna como parâmetro de saída a "versão" atual do registro. Para verificarmos qual é a versão exibimos o conteúdo da variável @VersaoRecuperada

No próximo passo simularemos o teste de atualização. Cole o código abaixo na janela J1 para realizar a alteração do estado civil do cliente 1 mas ainda não execute.

— Recuperar os dados de Cliente
DECLARE @VersaoRecuperada TIMESTAMP
EXEC usp_RecuperaCliente @Codigo = 1, @Versao = @VersaoRecuperada
OUTPUT

— Verificar o valor do campo versão do Cliente 1
SELECT @VersaoRecuperada

— Aguarda 5 segundos para simular o delay de preenchimento do campo
WAITFOR DELAY ’00:00:05′

— Atualizar o Status do Cliente 1
EXEC usp_AlteraCliente @EstadoCivil = ‘Casado’,
@Codigo = 1, @Versao = @VersaoRecuperada

— Verificar os dados do Cliente 1
SELECT * FROM tblClientes WHERE Codigo = 1

Na janela 2, cole o código abaixo para realizar a alteração da renda do cliente 1 mas ainda não execute.

— Recuperar os dados de Cliente
DECLARE @VersaoRecuperada TIMESTAMP
EXEC usp_RecuperaCliente @Codigo = 1, @Versao = @VersaoRecuperada OUTPUT

— Verificar o valor do campo versão do Cliente 1
SELECT @VersaoRecuperada

— Aguarda 5 segundos para o delay de preenchimento do campo
WAITFOR DELAY ’00:00:05′

— Atualizar o Status do Cliente 1
EXEC usp_AlteraCliente @Renda = 3.000,
@Codigo = 1, @Versao = @VersaoRecuperada

— Verificar os dados do Cliente 1
SELECT * FROM tblClientes WHERE Codigo = 1

Execute o código da janela J1 e antes que o mesmo finalize execute o código da janela J2. Aguarde a finalização e observe os resultados. A alteração feita em J1 foi realizada enquanto a alteração feita em J2 não foi realizada. O que ocorreu foi o seguinte:

  1. J1 e J2 recuperaram o mesmo registro com o mesmo TIMESTAMP
  2. J1 atualizou o registro com base no código 1 e no TIMESTAMP recuperado. Como o registro não havia sido atualizado ainda, o TIMESTAMP era o mesmo que o recuperado. Após a atualização de J1, o TIMESTAMP foi alterado para um novo valor.
  3. J2 tentou atualizar o registro com base no código 1 no TIMESTAMP recuperado. Como J1 fez a atualização primeiro e mudou o TIMESTAMP, J2 não localizou o registro. Havia o registro com o código 1, mas com o outro TIMESTAMP e portanto não fez a atualização.

Isso mostra como lidar com problemas de concorrência em um nível lógico sem utilizar os recursos de bloqueio do banco de dados. Se essa implementação não fosse feita. J1 faria a atualização e teria a percepção de que o cliente 1 é casado com renda de R$ 2.000,00. Quando J2 fizesse o segundo UPDATE, iria aumentar a renda para R$ 3.000,00 com a percepção de que o cliente continuaria solteiro mas ele viria casado. Com o mecanismo de controle de concorrência baseado em TIMESTAMP podemos evitar esse problema.

Isso no entanto, leva a um inconveniente. J2 não saberá se não fez a atualização porque alguém atualizou o registro primeiro, ou se o mesmo foi excluído por exemplo. Para evitar esse inconveniente, é preciso alterar a SP para essa situação em particular.

ALTER PROCEDURE usp_AlteraCliente
    @Codigo INT,
    @Nome VARCHAR(20) = NULL,
    @EstadoCivil VARCHAR(20) = NULL,
    @Renda SMALLMONEY = NULL,
    @Versao TIMESTAMP
AS
UPDATE
tblClientes SET
    Nome = ISNULL(@Nome,Nome),
    EstadoCivil = ISNULL(@EstadoCivil, EstadoCivil),
    Renda = ISNULL(@Renda,Renda)
WHERE
    Codigo = @Codigo AND Versao = @Versao
IF (@@rowcount = 0) AND EXISTS (SELECT * FROM tblClientes WHERE Codigo = @Codigo)
    RAISERROR(‘O registro foi atualizado previamente. Tente novamente’,16,1)

Dessa forma, se nenhum registro for atualizado mas houver um cliente com o código especificado, um erro será retornado. Se os códigos de atualização forem executados após a alteração da SP, J2 receberá uma mensagem dizendo que em outras palavras, o registro que foi recuperado não exatamente o presente no banco de dados e que uma nova tentativa deve ser feita. Evidente que o tratamento de erro deve ser adequado para que vários usuários não tentem atualizar e receber a mesma mensagem indefinidamente.

Outra possibilidade é que caso a mensagem apareça, J2 seria perguntado se deseja efetuar as alterações mesmo com a mensagem. Dessa forma as alterações de J2 não seriam perdidas, mas ele seria notificado das possíveis conseqüências. Nessa situação específica isso pode ser desejável já que as atualizações de J1 e J2 não são conflitantes, mas nem sempre esse é o cenário mais comum.

O mecanismo apresentado é clássico na resolução de problemas de concorrência e pega emprestado alguns conceitos da concorrência otimista. Fiz todo o código baseado em TSQL, mas no mundo real, certamente o TIMESTAMP será recuperado para a camada do cliente e resubmetido após a alteração (por isso o DELAY de 5s).

Até a próxima pessoal,

[ ]s,

Gustavo

3 Respostas para “Mais um velho problema de concorrência…

  1. Pingback: SQL Server – TimeStamp « Guilherme Ferrera

  2. Gustavo parabéns pelo artigo, muito bem explicado e didático. Só uma dúvida como a procedure retorna o raiserror, no caso essa mensagem cairia no bloco catch de uma linguagem como java ou c# ne? Ai programador teria que tratar adequadamente pra passar a msg pro usuario?!

    • Oi Gerson,

      Sim. A idéia é essa mesma. Lançar uma exceção para que o banco possa sinalizar que a linha lida não é mais a mesma e assim as aplicações possam fazer o tratamento adequado.

      [ ]s,

      Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s