Início / dba / Perguntas / 259312
Accepted
Dryadwoods
Asked: 2020-02-11 05:04:02 +0800 CST

Inserir com resultado da função - evite duplicatas

  • 772

Eu tenho uma função MS Sql que tem alguma lógica para processar o próximo ID de um elemento (concats letras com números com base em alguma lógica de negócios, também com base em um select count()).

De alguma forma, durante os últimos dias (em ambiente de produção), já que o sistema está sendo usado por mais usuários simultâneos, conseguimos algumas duplicatas.

A lógica existente é muito simples:

insert into XXXX (A,B,C,D,E) 
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE

De alguma forma, o GiveMeNewId devolveu o mesmo ID em duas solicitações paralelas.

Esta função é utilizada no momento em diferentes lugares, dentro de um Stored Procedure e também a partir de uma construção SQL "dinâmica" de um "cliente C#".

Eu não quero implementar uma camada "singleton" em algum software intermediário.

Alguma ideia de como resolver este problema? Obrigado.

PS: para testar eu tinha as seguintes linhas dentro da função SQL para poder rodar diferente o insert em diferentes janelas.

/*********************************************************/
declare @dtStart datetime=getutcdate()
while datediff(second,@dtStart,getutcdate()) < 10
begin
    set @dtStart = @dtStart
end
/*********************************************************/
sql-server locking

2 respostas

  1. No meu cenário de teste, acho que sou capaz de resolver esse problema escrevendo:

      insert into XXXX with (tablock, xlock) (A,B,C,D,E) .....

    Pelo menos agora não consigo replicar entradas duplicadas, mas não tenho certeza se estou criando alguns problemas dos quais não estou ciente.

    Se você acha que isso não é uma solução, por favor me avise.

    • 1
  2. Best Answer

    Além de lidar com o acesso exclusivo à própria tabela, como você mencionou, você também pode lidar com o acesso às chamadas de função com sp_getapplock.

    Exemplo sem o uso de transações, travando no nível da sessão.

    EXEC sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive',
               @LockOwner= 'Session';
insert into XXXX (A,B,C,D,E) 
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE;
EXEC sp_releaseapplock @Resource = 'GiveMeNewID',   
               @LockOwner= 'Session';

    Ou

    DECLARE @date datetime
EXEC sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive',
               @LockOwner= 'Session';
select @date=dbo.GiveMeNewID(getdate())
EXEC sp_releaseapplock @Resource = 'GiveMeNewID',   
               @LockOwner= 'Session';
insert into XXXX (A,B,C,D,E) 
select @date, @ParamB, @ParamC, @ParamD, @ParamE;

    Exemplo fictício com transações, travando no nível da transação.

    CREATE FUNCTION dbo.bla()
returns datetime2
as 
BEGIN
DECLARE @date datetime2 = getdate()
RETURN  @date
END

    Execute esta consulta em uma sessão (lembre-se de que uma transação permanecerá aberta)

    BEGIN TRAN;  
DECLARE @result int;  
EXEC @result = sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive';  
SELECT dbo.bla()

    Execute o mesmo em outra sessão

    BEGIN TRAN;  
DECLARE @result int;  
EXEC @result = sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive';  
SELECT dbo.bla()

    A segunda sessão será bloqueada até que a transação seja concluída.

    Não se esqueça de correr COMMIT TRANnas duas sessões.

    Além disso

    Dependendo da lógica da função, se você quiser diminuir as chances de duplicatas ao usar os dados de data e hora como parâmetro, você pode começar a usar o tipo de datetime2data e SYSDATETIME()as chamadas de função para obter intervalos de tempo maiores .

    • 1

relate perguntas