Transactions in UniDAC with Firebird

[HelgeLange]

Hello there,

we're currently using ZEOS in our application but I (chief programmer) want to change to UniDAC. We did a test some time ago, speed was much better, but I got into some trouble with my transaction. Some data arrived in my database, other data not. Due to time issues, I tabled it until now and this morning I changed again from ZEOS ti UniDAC in a copy of my code and still have some issues with transactions. Lock conflicts etc.

I will explain a typical error I get right now and how I use the connection and transactions atm.

1. I have one global connection that is used by every form. I assigned a default transaction as read-only for the test.
2. Every form runs with its own transaction (as forms can be opened in multiple instances)
3. I have calls to do stuff in the database through stored procedures. Those calls use their own transaction, too.

Now the problem:

I call a stored procedure from (3) with its own transaction. I see how the transaction is started (I tried starting it before and after assigning the transaction to the TUniStoredProcedure, same result) in both application and DBMonitor (great tool, btw. kudos). The transaction is configured as read/write.

Code: Select all

procedure TDMFAC.__On_Descontabilizar_FA(SubHookID: Integer; QHS: pQHS; UserParam: Pointer; var ErrCode: Integer);
begin
  If ((QHS^.pParams.ParamByName['Clase'].Value = 'FA') Or (QHS^.pParams.ParamByName['Clase'].Value = 'DV')) Then begin
    QHS^.pParams.ParamByName['Result'].Value := False;
    try
      if not SP_OE_DESCONTABILIZAR_DOCUMENTO.Transaction.Active then
        SP_OE_DESCONTABILIZAR_DOCUMENTO.Transaction.StartTransaction;
      SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('E').AsInteger := QHS^.pParams.ParamByName['E'].Value;
      SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('S').AsInteger := QHS^.pParams.ParamByName['S'].Value;
      SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('Tipo').AsString := QHS^.pParams.ParamByName['Tipo'].Value;
      SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('Numero').AsInteger := QHS^.pParams.ParamByName['Numero'].Value;
      SP_OE_DESCONTABILIZAR_DOCUMENTO.ExecProc;
      QHS^.pParams.ParamByName['Result'].Value := True;
    except
      // do some stuff here
    end;
  end;
end;

When I reach SP_OE_DESCONTABILIZAR_DOCUMENTO.ExecProc;, an error occurs, that I try to update in a read-only-transacion (see (1)).
If I try with a read/write-transaction as global transaction in (1), no error occurs but as I do no commit during the life-time of the program, no update is visible. So the document I try to process in the code still appears to be unprocessed and a 2nd call to the stored procedure gives me a dead-lock in no_wait transaction. Of course, the update from the first call are still pending.

It is pretty save to assume, that in the first call the read-only global transaction is used, although I assigned the read/write one.
In the 2nd call (when I use a read/write model for the global one) it appears to be used another transaction, because the lock conflict can appear only when another transaction is used.

Any idea what can cause that behavior ?
I really want to get away from ZEOS as soon as possible, but I cannot convince my boss to buy any component, if I cannot prove that it will work.

Thanks in advance,
Helge Lange

[HelgeLange]

Still having the same problem, but I made some progress in describing the problem better

The TUniStoredProc object cerated in the datamodule seems to be faulty with the transaction. The object is created when the DLL is loaded (directly after program start)
The DFM for the object is as follows :

Code: Select all

  object SP_OE_DESCONTABILIZAR_DOCUMENTO: TUniStoredProc
    StoredProcName = 'OE_DESCONTABILIZAR_DOCUMENTO'
    Left = 232
    Top = 224
    ParamData = <
      item
        DataType = ftInteger
        Name = 'E'
        ParamType = ptInput
      end
      item
        DataType = ftInteger
        Name = 'S'
        ParamType = ptInput
      end
      item
        DataType = ftString
        Name = 'TIPO'
        ParamType = ptInput
      end
      item
        DataType = ftInteger
        Name = 'NUMERO'
        ParamType = ptInput
      end>
  end

Now when I run the following code the object will iuse in its first call always the global transaction, no matter what is assigned (but show me, that it uses the "right" one). I know that, because the change of the read-only property has direct effect on the execution

Code: Select all

procedure TDMFAC.__On_Descontabilizar_FA(SubHookID: Integer; QHS: pQHS; UserParam: Pointer; var ErrCode: Integer);
begin
  try
    SP_OE_DESCONTABILIZAR_DOCUMENTO.Connection := TUniConnection(QHS^.pDB);
    SP_OE_DESCONTABILIZAR_DOCUMENTO.Transaction := TUniTransaction(QHS^.pTransaction);
    SP_OE_DESCONTABILIZAR_DOCUMENTO.UpdateTransaction := TUniTransaction(QHS^.pTransaction);
    SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('E').AsInteger := QHS^.pParams.ParamByName['E'].Value;
    SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('S').AsInteger := QHS^.pParams.ParamByName['S'].Value;
    SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('Tipo').AsString := QHS^.pParams.ParamByName['Tipo'].Value;
    SP_OE_DESCONTABILIZAR_DOCUMENTO.ParamByName('Numero').AsInteger := QHS^.pParams.ParamByName['Numero'].Value;
    SP_OE_DESCONTABILIZAR_DOCUMENTO.Execute;
    QHS^.pParams.ParamByName['Result'].Value := True;
  except
    on E: Exception do begin
      Raise Exception.Create(E.Message);
    end;
  end;
end;

So if the Connection.DefaultTransaction.ReadOnly = True it will tell me in the exception that I try to update in a read-only-transaction, although "QHS^.pTransaction" contains another transaction that doesn't have the flag.
If i put Connection.DefaultTransaction.ReadOnly = False then it raises no exception, but a second call will cause a lock conflict on no_wait transaction and before running the function a 2nd time I can't see the changes made by the SP.

On the other hand if I use the following code everything works fine :

Code: Select all

procedure TDMFAC.__On_Descontabilizar_FA(SubHookID: Integer; QHS: pQHS; UserParam: Pointer; var ErrCode: Integer);
var SP : TUniStoredProc;
begin
  try
    SP := TUniStoredProc.Create(Self);
    SP.Connection := TUniConnection(QHS^.pDB);
    SP.Transaction := TUniTransaction(QHS^.pTransaction);
    SP.UpdateTransaction := TUniTransaction(QHS^.pTransaction);
    SP.StoredProcName := 'OE_DESCONTABILIZAR_DOCUMENTO';
    SP.Prepare;
    SP.ParamByName('E').AsInteger := QHS^.pParams.ParamByName['E'].Value;
    SP.ParamByName('S').AsInteger := QHS^.pParams.ParamByName['S'].Value;
    SP.ParamByName('Tipo').AsString := QHS^.pParams.ParamByName['Tipo'].Value;
    SP.ParamByName('Numero').AsInteger := QHS^.pParams.ParamByName['Numero'].Value;
    SP.Execute;
  except
    on E: Exception do begin
      Raise Exception.Create(E.Message);
    end;
  end;
end;

So basically the change is that I create the SP object by hand, but at least it works.

So the question is... what's the difference for the UniDAC code ?

Thanks in advance.

[ZEuS]

Thank you for the information. We have reproduced the error and fixed it. The fix will be included in the next UniDAC build.
For now, we can send you a night build including the fix. Please send an email to eugeniyz*devart*com, in which specify your license number (if you are a registered user) and the exact version of the IDE you are using.