TUniTransaction uses commitretaining

sir.wally.lewis · Post by **sir.wally.lewis** » Mon 21 Mar 2011 22:53

I am using Unidac, with Firebird.
My system is slowing down drastically after not long in processing.

I went on the firebird groups.
And they said it was because My application was doing a "Commit Retaining"
see: http://tech.groups.yahoo.com/group/fire ... age/112577

I then used the monitor tool and found out they were right! see below results from TUniSQLmonitor.

What can I do to stop this "CommitRetaining" thing?

eg.
20110321155428141
Start:
20110321155428345
EXECUTE PROCEDURE PROC_CRT_OPR_SGN_ON(:INID_PID, :INPW_ACS_OPR, :INNM_USR)
:INID_PID(Integer,IN)=7676
:INPW_ACS_OPR(WideString[6],IN)='motherhen'
:INNM_USR(WideString[7],IN)='admin'
:OUTSC_DV(SmallInt,OUT)=
:OUTID_OPR(Integer,OUT)=
:OUTID_SFT_INT(Integer,OUT)=
20110321155428360
CommitRetaining:
20110321155428673
SELECT COUNT(*) FROM (
SELECT 1 AS C
FROM DO_POS_LOG D
JOIN LO_BSN_UN BU
ON( BU.ID_BSN_UN = D.ID_BSN_UN )
JOIN ST_ASCTN_BSNGP BG
ON( BG.ID_BSNGP_FNC = 1 )
AND( BU.ID_BSN_UN = BG.ID_BSN_UN )
JOIN PA_PRTY_OPRPA
ON( BU.ID_PRTY = PA_PRTY_OPRPA.ID_PRTY )
JOIN LO_DS DS
ON( D.ID_DS_DST = DS.ID_DS )
JOIN LO_STE STE
ON( STE.ID_LCN_GEO_PHY = D.ID_LCN_GEO_PHY )
WHERE( D.CD_STS = 0 )
)

AndreyZ · Post by **AndreyZ** » Tue 22 Mar 2011 09:53

Hello,

You can avoid using the CommitRetaining method by UniDAC in two ways:
1) explicitly start transactions. Here is an example:

Code: Select all

UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'select * from test_table';
UniQuery.Open;
UniConnection.StartTransaction;
UniQuery.Edit;
UniQuery.FieldByName('test_field').AsString := 'test';
UniQuery.Post;
UniConnection.Commit;

2) use different transactions in the Transaction and UpdateTransaction properties of the TUniQuery and TUniTable components. Here is an example:

Code: Select all

UniQuery.Transaction := UniTransaction1;
UniQuery.UpdateTransaction := UniTransaction2;
UniQuery.SQL.Text := 'select * from test_table';
UniQuery.Open;
UniQuery.Edit;
UniQuery.FieldByName('test_field').AsString := 'test';
UniQuery.Post;

sir.wally.lewis · Post by **sir.wally.lewis** » Wed 23 Mar 2011 05:15

Implemented option 2.

Unfortunately problem not solved.

I have just sent an email to [email protected]

with implementation information and monitor logs.

AndreyZ · Post by **AndreyZ** » Wed 23 Mar 2011 12:58

I have answered you by e-mail.