0

Need help me how solve this problem I Get error concurent update? Delphi Unigui Web application master detail cacheupdatemode I have

  1. MainConnection
  2. ReadTransaction isolation :ReadCommited
  3. WriteTransaction isolation :Snapshot
    masterqry ='select * from master' ,  detailqry ='select * from detail'
      autocommit:=false
      chachedupdate:=true
      localmasterdetail:=true 
      Transaction := ReadTransaction
      UpdateTransaction:=WriteTransaction
      and storedProcedure
      autocommit:=false
      chachedupdate:=false
      Transaction := ReadTransaction
      UpdateTransaction:=WriteTransaction

do like this

masterqry.post;
detailqry.post;

try
  UpdateTransaction.start
  masterqry.commitupdates;
  detailqry.commitupdates; 
  storedProcedure.parambyname('P_ID').value:=masterqryID.value
  storedProcedure.execsql
  storedProcedure.close;
  UpdateTransaction.commit;
except
  UpdateTransaction.rollback;
  masterqry.cancelUpdates;
  detailqry.cancelUpdates;
  storedProcedure.close;
end

stored procedure like this

ProcMasterTableUpdateBalance(ID)
begin
  update master
     set summa= (select sum(summ) from detail where masterid=:id)
  where  masterid=:id;


  update detail a
     set addamount = (select sum(summ)/0.1 from detail b where b.detailid=a.detailid and  b.masterid=:id)
  where  a.masterid=:id;

  delete from archivedetail where  masterid=:id;
  insert into archivedetail
  select * from detail where  masterid=:id;

end
ain
  • 22,394
  • 3
  • 54
  • 74
Good Bit
  • 23
  • 6
  • You must commit the transaction before execute the stored procedure. – Val Marinov Feb 04 '16 at 07:39
  • Exactly which Firebird 3 version are you using? Do you get the same problem with Firebird 2.5? If not you might have hit a bug with Firebird 3 (which btw is still in development). – Mark Rotteveel Feb 04 '16 at 08:20
  • @ValMarinov If all updates happen in the same transaction as the stored procedure call, than it should not be necessary to commit the transaction before calling the stored procedure. – Mark Rotteveel Feb 04 '16 at 08:23
  • I am not familiar with delphi, nor devart, but are you sure the stored procedure is executed with the `UpdateTransaction`? If not that could explain your problem. You might also want to consider using a trigger for what your stored procedure is doing. – Mark Rotteveel Feb 04 '16 at 09:28
  • How about wrong or error except transaction.. When commit before call procedure or when call procedure how rollback? – Good Bit Feb 04 '16 at 09:30
  • I would like to do all save master and detail and call procedure in one transaction? Is it imposible.? – Good Bit Feb 04 '16 at 09:33
  • @Mark Your comment answered to what you have written :) – Val Marinov Feb 04 '16 at 09:35
  • @GoodBit Consider using a single transaction instead? – Mark Rotteveel Feb 04 '16 at 09:39
  • @ValMarinov So if I understand you correctly, devart/delphi will use the `ReadTransaction` for executing the stored procedure here? – Mark Rotteveel Feb 04 '16 at 09:39
  • ReadTransaction for read Update Transaction for write data – Good Bit Feb 04 '16 at 10:15
  • That I understand, but do you realise that this problem could stem from the stored procedure being executed using the `ReadTransaction`. – Mark Rotteveel Feb 04 '16 at 17:45
  • FB requires an open transaction, try CommitRetaining instead: [link](https://www.devart.com/ibdac/docs/?devart.ibdac.tibctransaction.commitretaining%28%29.htm) – FredS Feb 04 '16 at 20:49
  • @FredS In general you should avoid commit retaining like the plague. – Mark Rotteveel Feb 05 '16 at 07:22
  • Ok A will try delete UpdateTransaction property StoredProcedure – Good Bit Feb 05 '16 at 09:33

0 Answers0