Transação concorrente - Deadlock

[Murilo Sousa]

Bom dia!

Eu possuo um sistema feito em Delphi 7 + Firebird 2.5 com componentes de conexão da paleta InterBase. 
Em uma das minhas rotinas, é realizada a baixa de estoque do produto através de Expedição de pedido de venda.
Estou com o seguinte problema: Ao fazer a expedição de um mesmo produto, simultaneamente, em terminais diferentes (e pedidos de venda diferentes), o sistema trava e logo após destravar exibe a seguinte mensagem de erro: "deadlock / concurrent transaction number is 14297". Segue o código:

IBTransAtualizacao.StartTransaction;
try
  ExpedirItem(StrToInt(edtCodigo.Text)
             ,poItemExpedido.nQuantidade
             ,poItemExpedido.nQuantidadeUP
             ,cdsItensVendaITE_QTD_EXPEDIDO.AsFloat
             ,cdsItensVendaITE_QTD_UP_EXPEDIDO.AsFloat
             );
  IBTransAtualizacao.Commit;
except
  on E: Exception do
  begin
    IBTransAtualizacao.Rollback;
    MensagemFalha('Expedição não executada!');
  end;
end;

Toda operação está ligada no mesmo Transaction (IBTransAtualizacao).
Dentro do método ExpedirItem, faço algumas consultas na tabela ANDAMENTO_ESTOQUE (Esta por sua vez armazena toda movimentação de estoque do sistema por produto) para poder recalcular o saldo do produto que está sendo expedido e assim poder dar baixa do mesmo no estoque (Atualizar o campo PRO_ESTOQUE da tabela PRODUTO).
O deadlock provavelmente ocorre porque ao consultar simultaneamente a tabela ANDAMENTO_ESTOQUE cruzando (JOIN) com a tabela PRODUTO, o firebird bloqueia o registro referente ao produto que está sendo expedido. Mas está tudo ligado na mesma transação. Alguém sabe como resolver o problema do deadlock? Como faço para trabalhar com "fila"? Ex: Caso a transação em um terminal tente acessar um registro que já está sendo utilizado por outro, o sistema aguarde até o registro ser "liberado"? A propriedade Params do IBTransAtualizacao está como:

read_committed
no_rec_version
wait

Obrigado desde já!

[Flávio_Petu]

falai brother... tenta utilizar o CommitRetaining no lugar do commit

 

[EMBarbosa]

Em 07/07/2016 at 10:38, Flávio_Petu disse:

falai brother... tenta utilizar o CommitRetaining no lugar do commit

 

Isso não vai resolver. Pelo contrário, pode até piorar a situação...

[Flávio_Petu]

 

Em 11/07/2016 at 15:56, EMBarbosa disse:

Isso não vai resolver. Pelo contrário, pode até piorar a situação...

porque poderia piorar EMBarbosa ??

não entendi

[EMBarbosa]

16 horas atrás, Flávio_Petu disse:

 

porque poderia piorar EMBarbosa ??

não entendi

CommitRetaining mantém a transação aberta. Qualquer outra transação concorrente com o parâmetro "wait" também vai ficar esperando a transação.

Via de regra, toda transação deve permanecer aberta pelo menor tempo possível. Isso também é importante para que o servidor possa executar o "Sweep" no momento apropriado.

Faz uma pesquisa no google sobre CommitRetaining e Firebird... Provavelmente vai encontrar muita informação, principalmente no grupo de suporte do firebird no yahoo.

[Gerson De Simone]

Usa stored procedures, nas rotinas criticas, fazendo todo o procedimento nela, também nos procedimentos concorrentes, isso resolve o problema.

Por exemplo esse TRIGGER é parecido com o que vc tem que fazer

CREATE TRIGGER "AJ" FOR "PED_IT_TMP"
ACTIVE BEFORE INSERT POSITION 0
as
declare variable DATA TIMESTAMP;
declare variable QT_VENDAS NUMERIC(9, 3);
declare variable N_VENDAS NUMERIC(9, 3);
declare variable QTD NUMERIC(9, 3);
declare variable NQT NUMERIC(9, 3);
declare variable QT NUMERIC(9, 3);
declare variable TP VARCHAR(10);
declare variable DT INTEGER;
declare variable TOT DECIMAL(9, 3);
begin
data=CURRENT_DATE;
qt=PED_IT_tmp.QUANT;
select tipo from pedidos where cod=PED_IT_tmp.cod_ped into tp;
select vendas,qt_vend,pro_eqtd  from produto1 where COD=PED_IT_tmp.cod_prod  INTO :qt_vendas,:N_vendas,:qtd;
if (tp<>'Devolucao') then
begin
nqt=:qtd-:qt;
update produto1 set pro_eqtd=:nqt,
dt_ult_vend=:data,
qt_ult_vend=:QT,
qt_vend=:N_vendas+1,
vendas=:qt_vendas+:qt,
VAL_COMP=(PRO_CUSTO*:NQT),
VAL_VEND=(PRO_VVEND*:NQT) WHERE COD=PED_IT_tmp.cod_prod ;
end
else
begin
nqt=:qtd+:qt;
update produto1 set pro_eqtd=:nqt,
dt_ult_vend=:data,
qt_ult_vend=:QT,
qt_vend=:N_vendas-1,
vendas=:qt_vendas-:qt,
VAL_COMP=(PRO_CUSTO*:NQT),
VAL_VEND=(PRO_VVEND*:NQT) WHERE COD=PED_IT_tmp.cod_prod ;
end

   
end

