none
Concorrência de Solicitações - Duplicando dados RRS feed

  • Pergunta

  • Boa tarde pessoal.

    Estou com um probleminha de concorrência em um servidor SQL. Já procurei em inúmeros forúns, porém, não consegui achar a solução.

    Em um determinado processo, tenho uma fila de itens que devem ser tratados e cada processo deve ser tratado por um único usuário. Quando o usuário termina de tratar, o sistema já busca o próximo item da fila.

    O problema é que está sendo indicado para mais de um analista o mesmo item.

    A função que faz a busca é uma store procedure, que possui o seguinte comando:

    @Usuario as int
    
    BEGIN
    	
    SET NOCOUNT ON;
    
    	declare @id_Conta INT
    begin transaction
    
    	INSERT INTO USUARIO_CONTA (ID_USUARIO, ID_CONTA) VALUES (@Usuario,
    	(SELECT TOP 1 ID_CONTA FROM [CONTAS] 
    		WHERE ID_STATUS = 1 AND id_USUARIO_CONTA IS NULL order by Data_Entrada asc))
    commit
    
    
    SET @id_Conta =  (SELECT TOP 1 ID_CONTA FROM [CONTAS] WHERE ID_STATUS = 1 AND id_USUARIO_CONTA =@Usuario order by Data_Entrada asc)
    	
    	SELECT * FROM CONTAS WHERE ID_CONTA = @id_Conta
     
    End

    O problema é que, em alguns casos, o comando insert está sendo realizado para o mesmo id, fazendo com que o mesmo caso vá para dois usuários diferentes. A diferença entre os dois comandos que atingem o mesmo item corresponde à milésimos de segundo, conforme a imagem abaixo do result da tabela "Usuario_Conta".

    id_Conta      id_Usuario       Data_Ativacao
    41194	        7	        2018-07-24 14:31:08.050
    41194	        6	        2018-07-24 14:31:08.053

    A View Contas é composta pela seguinte combinação de tabelas. Coloquei como texto pois não posso colocar imagens.

    SELECT        dbo.Conta.id_Cont, dbo.Conta.Nome, dbo.Conta.OBS, dbo.Status_Conta.id_Status, Usuario_Conta.id_usuario, Usuario_Conta.id_Usuario_Conta, 
                             dbo.Conta.Data_Entrada
    FROM            dbo.Conta LEFT OUTER JOIN
                                 (SELECT        id_Usuario_Conta, id_usuario, id_Conta, Data_Ativacao, Ativo
                                   FROM            dbo.Usuario_Conta AS Usuario_Conta_1
                                   WHERE        (Ativo = '1')) AS Usuario_Conta ON dbo.Conta.id_Cont = Usuario_Conta.id_usuario LEFT OUTER JOIN
                             dbo.Status_Conta ON dbo.Conta.id_Cont = dbo.Status_Conta.id_Conta

    Pelo que eu havia entendido, por estar dentro de um Transaction, não poderia ser realizada mais nenhuma operação enquanto ele não terminasse, dessa forma, quando a segunda inserção fosse realizada, já seria para outro item, mas não é isso o que está acontecendo.

    Alguém tem alguma idéia de como poderia fazer para evitar esse duplicidade?



    • Editado Josue R Coelho quarta-feira, 25 de julho de 2018 12:23 Ajuste do código Transcrito
    terça-feira, 24 de julho de 2018 18:12

