Piores práticas – Geração de seqüênciais baseados no MAX + 1

Boa Noite Pessoal,

Hoje falarei de mais uma prática ruim relacionada a banco de dados. Digo relacionada a banco de dados, porque o problema que irei descrever não é restrito ao SQL Server. Trata-se de um problema de concorrência que pode ocorrer em qualquer banco de dados e muitas vezes pode passar despercebido. Trata-se da geração de seqüênciais baseada no MAX + 1.

A utilização da técnica MAX + 1 é uma tradução lógica de um algoritmo de incremento. Uma vez que uma tabela tenha o último registro cujo ID seja igual a 3 por exemplo, bastaria capturar o último ID incrementá-lo de uma unidade obtendo-se assim o próximo ID que no exemplo seria o ID 4. O processo é repetido múltiplas vezes e obtêm-se o ID 5, 6, 7, 8 e assim sucessivamente.

Não há nada de errado com esse algoritmo. É uma forma bem prática de obter-se o próximo ID e assim incrementar automaticamente a coluna de ID, código ou qualquer outra que seja autonumerada. O empecilho está na concorrência. Utilizar esse recurso puramente da forma descrita é supor que apenas um usuário por vez utilizará a tabela. Isso é era bem verdade nos anos 80 e início dos anos 90 na qual muitas aplicações eram locais e monousuários (enquanto escrevo essa frase escuto aqui o single Save a Prayer do Duran Duran e acho que estou exatamente nessa época). Se uma aplicação está isolada e destinada à utilização por apenas um usuário, não há nenhum problema de concorrência e a lógica descrita funcionará perfeitamente. A questão é que não estamos mais nos anos 80 e hoje dificilmente se utiliza uma aplicação local e monousuário (durante essa frase já estou ouvindo o remix de Give It To Me da Madonna feito pelo Paul Oakenfold). As aplicações estão disponíveis em diversos pontos de acesso (estações de trabalho, servidores, dispositivos móveis, etc) e com uma quantidade cada vez maior de usuários simultâneos além de online fora de períodos comerciais. Em ambientes como esses, o MAX + 1 definitivamente não tem espaço (mesmo com as devidas adaptações).

Quem já passou pelos problemas do MAX + 1 sabe exatamente do que estou falando. Na verdade nem precisa passar por eles. Basta se perguntar o seguinte: "Se o próximo seqüencial é obtido através do MAX + 1, o que acontece se dois usuários lerem o mesmo registro exatamente na mesma hora ? Será que eles não vão obter o mesmo seqüencial ? Será que isso não vai dar nenhum problema ?". Simulemos então os problemas do MAX + 1 e apontemos às soluções. Nada melhor do que um script. Utilizarei duas conexões que chamarei respectivamente de C1 e C2. Essas conexões podem ser janelas do SQL Server Management Studio, SQLCMD ou alguma aplicação que acesse uma base SQL Server 2005. O primeiro passo é a criação de uma tabela que precise de um seqüencial. Irei utilizar uma fictícia tabela de pessoas.

— Criação da tabela tblPessoas
CREATE TABLE tblPessoas (IDPessoa INT NOT NULL, NomePessoa VARCHAR(50))
ALTER TABLE tblPessoas ADD CONSTRAINT PKPessoa PRIMARY KEY (IDPessoa)

Como há uma chave primária na coluna IDPessoa não podemos utilizar uma trigger tradicional para gerar o ID. Uma vez que a trigger é disparada após o INSERT ter sido realizado, o seqüencial precisa ser gerado antes do INSERT e não depois através da trigger. As saídas para isso é gerar uma procedure de inserção ou a utilização de um trigger INSTEAD OF já que o SQL Server ainda não possui triggers BEFORE. Utilizarei a trigger INSTEAD OF por entender que a geração do seqüencial deve ser garantida para todo o INSERT.

— Criação da trigger de geração de seqüenciais
CREATE TRIGGER trgSeqIDPessoa ON tblPessoas
INSTEAD OF INSERT
AS
BEGIN
    — Obter o maior ID (no caso do primeiro registro retornar zero)
    DECLARE @IDPessoa INT
    SET @IDPessoa = ISNULL((SELECT MAX(IDPessoa) FROM tblPessoas),0)
    SET @IDPessoa = @IDPessoa + 1

    — Obter o nome da Pessoa
    DECLARE @NomePessoa VARCHAR(50)
    SET @NomePessoa = (SELECT NomePessoa FROM INSERTED)

    — Gravar o registro na tabela de pessoas
    INSERT INTO tblPessoas VALUES (@IDPessoa, @NomePessoa)
END

Uma simples instrução INSERT executada algumas vezes pode demonstrar que a trigger funciona perfeitamente e que o campo IDPessoa é autoincrementado através das ações da trigger. Ex:

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 1’)
INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 2’)
INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 3’)