ele atualiza uma movimentação inteira (tipo todos os itens de um pedido), a partir dessa stored procedure

CREATE PROCEDURE "AJUSTA_EST_TOTAL"
(
  "C_PED" INTEGER
)
AS
BEGIN EXIT; END ^

ALTER PROCEDURE "AJUSTA_EST_TOTAL"
(
  "C_PED" INTEGER
)
AS
declare variable CLI INTEGER;
declare variable TIPO VARCHAR(15);
declare variable TP VARCHAR(1);
begin
insert into PED_IT_TMP select * from "PED_IT" where cod_ped=:c_ped;
delete from PED_IT_TMP where cod_ped=:c_ped;
select cli,tipo from pedidos where cod=:"C_PED" into :cli,:tipo;
execute procedure tp1 :tipo RETURNING_VALUES :tp;
if (tp='V') then tp='P';
update pedidos set
fechado='T',
blq='T',
data=current_date,
hora=current_time where cod=:c_ped;
update ped_it set
data=current_date,
tipo=:"TP" where cod_ped=:c_ped;
update EMPRESA set DT_ULT_COMP=current_date where empr_cod=:cli;
  suspend;
end

a única informação que trafega na rede é o nº do pedido, mesmo com 20 transações diferentes o FB gerencia.

Eu passo todo trabalho para o servidor FB, em 80% das tarefas, é mais rápido e sem erros.

espero ter dado uma dica, pode dar trabalho no inicio, mas resolve.

 

 

 

[Murilo Sousa]

Bom dia pessoal,

Agradeço pelas dicas. Consegui resolver o problema com a seguinte lógica: O sistema tenta fazer a operação (try), se não conseguir (cair em um except) ele tenta novamente mais 4 vezes. Se conseguir fazer a operação sem dar deadlock, interrompe o loop e dá um commit, senão, se não conseguir em nenhuma das 5 tentativas, gera uma exceção com uma mensagem informativa e legível para o usuário e aborta a operação seguido de um rollback. Analisei e testei bem os locais na aplicação onde isso poderia afetar e pelo que vi deu tudo certo, não ocasionou nenhum outro problema e a operação foi executada corretamente. Obrigado a todos.

Editado 21 Julho, 2016 por Murilo Sousa

[Murilo Sousa]

Essa solução foi sugerida pelo pessoal na última FDD que aconteceu em Piracicaba.

[Antonio Carlos L]

@Murilo Sousa, @Gerson De Simone, @EMBarbosa, o post é antigo,  e se puderem dar uma ajuda, agradeço.

Estou com o seguinte ambiente, Firebird 2.5 em servidor linux, Delphi XE com DBEXPRESS (  sistema legado ),  umas 50 estações realizando atendimentos.

O componente do tipo TSQLConnection esta configurado assim :

blobsize=-1
commitretain=False
waitonlocks=True
isolationlevel=ReadCommitted
trim char=False

Durante toda a operação de atendimento, não ocorre nenhum problema, o problema de DEADLOCK ocorre quando vai ser finalizado a operação.

Ao ser finalizado é chamado a Stored Procedure  'SP_PROCESSA' e tenho por vezes mensagens do tipo de que a mesma esta em uso ou ela é acessada e em um dado ponto do processamento ocorre o  DEADLOCK

A procedure esta sendo chamada assim :

try
  sqlSP := TSQLStoredProc.Create(nil);
  try
    sqlSP.SQLConnection := Conn;
    T := Conn.BeginTransaction(TDBXIsolations.ReadCommitted);
    with sqlSP do
     begin
       StoredProcName := 'SP_PROCESSA';
       ParamByName('IN_DADOS').AsString := 'STRING DE PARAMETROS'
       ExecProc;
     end;  
    Conn.CommitFreeAndNil(T)
  finally
    FreeAndNil(sqlSP);
  end;
except
  Conn.RollbackFreeAndNil(T);
end;

O que é mais estranho é que quando ocorre o DEADLOCK e da o exception é solicitado o Rollback e mesmo assim certas partes do processamento NÃO SÃO DESFEITAS.