Respostas

  • Criei um script um pouco simplificado utilizando variáveis de tabela e repetindo a consulta da View Contas onde a mesma é utilizada para simular a execução do seu código, e nos meus testes a "View" retorna o mesmo resultado antes e depois do Insert. Segue o script que executei através do SQL Server Management Studio:

    declare @Conta table
    (id_Conta int, Data_Entrada date);
    
    declare @Status_Conta table
    (id_Status_Conta int, id_Conta int, id_Status int);
    
    declare @Usuario_Conta table
    (id_Usuario_Conta int, id_Usuario int, id_Conta int, Ativo bit);
    
    insert into @Conta values
    (1, '20180725'),
    (2, '20180725'),
    (3, '20180725');
    
    insert into @Status_Conta values
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1);
    
    -- Consulta equivalente a View Contas
    SELECT
        Conta.id_Conta, 
        Status_Conta.id_Status, 
        Usuario_Conta.id_usuario, 
        Usuario_Conta.id_Usuario_Conta, 
        Conta.Data_Entrada
    FROM @Conta as Conta
    LEFT OUTER JOIN
    (
        SELECT
            id_Usuario_Conta, 
            id_usuario, 
            id_Conta
        FROM @Usuario_Conta AS Usuario_Conta_1
        WHERE
            (Ativo = '1')
    ) AS Usuario_Conta ON Conta.id_Conta = Usuario_Conta.id_usuario 
    LEFT OUTER JOIN @Status_Conta as Status_Conta ON Conta.id_Conta = Status_Conta.id_Conta
    
    declare @Usuario as int;
    set @Usuario = 100;
    
    -- Insert equivalente ao da procedure
    INSERT INTO @USUARIO_CONTA (ID_USUARIO, id_Conta) VALUES (@Usuario,
    (SELECT TOP 1 id_Conta 
     FROM 
     (
    	-- Consulta equivalente a View Contas
    	SELECT
    		Conta.id_Conta, 
    		Status_Conta.id_Status, 
    		Usuario_Conta.id_usuario, 
    		Usuario_Conta.id_Usuario_Conta, 
    		Conta.Data_Entrada
    	FROM @Conta as Conta
    	LEFT OUTER JOIN
    	(
    		SELECT
    			id_Usuario_Conta, 
    			id_usuario, 
    			id_Conta
    		FROM @Usuario_Conta AS Usuario_Conta_1
    		WHERE
    			(Ativo = '1')
    	) AS Usuario_Conta ON Conta.id_Conta = Usuario_Conta.id_usuario 
    	LEFT OUTER JOIN @Status_Conta as Status_Conta ON Conta.id_Conta = Status_Conta.id_Conta
     ) CONTAS
     WHERE ID_STATUS = 1 AND id_USUARIO_CONTA IS NULL 
     order by Data_Entrada asc))
    
    -- Verificando registro inserido
    select * from @Usuario_Conta
    
    -- Consulta equivalente a View Contas
    SELECT
        Conta.id_Conta, 
        Status_Conta.id_Status, 
        Usuario_Conta.id_usuario, 
        Usuario_Conta.id_Usuario_Conta, 
        Conta.Data_Entrada
    FROM @Conta as Conta
    LEFT OUTER JOIN
    (
        SELECT
            id_Usuario_Conta, 
            id_usuario, 
            id_Conta
        FROM @Usuario_Conta AS Usuario_Conta_1
        WHERE
            (Ativo = '1')
    ) AS Usuario_Conta ON Conta.id_Conta = Usuario_Conta.id_usuario 
    LEFT OUTER JOIN @Status_Conta as Status_Conta ON Conta.id_Conta = Status_Conta.id_Conta
    

    Não sei se cometi algum erro nessa simulação, mas me parece equivalente aos códigos postados.


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 25 de julho de 2018 18:04