SELECT IDPessoa, NomePessoa FROM tblPessoas

O que aconterá por exemplo se dois usuários acessarem simultaneamente a base para inserir um novo registro ? Como será que a trigger irá se comportar ? Não há como simular duas inserções simultâneas já que entre as trocas de janela da primeira conexão para a segunda conexão o INSERT já terá sido feito. Para tornar essa simulação possível, o código da trigger será alterado para provocar um DELAY proposital de 5 segundos após a captura do seqüencial. Durante esse DELAY será possível trocar as conexões e simular dois usuários simultâneos.

— Alteração da trigger de geração de seqüenciais
ALTER TRIGGER trgSeqIDPessoa ON tblPessoas
INSTEAD OF INSERT
AS
BEGIN

    — Obter o maior ID (no caso do primeiro registro retornar zero)
    DECLARE @IDPessoa INT
    SET @IDPessoa = ISNULL((SELECT MAX(IDPessoa) FROM tblPessoas),0)
    SET @IDPessoa = @IDPessoa + 1

    — Obter o nome da Pessoa
    DECLARE @NomePessoa VARCHAR(50)
    SET @NomePessoa = (SELECT NomePessoa FROM INSERTED)

    — Força um DELAY proposital de 5 segundos
    WAITFOR DELAY ’00:00:05′

    — Gravar o registro na tabela de pessoas
    INSERT INTO tblPessoas VALUES (@IDPessoa, @NomePessoa)
END

Na conexão C1, execute o seguinte comando para realizar uma inserção.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 4’)

Enquanto a conexão C1 está executando, alterne para a conexão C2 e execute o comando abaixo para realizar outra inserção. Como há o DELAY proposital, o efeito é de como se os usuários estivessem simultâneos.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 5’)

Após aguardar 5 segundos da execução da conexão C2, um erro foi gerado.

Msg 2627, Level 14, State 1, Procedure trgSeqIDPessoa, Line 19
Violation of PRIMARY KEY constraint ‘PKPessoa’. Cannot insert duplicate key in object ‘dbo.tblPessoas’.

The statement has been terminated.

Qual seria a razão para um erro de violação de chave primária ? Se a coluna IDPessoa é chave primária e a trigger é responsável por gerar o seqüencial qual seria a explicação para uma violação de chave primária ? A resposta é simples. Quando a conexão C1 realizou o INSERT, a trigger pesquisou o maior ID que era 3 e o armazenou na variável @IDPessoa. Quando C2 realizou o INSERT, C1 ainda não havia realizado o INSERT, então C2 pesquisou o maior ID que continuava sendo igual a três. Tanto C1 quanto C2 com base no ID 3 produziram o ID igual a 4. Como C1 realizou o INSERT primeiro, ela conseguiu realizar a inserção com o ID 4. C2 realizou o INSERT depois e como o ID 4 já estava cadastrado, houve uma violação de chave primária, pois, o ID não tolera dados em repetição. Se o ID não fosse chave primária, haveria dois registros com o ID igual a 4 (basta retirar a chave primária e fazer um novo teste).

A chave primária conseguiu impedir a presença de registros duplicados, mas um erro foi produzido e o INSERT de C2 foi cancelado. Alguns argumentaram que isso é aceitável já que a chance de dois registros simultâneos aparecerem é pequena e o erro de Primary Key pode ser tratado na aplicação. Tais argumentos podem fazer sentido para pequenas aplicações corporativas mas aplicações de missão crítica certamente irão desbancar tais argumentos. Imagine que um site de comércio eletrônico como a Amazon ou ainda a receita federal (na época da liberação do imposto de renda) mantenham um seqüencial de visitas ao site ? Certamente que a implementação da trigger não funcionaria. Ainda que o erro fosse tratado na aplicação e ressubmetido, essa definitivamente seria uma forte limitação à escalabilidade do site.

A solução parece ser bem simples. É preciso bloquear o MAX + 1 até que o INSERT tenha sido concluído. Se o MAX + 1 for bloqueado não haverá como C1 e C2 pegarem o mesmo ID. A idéia é interessante. Uma prova de conceito envolve a alteração da trigger.

— Alteração da trigger de geração de seqüenciais
ALTER TRIGGER trgSeqIDPessoa ON tblPessoas
INSTEAD OF INSERT
AS
BEGIN
    — Obter o maior ID (no caso do primeiro registro retornar zero)
    — O ID é bloqueado exclusivamente

    DECLARE @IDPessoa INT
    SET @IDPessoa = ISNULL((SELECT MAX(IDPessoa) FROM tblPessoas WITH (XLOCK)),0)
    SET @IDPessoa = @IDPessoa + 1

    — Obter o nome da Pessoa
    DECLARE @NomePessoa VARCHAR(50)
    SET @NomePessoa = (SELECT NomePessoa FROM INSERTED)

    — Força um DELAY proposital de 5 segundos
    WAITFOR DELAY ’00:00:05′

    — Gravar o registro na tabela de pessoas
    INSERT INTO tblPessoas VALUES (@IDPessoa, @NomePessoa)