PROCEDURE SP_PROCESA(IN_DADOS)
/* PASSOS ILUSTRATIVOS */ 
BEGIN

1 - VARIAVEIS RECEBEM VALORES DE SELECTS;

2 - EXECUTE PROCEDURE BAIXA_ESTOQUE;

3 - EXECUTE PROCEDURE LANCA_COMISSAO;

4 - EXECUTE PROCEDURE ATUALIZA_STATUS;

END;

Não tenho como colocar em uma TRIGGER como sugerido pelo colega @Gerson De Simone mas tudo já está dentro de uma stored procedure

ACHO que nesse caso eu não deveria explicitamente controlar a transação e deveria fazer assim :

try
  sqlSP := TSQLStoredProc.Create(nil);
  try
    sqlSP.SQLConnection := Conn;
    with sqlSP do
     begin
       StoredProcName := 'SP_PROCESSA';
       ParamByName('IN_DADOS').AsString := 'STRING DE PARAMETROS'
       ExecProc;
     end;  
  finally
    FreeAndNil(sqlSP);
  end;
except
;
end;

Isso bastaria para o próprio Firebird controlar a transação ou teria que fazer algo do tipo :

WHEN ANY DO
    begin
      IN AUTONOMOUS TRANSACTION DO
        BEGIN
          1 - VARIAVEIS RECEBEM VALORES DE SELECTS;

          2 - EXECUTE PROCEDURE BAIXA_ESTOQUE;

          3 - EXECUTE PROCEDURE LANCA_COMISSAO;

          4 - EXECUTE PROCEDURE ATUALIZA_STATUS;
        END

      EXCEPTION EXSP_PROCESSA ;
    end
 

Se o colega  @Murilo Sousa puder passar o link do evento no qual ele conseguiu a solução ou puder detalhar um pouco mais como fez, agradeço.

Desde já obrigado.

 

 

 

 

 

 

 

 

 

 

 

 

[Murilo Sousa]

20 minutos atrás, Antonio Carlos L disse:

O que é mais estranho é que quando ocorre o DEADLOCK e da o exception é solicitado o Rollback e mesmo assim certas partes do processamento NÃO SÃO DESFEITAS.

@Antonio Carlos L

1 - Amigo, nesse caso é muito provável que você tem algum commit perdido dentro da sua SP.. Por isso as alterações não são desfeitas.. Não é que não são desfeitas, é que já foram commitadas.

2 - Como todos sabemos, o deadlock ocorre por concorrência de registros entre transações. Muito provável que você esteja tentando acessar algum registro de uma tabela na transação X, porém ele está em alteração e pendente de commit na transação Y.

 

24 minutos atrás, Antonio Carlos L disse:

Se o colega  @Murilo Sousa puder passar o link do evento no qual ele conseguiu a solução ou puder detalhar um pouco mais como fez, agradeço.

3 - Cara, nesse caso, eu participei do evento FDD (Firebird developers day) que ocorreu em Piracicaba em 2016. Ele ocorreu recentemente no dia 18/08/2018, porém, nessa edição eu não compareci. Naquela época, eu conversei com algumas pessoas para esclarecer essa dúvida e por fim cheguei na solução citada. O meu caso é bem nítido: no meu sistema, eu estava realizando a expedição de pedidos de venda. Se eu realizasse a expedição (em diferentes terminais ao mesmo tempo) de pedidos que possuíssem o mesmo produto, o banco de dados apresentava deadlock, pois, no momento da atualização do campo estoque do produto enquanto uma transação estava processando a alteração do estoque, a outra também tentava, porém, o registro ainda estava em uso pela primeira transação que processava sua alteração. Com base nisso, fiz um esquema de tentativas para a atualização do campo estoque.. Uma hora a primeira transação terminaria seus processos, enquanto isso a segunda estava tentando, mas, com um except silencioso para não ficar exibindo toda hora o deadlock para o usuário, depois que o registro destravasse na primeira transação, a segunda conseguiria alterá-lo normalmente. Essa foi a lógica que resolveu meu problema. É um pouco confuso, mas dependendo do seu cenário, pode ser uma solução. Boa sorte!

[Antonio Carlos L]

@Murilo Sousa, obrigado pelo seu retorno.

Eu também achei isso de haver um COMMIT perdido, mas revisei e não tem.

Eu "acho" que resolvi essa questão trazendo TODAS as SP chamadas para dentro  da principal,  eu simulei aqui um LOCK e o Roolback ocorreu em tudo. Não entendi, não havia nenhum commit.

Agora em relação a dar o erro da SP estar em uso, ai realmente complicou, não sei como tratar isso.

[Felipe E. Resende Mesquita]

1 hora atrás, Antonio Carlos L disse:

 

Bom dia, Antonio Carlos L.

Este tópico é bem antigo, portanto será fechado. Novas dúvidas criar um novo tópico.