Usuário com melhor resposta
Concorrência de Solicitações - Duplicando dados

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
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
- Sugerido como Resposta Junior Galvão - MVPMVP quarta-feira, 25 de julho de 2018 19:15
- Marcado como Resposta Filipe B CastroModerator sexta-feira, 3 de agosto de 2018 19:44
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
-
-
-
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.
-
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.
-
-
-
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"
-
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
-
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.
-
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
-
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 -
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.
-
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
- Sugerido como Resposta Junior Galvão - MVPMVP quarta-feira, 25 de julho de 2018 19:15
- Marcado como Resposta Filipe B CastroModerator sexta-feira, 3 de agosto de 2018 19:44
-
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.