END

O HINT XLOCK representa um bloqueio exclusivo. Uma vez que o bloqueio exclusivo seja colocado não haverá como duas conexões simultâneas capturarem o mesmo ID. A primeira conexão irá obter o ID, bloqueá-lo e só irá liberar o bloqueio após a realização do INSERT evitando potenciais duplicades e (ou) erros retornados para a aplicação. O teste pode ser feito com novos cadastrados. Na conexão, execute o script abaixo:

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 5’)

Enquanto a conexão C1 está executando, alterne para a conexão C2 e execute o comando abaixo para realizar outra inserção. Como há o DELAY proposital, o efeito é de como se os usuários estivessem simultâneos.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 6’)

Após aguardar 5 segundos da execução da conexão C2, tudo parece funcionar perfeitamente. Uma pesquisa na tabela irá demonstrar que os IDs 5 e 6 foram inseridos corretamente e o código funcionou perfeitamente. Os seqüenciais foram gerados conforme desejado. Para uma prova de conceito digna de um ambiente de alta concorrência, um novo teste será realizado. Enquanto a conexão C1 estiver um inserindo um registro, tentaremos consultar um registro já inserido.

INSERT INTO tblPessoas (NomePessoa) VALUES (‘Pessoa 7’)

Enquanto a conexão C1 está executando, alterne para a conexão C2 e execute o comando abaixo para realizar uma atualização. Como há o DELAY proposital, o efeito é de como se os usuários estivessem simultâneos.

UPDATE tblPessoas SET NomePessoa = ‘6a Pessoa’ WHERE IDPessoa = 6

Embora o registro 7 esteja sendo gerado, ao executar o comando acima, foi necessário esperar para que o update pudesse ser realizado. Essa espera ocorre porque a trigger lançou um bloqueio sobre o registro 6 (esse era o maior registro quando o INSERT de C1 foi realizado). Enquanto o INSERT de C1 não finalizar, o UPDATE irá ter que aguardar já que ele atua sobre o registro 6.

Embora seja muito pouco provável que alguém queira atualizar um registro recém inserido está bem claro que utilizar uma técnica do tipo MAX + 1 pode provocar duplicidades e caso seja utilizado o XLOCK para inibir essas duplicidades o resultado obtido é uma fila. Toda vez que uma inserção for feita, as demais terão que aguardar. Se ocorrer uma situação de 10 usuários simultâneos, as inserções serão realizadas de forma seqüencial, ou seja, o primeiro que chegar será atendido e os demais usuários terão de esperar para serem atendidos na ordem em que chegaram até que toda a fila seja atendida. Quanto mais usuários simultâneos houver, maior tenderá a ser a fila e mais os usuários irão aguardar já que os INSERTs não ocorrerão mais em paralelo mas sim de forma seqüencial.

Pode-se argumentar que a duração de um INSERT é de apenas 3ms, mas quando 3ms são responsáveis por gerar uma requisição de forma enfileirada, mil usuários simultâneos serão capazes de gerar uma espera de 3 segundos. Se o INSERT não for tão simples, mas estiver em uma stored procedure com dezenas de outras tarefas, validações e gravações, e demorar apenas 1 segundo para executar, 100 usuários serão capaz de gerar uma fila de 100s. Uma fila de 100 segundos é suficiente para gerar múltiplos timeouts e se os usuários tentarem novamente uma nova fila se formará gerando mais bloqueios, espera e insatisfação.

A solução mais elementar para evitar é utilizar a propriedade Identity do SQL Server. A autonumeração controlada pelo SQL Server é infinitamente mais otimizada para lidar com a concorrência do que uma solução baseada em MAX + 1. Aos interessados já discorri sobre o Identity e seus efeitos em um artigo postado no site do Plugmasters.

SQL Server: Geração de seqüenciais de forma automática
http://www.plugmasters.com.br/sys/materias/836/1/SQL-Server%3A-Gera%E7%E3o-de-seq%FCenciais-de-forma-autom%E1tica

O Identity tem seus pontos positivos, mas tem também suas falhas e pode levar a situações de "furos" na seqüência. Idealmente o "furo" na seqüência não deveria ser um problema uma vez que os objetivos principais do seqüencial são meramente garantir unicidade e que o registro subseqüente terá um valor superior ao registro anterior. Se por uma razão estética é necessário manter uma seqüência sem furos, o Identity não será suficiente. Para essas situações o MAX + 1 talvez fosse uma solução, mas não acredito que manter uma seqüência contínua justifique sacrificar a concorrência. Se isso é realmente necessário, a construção de uma tabela de seqüenciais pode ser uma melhor solução, pois, reduz os efeitos negativos sobre a concorrência. Alguns problemas permanecem, mas as restritições são menores que a abordagem MAX + 1.