Todas as Respostas

  • Boa tarde,

    Não sei se vou conseguir ajudar, mas uma dúvida... as colunas Id_Status, Id_Usuario_Conta E Id_Cont da tabela Conta são alteradas em algum momento? Se são, em qual momento e com qual comando?


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 24 de julho de 2018 19:43
  • Deleted
    quarta-feira, 25 de julho de 2018 00:00
  • Outra questão: qual o nível de isolamento configurado?
    quarta-feira, 25 de julho de 2018 01:31
  • Bom dia gapimex.

    Essas colunas estão em tabelas separadas. A id_status está na tabela Status_Conta, a id_usuario_Conta é a PK da tabela Usuário_Conta. A id_Conta está em três tabelas, sendo a tabela Conta, como PK, e nas tabelas Status_Conta e Usuário_Conta como FK.

    A tabela Conta é preenchida por um outro processo, que gera a "fila", e quando há o primeiro insert na tabela Conta, ocorre uma inserção na tabela Status_Conta também. A tabela Usuário_Conta é preenchida nesse processo que mostrei acima. Já a tabela Status_Conta é alterada ao fim da análise do usuário e antes de solicitar novo item da fila, ou seja, antes desse processo.

    quarta-feira, 25 de julho de 2018 11:58
  • Bom dia José.

    Se uma conta pode estar associada a um único usuário, então para o quê serve a tabela USUARIO_CONTA? Não seria o caso de ter uma coluna ID_USUARIO como chave estrangeira na tabela CONTA, apontando para o usuário que for responsável pela conta?

    Eu posso ter um usuário na conta "Ativo" de cada vez. A tabela Usuário_Conta seria para manter um histórico de qual usuário tratou. Ela possui um "rastro", digamos assim, pois o usuário pode ter iniciado o tratamento e não ter finalizado, devolvendo o caso para a fila. Você acha que poderia resolver o problema se colocasse o usuário ativo na Tabela Conta e deixasse tabela Usuário_conta apenas como registro de histórico?

    E sobre a tabela STATUS_CONTA, o que ela contém?

    Ela contém o registro do status atual da conta e o id da conta. Também serve como histórico ao mesmo tempo, mantendo o rastro de todos os status que a conta já passou.

    A princípio o par BEGIN TRANSACTION / COMMIT está inútil no trecho de código informado, pois o INSERT já gera um envoltório BT/COMMIT. Exceto, é claro, se a opção IMPLICIT_TRANSACTIONS estiver ON, quando somente o BT é gerado (se @@TRANCOUNT estiver zerado)

    IMPLICIT_TRANSACTIONS  está OFF.

    No trecho abaixo
       (SELECT TOP 1 ID_CONTA FROM [CONTAS]
               WHERE ID_STATUS = 1 AND id_USUARIO_CONTA IS NULL order by Data_Entrada asc))
    consta a coluna id_USUARIO_CONTA como existindo na exibição (view) CONTAS. Mas esta coluna não existe na declaração da exibição que transcreveu para este tópico. Poderia rever os códigos que transcreveu, acrescentando as informações que estão faltando?

    Realmente, havia transcrito o código da consulta errada. Ajustei acima.

    quarta-feira, 25 de julho de 2018 12:22
  • Bom dia André.

    Desconheço essa informação, bem como a forma de descobrir.

    Não tenho grandes conhecimentos em banco de dados, infelizmente. Podes me dizer como descobrir, para que eu possa te passar essa informação?

    quarta-feira, 25 de julho de 2018 12:26
  • Deleted
    quarta-feira, 25 de julho de 2018 13:18
  • Realmente, havia transcrito o código da consulta errada. Ajustei acima.

    A junção
         ON dbo.Conta.id_Cont = Usuario_Conta.id_usuario
    está correta?

    Estou tentando simular aqui as condições, por isso necessito das informações exatas.



    Entendo Perfeitamente.

    Infelizmente tive que realizar algumas alterações, por restrições da empresa, para poder postar aqui, e acabei cometendo erros na transcrição do código.

    A Junção correta é "ON dbo.Conta.id_Cont = Usuario_Conta.id_Conta"

    quarta-feira, 25 de julho de 2018 13:39
  • Bom dia,

    Josue, não sei se entendi corretamente mas pela query que destaco abaixo e é utilizada no Insert deduzi que a tabela Contas possui as colunas Id_Status e Id_Usuario_Conta, pois são utilizadas no Where:

    	(SELECT TOP 1 ID_CONTA FROM [CONTAS] 
    		WHERE ID_STATUS = 1 AND id_USUARIO_CONTA IS NULL order by Data_Entrada asc)
    Imaginei, talvez equivocadamente, que essas colunas ou pelo menos uma delas fosse alterada nessa procedure, pois acho que mantendo-se o Id_Status igual a 1 e o Id_Usuario_Conta nulo a mesma linha da tabela Contas será retornada no próximo Insert.


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 25 de julho de 2018 14:21
  • Bom dia,

    Josue, não sei se entendi corretamente mas pela query que destaco abaixo e é utilizada no Insert deduzi que a tabela Contas possui as colunas Id_Status e Id_Usuario_Conta, pois são utilizadas no Where:

    	(SELECT TOP 1 ID_CONTA FROM [CONTAS] 
    		WHERE ID_STATUS = 1 AND id_USUARIO_CONTA IS NULL order by Data_Entrada asc)
    Imaginei, talvez equivocadamente, que essas colunas ou pelo menos uma delas fosse alterada nessa procedure, pois acho que mantendo-se o Id_Status igual a 1 e o Id_Usuario_Conta nulo a mesma linha da tabela Contas será retornada no próximo Insert.



    Gapimex,

    Os nomes ficaram parecidos. A tabela se chama Conta.

    Contas é o nome da Consulta(view) que criei. Essa sim possui todos esses campos, mas são de tabelas diferentes.

    quarta-feira, 25 de julho de 2018 14:28
  • Ok, não tinha reparado, mas pelo que entendi acho que o mesmo Id_Conta vai ser retornado na query do Insert nas próximas execuções da procedure porque as colunas Id_Usuario_Conta e Ativo da linha inserida na tabela Usuario_Conta ficam nulas e o Id_Status não é alterado.

    Assinatura: http://www.imoveisemexposicao.com.br



    • Editado gapimex quarta-feira, 25 de julho de 2018 15:00
    quarta-feira, 25 de julho de 2018 14:57
  • Josue,

    Há vários comentários que seguem a linha de raciocínio no BD.

    Mas esse cenário me parece típico de um problema de concorrência a nível de aplicação. O sistema executa quase que simultaneamente a SP que acaba retornando a mesma informação para dois usuários, sendo que não deveria.

    Já passei por cenários parecidos. Já pensou por esse lado? Se sim, podemos começar a discutir soluções para sua app aqui.


    Espero ter ajudado! Se consegui, não esquece de marcar no fórum como útil! Obrigado :)
    #fullstack dev - MCSA, MCTS and actually blogging at www.saindodacaixinha.com.br

    quarta-feira, 25 de julho de 2018 16:11
  • Ok, não tinha reparado, mas pelo que entendi acho que o mesmo Id_Conta vai ser retornado na query do Insert nas próximas execuções da procedure porque as colunas Id_Usuario_Conta e Ativo da linha inserida na tabela Usuario_Conta ficam nulas e o Id_Status não é alterado.


    Gapimex,

    Não será o mesmo. O insert altera a relação id_usuario_conta/id_Conta, fazendo com que deixe de ser null.

    Na grande maioria das vezes, ele traz informações diferentes, como deve ser. Os casos em que estão ocorrendo duplicidade são em situações nas quais que há mais de uma solicitação em momentos muito próximos(centésimos de segundo de diferença) ao servidor. Ao invés de terminar uma e iniciar outra, ele está executando as duas simultaneamente.

    quarta-feira, 25 de julho de 2018 16:14
  • Criei um script um pouco simplificado utilizando variáveis de tabela e repetindo a consulta da View Contas onde a mesma é utilizada para simular a execução do seu código, e nos meus testes a "View" retorna o mesmo resultado antes e depois do Insert. Segue o script que executei através do SQL Server Management Studio:

    declare @Conta table
    (id_Conta int, Data_Entrada date);
    
    declare @Status_Conta table
    (id_Status_Conta int, id_Conta int, id_Status int);
    
    declare @Usuario_Conta table
    (id_Usuario_Conta int, id_Usuario int, id_Conta int, Ativo bit);
    
    insert into @Conta values
    (1, '20180725'),
    (2, '20180725'),
    (3, '20180725');
    
    insert into @Status_Conta values
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 1);
    
    -- Consulta equivalente a View Contas
    SELECT
        Conta.id_Conta, 
        Status_Conta.id_Status, 
        Usuario_Conta.id_usuario, 
        Usuario_Conta.id_Usuario_Conta, 
        Conta.Data_Entrada
    FROM @Conta as Conta
    LEFT OUTER JOIN
    (
        SELECT
            id_Usuario_Conta, 
            id_usuario, 
            id_Conta
        FROM @Usuario_Conta AS Usuario_Conta_1
        WHERE
            (Ativo = '1')
    ) AS Usuario_Conta ON Conta.id_Conta = Usuario_Conta.id_usuario 
    LEFT OUTER JOIN @Status_Conta as Status_Conta ON Conta.id_Conta = Status_Conta.id_Conta
    
    declare @Usuario as int;
    set @Usuario = 100;
    
    -- Insert equivalente ao da procedure
    INSERT INTO @USUARIO_CONTA (ID_USUARIO, id_Conta) VALUES (@Usuario,
    (SELECT TOP 1 id_Conta 
     FROM 
     (
    	-- Consulta equivalente a View Contas
    	SELECT
    		Conta.id_Conta, 
    		Status_Conta.id_Status, 
    		Usuario_Conta.id_usuario, 
    		Usuario_Conta.id_Usuario_Conta, 
    		Conta.Data_Entrada
    	FROM @Conta as Conta
    	LEFT OUTER JOIN
    	(
    		SELECT
    			id_Usuario_Conta, 
    			id_usuario, 
    			id_Conta
    		FROM @Usuario_Conta AS Usuario_Conta_1
    		WHERE
    			(Ativo = '1')
    	) AS Usuario_Conta ON Conta.id_Conta = Usuario_Conta.id_usuario 
    	LEFT OUTER JOIN @Status_Conta as Status_Conta ON Conta.id_Conta = Status_Conta.id_Conta
     ) CONTAS
     WHERE ID_STATUS = 1 AND id_USUARIO_CONTA IS NULL 
     order by Data_Entrada asc))
    
    -- Verificando registro inserido
    select * from @Usuario_Conta
    
    -- Consulta equivalente a View Contas
    SELECT
        Conta.id_Conta, 
        Status_Conta.id_Status, 
        Usuario_Conta.id_usuario, 
        Usuario_Conta.id_Usuario_Conta, 
        Conta.Data_Entrada
    FROM @Conta as Conta
    LEFT OUTER JOIN
    (
        SELECT
            id_Usuario_Conta, 
            id_usuario, 
            id_Conta
        FROM @Usuario_Conta AS Usuario_Conta_1
        WHERE
            (Ativo = '1')
    ) AS Usuario_Conta ON Conta.id_Conta = Usuario_Conta.id_usuario 
    LEFT OUTER JOIN @Status_Conta as Status_Conta ON Conta.id_Conta = Status_Conta.id_Conta
    

    Não sei se cometi algum erro nessa simulação, mas me parece equivalente aos códigos postados.


    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 25 de julho de 2018 18:04
  • Pessoal,

    Obrigado pela ajuda. Não consegui resolver o problema, mas não terei mais como tentar. O projeto no qual estava ocorrendo este problema foi suspenso e terei que me dedicar à outras atividades.

    Gapimex, não cheguei a analisar sua resposta.

    Aos moderadores, podem fechar o tópico, por gentileza.

    quinta-feira, 26 de julho de 2018 12:33