Concorrência de Solicitações - Duplicando dados

Question

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?

Answer 1

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

Answer 2

Deleted

Answer 3

Outra questão: qual o nível de isolamento configurado?

Answer 4

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.

Answer 5

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.

Answer 6

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?

Answer 7

Deleted

Answer 8

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"

Answer 9

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

Answer 10

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.

Answer 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.

---

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

Answer 12

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

Answer 13

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.

Answer 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

Answer 15

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.