Acredito que há razões fortes para não utilizar o MAX + 1 e sinceramente não o recomendo. Pode ser que os implementadores que o utilizam nunca tenham se deparado com aplicações de forte concorrência, mas ainda que a aplicação seja pequena não acho interessante o uso do MAX + 1. Se nas pequenas aplicações o MAX + 1 é utilizado, pode ser difícil largar esse vício quando aplicações maiores aparecerem.

[ ]s,

Gustavo

6 Respostas para “Piores práticas – Geração de seqüênciais baseados no MAX + 1

  1. Olá gustavo, você tem respondido meus posts no Forum da Microsoft.Bom, tenho lido bastante seu blog e confesso que não sou muito de ler blogs, mas gostei muito de seus posts, e li vários deles rs.Sobre este post especificamente ficou uma dúvida sobre os Identity.O que você está dizendo é que o max + 1 não terá furos pois sempre será o ultimo id + 1, já o Identity gerará um valor aleatório e único, porém tera furos entre um valor e outro?Não sei se ficou claro minha pergunta, a verdade é que eu não entendi os pos e contras de cada um deles.

  2. Olá Riderman,Fico feliz de ter esse feedback positivo sobre o meu blog. O Identity é suscetível a falhas, pois, o rollback irá "queimar" o Identity enquanto o MAX + 1 não tem esse problema. Em contrapartida, o MAX + 1 provocará uma fila que o Identity dispensa.Dê uma olhadinha no link do Plugmasters. Ele pode ser bem esclarescedor.Abs,

  3. Boa Noite Gustavo,Primeiro gostaria de parabenizá-lo! Seu artigo está excelente. E… agora vem o golpe heheheheComo faz para gerar o código sequencial antes de dar o insert? Exemplo:Tenho duas tabelas: Fornecedores e Endereços (quero fazer uma tabela só para endereços). Os dados das duas tabelas serão preenchidos na mesma tela. Como eu faço para ter o código que o fornecedor receberá antes de dar o insert nas duas tabelas?Nossa tomara que vc me ajude!Bjs

  4. Olá Danielle,Não é possível consultar um dado até que ele exista. Se o código ainda não foi inserido não é possível consultá-lo. Normalmente não é comum que alguém saiba o código antes do cadastro até porque existe a possibilidade do cadastro não ser efetivado e nesse caso o código (mesmo que visto previamente) não faria sentido.Se você tiver mesmo muita necessidade de saber o código antes de inserir o registro, você pode consultar o último valor e incrementar, mas correrá um risco enorme de no momento de inserir aquele código, o mesmo já ter sido inserido por outro usuário. Você pode até bloquear, mas aí todos irão esperar até que o cadastro se concretize. Isso seria terrível em termos de concorrência.Eu recomendaria que você tentasse procurar uma outra implementação. Não acho que é importante que o cliente veja o código de um cadastro que ainda não foi feito.Abs,

  5. Boa noite Gustavo,

    Parabens ao varios post´s, esta me ajudando muito em duvidas com o SQLServer !!!
    Atualmente estou migrando um banco de dados Firebird.. para SQLServer.

    Tenho uma situacao que gostaria de uma orientacao, se possivel !…
    la vai!!!

    Veja:

    “TabelaXPTO”
    campo1 int – identity – PK
    campo2 varchar(50)
    campo3 datetime
    campo4 …
    campo5 …
    campo6 .. CODIGO DA EMPRESA
    campo7 int – regra querer um numero automatico

    Preciso da seguinte Regra:
    Para o COMPO7 preciso de um valor automatico que nao se repita por “Empresa” .

    Exemplo do sql:
    Select MAX( campo7 )+ 1 from TABELAXPTO
    Where
    campo6 = :codigo da empresa X

    ( no Firebird utilizo o tal “GENERATOR”, onde é possivel criar varios “AUTONumeracao” )
    Importante: Esse numero nao sera atribuido para todos dos registros, ou seja, nao posso usar o “identity”.

    Solucoes!:??

    Pensei em fazer um SELECT MAX + 1(utilizando trigger), conforme mostra seu POST,
    com o XLOCK ;;esta gerando um “Gargalo”..demora para incluir os registros..
    sao muitas concorrencias. !!!!

    qual sua opniao sobre o caso :?
    teria uma ideia de como posso trabalhar no SQLServer…
    se tivesse Algu como o Generator do Firebird.. ja resolveria o Problema.

    Grato
    Fábio

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