0

I want to move specific lines (some fields) from a tableA to a tableB by deleting these rows from tableA.

I use Delphi version Rio with FireDAC with Firebird RDMBS.

  1. I use a procedure to move and delete a single line
  2. I can not use this cursor to move multiple lines

I use procedure 1 to move and delete a single line. The problem is that when I use the procedure 2 (EXECUTE PROCEDURE "PRST_RetransFert";) I get an error message:

Engine Error (code = 335544336): deadlock.
update conflicts with concurrent update.
concurrent transaction number is 41556.
At procedure 'PRST_INSERTMEMODELETTRANSF' line: 36, col: 3
At procedure 'PRST_RetransFert' line: 13, col: 15.
SQL Error (code = -913): deadlock.

This procedure alone works fine.

RECREATE PROCEDURE PRST_INSERTMEMODELETTRANSF (
 IDTRANSFMEMO INTEGER)
AS 
declare variable numero varchar(10);
declare variable sommetransf decimal(15,2);
declare variable crdtransf smallint;
declare variable marge decimal(15,2);
declare variable idut integer;
declare variable idcl integer;
declare variable optionenvoie integer;
declare variable idop integer;
declare variable idoptionoperat integer;
declare variable naturetransf varchar(25);
declare variable codetransfert varchar(25);
declare variable coderapel varchar(25);
declare variable codepin varchar(25);
declare variable avantconnect smallint;
declare variable etattransf varchar(255);
begin
  /* Procedure Text */
   :etattransf = '....NON TRANSMIS RETRANSFERE EN ATTENTE.....' ;
   :AVANTCONNECT = 0 ;
   select numero ,somme ,credit_cl ,marge,id_ut ,
                    id_cl , option_envoie ,id_op  ,id_option_operat
                    ,nature_transf ,code_transfert  , code_rapel  ,Code_pin
                    from  v_nontransmi  where id_transfmemo = :idtransfmemo
                      into :numero,:sommetransf , :CRDTRANSF , :marge  , :idut
                                         , :idcl   , :optionenvoie   , :idop  , idoptionoperat  , :naturetransf
                                           , :codetransfert , :coderapel , :codepin ;

  insert  into transfertsmemoire (numero, somme_transf, crd_transf, marge, id_ut, etat_transf, id_cl, avant_connect,
                                 option_envoie, id_op, id_option_operat, nature_transf, code_transfert, code_rapel, code_pin)
                          Values (:Numero,:sommetransf, :crdtransf,:marge,:idut,:etattransf,:idcl, :avantconnect,:optionenvoie,:idop,
                                 :idoptionoperat,:naturetransf,:codetransfert,:coderapel,:codepin);

  delete from transferts where id_transfmemo = :idtransfmemo ;
--  suspend;
end

I can not use this SP to move multiple lines

RECREATE PROCEDURE "PRST_RetransFert"
AS 
declare variable id_trsMem integer ;  
declare curIdMemoTran Cursor for 
  (select id_transfmemo from v_nontransmi);
   begin  
    open curIdMemoTran;
      while (row_count > 0) do
          begin
              fetch curIdMemoTran into :id_trsMem ;
              execute procedure prst_insertmemodelettransf (:id_trsMem);
              if (row_count = 0) then leave;
            suspend;
          end
  close curIdMemoTran ;
end
Arioch 'The
  • 15,799
  • 35
  • 62
  • Why do you say you cannot? Which Delphi version? (This should be straightforward to do using FireDAC) – MartynA Nov 06 '19 at 20:38
  • thank you very much for your answer so fast I use embarcadero RIO with FireDAC (BDD FIREBIRD)the problem is that when I use the procedure 2 (EXECUTE PROCEDURE "PRST_RetransFert";) I will get an error message next Engine Error (code = 335544336): deadlock. update conflicts with concurrent update. concurrent transaction number is 41556. At procedure 'PRST_INSERTMEMODELETTRANSF' line: 36, col: 3 At procedure 'PRST_RetransFert' line: 13, col: 15. SQL Error (code = -913): deadlock. – nabil doghmane Nov 06 '19 at 20:45
  • 1
    Anyway, consider using TFDBatchMove, etc. if you cannot resolve the deadlock. – MartynA Nov 06 '19 at 20:47
  • Engine Error (code = 335544336): deadlock. update conflicts with concurrent update. concurrent transaction number is 41556. At procedure 'PRST_INSERTMEMODELETTRANSF' line: 36, col: 3 At procedure 'PRST_RetransFert' line: 13, col: 15. SQL Error (code = -913): deadlock.this error appears in firebirdMaestro and the same error in ibexpert – nabil doghmane Nov 06 '19 at 20:51
  • Please, that the sql code of the second procedure["PRST_RetransFert"] is correct or not? I think that the error comes from here can be defect of syntax but is compiled without result or with result error – nabil doghmane Nov 06 '19 at 20:57
  • I doubt whether your problem comes from a syntax error because if there was a syntax error, the Sql would not execute, but it seems to attempt to execute, otherwise you would'n get the deadlock. Deadlocks normally occur when two concurrent processes attempt to lock resources (tables) in a different order than one another. So I suggest you carefully check whether or not your two Sql commands lock the resources they use in the same otder. Try googling 'firebird causes of deadlock' – MartynA Nov 07 '19 at 08:36
  • Exactly how are you executing this, and why do you use `suspend` in these stored procedures? Procedures with `suspend` are selectable, not executable, and if you execute them instead of selecting from them, they will only process until the first `suspend` and then exit. The presence of the `suspend` is likely the cause of your problem. – Mark Rotteveel Nov 07 '19 at 18:04
  • Nabil, you say you want to copy data from `tableA` and then delete from `tableA` but your code is different! you code quotes different relations there. Please, EDIT the question and show the definition of those, show how they are related. `select numero .... from v_nontransmi` and then `delete from transferts` @MarkRotteveel i think that is that very new cursor stability introduced in FB3, and yeah, it seems normal to deadlock when they try to read and delete the same record simultaneously from different SPs. – Arioch 'The Nov 07 '19 at 19:32
  • 1
    Fuse those two procedures together! Make the procedure have BOTH the loop AND copying+deleting in the loop body. As of know it seems you have two active queries from the same source - `select ... from transferts` in caller SP and `delete ... from transferts` in the callee. In FB 3 they seem to dead-lock one another. So, remove extra queries and extra SPs, make one SP with single loop/cursor. Also consider remaking cursor into `for select ... into ... do ...` loop - there was some slow down of explicit operations in FB3 when compared with FB2. – Arioch 'The Nov 07 '19 at 19:37
  • 1
    Two more avenues for research: 1) log into the database as SYSDBA and when the error is displayed do `select * from mon$transactions` - see how many you have, is it one transaction deadlocking itself or two deadlocking one another? see `c:\Program Files\Firebird\Firebird_3_0\doc\README.monitoring_tables.txt` and 2) use Trace API (for example via FBProfiler from SourceForge) to see what transactions and statements are send to the RDBMS. Maybe there really are several transactions active? (or maybe there are hidden implicit `savepoint`s within SPs) – Arioch 'The Nov 07 '19 at 19:54

0 Answers0