Some comments on your code:

A rule of thumb for IBO is don't have nil owners for components except maybe
the TIB_Session component. If you are creating components use the
TIB_Session instance as the owner.

You don't need to worry about explicitly setting the SQL Dialect to 3 unless
you are creating a database from scratch. Once created the database itself
tells you what its dialect is.
Hope this helps.

I also think it is a good idea to use pools of module instances and allow a
thread to be active in them at will. Only use a critical section to pull
instances in and out of a pool, not to embrace the entire operation. The
rest of it should be queue management like allowing your requests that don't
have an available module instance to be processed with to go into a file
stream and then when modules are freed up the check for waiting items in the
file queue. You might also consider using virtual memory for that too but I
would be careful about the possibility of getting slammed and run out of
virtual memory in case a failure occurred. of course you could limit the
number of items in the queue and then reject incoming requests if the queue
is too full.

As for deadlocks, when using LockWait you are more likely to see what
appears as a frozen process. Especially since you are actually performing
potentially frozen operations within a critical section.

Hope these thoughts and suggestions are helpful.

I'll have to freshen up me head a little to digest your code in detail.

Regards,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Gordon Hamm" <vds@...>
To: <IBObjects@yahoogroups.com>
Sent: Thursday, August 09, 2001 12:53 PM
Subject: [IBO] Dead Locks?


>
> Hi All!
> I've had great success so far in my app . Its up and running but have
> ocasional dead lock situations..
> If you remember, Ive got a phone box that processes calls.. I share (using
a
> crit section) a few querys / connection / transaction..
> My Phone system Constructor code looks like..
>
> IbConnection := TIB_Connection.create(nil);
> IbConnection.SQLDialect := 3;
> IbConnection.LoginPrompt := False;
> IbConnection.protocol := cpTCP_IP;
>
> IBTransaction := TIB_Transaction.create(nil);
> IBTransaction.IB_Connection:=ibconnection;
> IBTransaction.LockWait:=True;
> IBTransaction.Isolation:=TiCommitted;
> IBTransaction.RecVersion:=True;
>
> IbConnection.DefaultTransaction:=IBTransaction;
>
> PIN_StoredProc := TIB_cursor.create(nil);
> Pin_Storedproc.IB_Connection := IbConnection;
> Pin_Storedproc.IB_Transaction:=IBTransaction;
> Pin_Storedproc.sql.add('EXECUTE PROCEDURE VALIDATE_PIN(:V_PIN)');
>
>
> I have Isolation set to committed, recversion set to true, as I dont care
> about versions ( I dont think) I also have lockwait set to True.
>
>
> In my code where I call the stored proc, it looks like...
>
>
>
>
> procedure TSwitchData.Validate_PIN(var ChannelData: TCaller_Account_Info);
> begin
> EnterCriticalSection(DB_CS);
> try
> with ChannelData do
> begin
> if Pin <> '' then
> begin
>
> balance := 0;
> Custid := '';
>
> Status := UnknownError;
> IBTransAction.StartTransaction;
> try
>
> Pin_StoredPROC.ParamByName('V_PIN').AsString := PIN;
> Pin_StoredPROC.EXECUTE;
> Status := pin_storedproc.fieldbyname('V_RESULT_CODE').asInteger;
>
> if status = 0 then
> begin
> Custid := pin_storedproc.fieldbyname('V_Custid').asString;
> Is_Prepaid :=
> (Pin_StoredProc.fieldbyname('V_IS_PREPAID').asString = 'Y');
> BillToName :=
> Pin_StoredProc.fieldbyname('V_BillToName').asString;
> CallerLast :=
Pin_StoredProc.fieldbyname('V_PINLAST').asString;
> Callerfirst :=
> Pin_StoredProc.fieldbyname('V_PINFirst').asString;
> balance := Pin_StoredProc.fieldbyname('V_Balance').asFloat;
> Credit_limit :=
> Pin_StoredProc.fieldbyname('V_Credit_limit').asFloat;
> RUNNING_CALL_COST :=
> Pin_StoredProc.fieldbyname('V_RUNNING_CALL_COST').asFloat;
> cc_Rounding :=
> Pin_StoredProc.fieldbyname('V_cc_Round').asInteger;
> LD_Rounding :=
> Pin_StoredProc.fieldbyname('V_LD_Round').asInteger;
> Hop_Rounding :=
> Pin_StoredProc.fieldbyname('V_Hop_Round').asInteger;
> BillMinOfXSeconds :=
> Pin_StoredProc.fieldbyname('V_Bill_Min_XSeconds').asInteger;
> CallerLast := Pin_StoredProc.fieldbyname('V_Last').asString;
> Hop_Rate := Pin_StoredProc.fieldbyname('V_Hop_Rate').asFloat;
> LD_Rate := Pin_StoredProc.fieldbyname('V_LD_Rate').asFloat;
> CC_Rate := Pin_StoredProc.fieldbyname('V_CC_Rate').asFloat;
> LD_ISACTIVE :=
> (Pin_StoredProc.fieldbyname('V_LD_ISACTIVE').asString = 'Y');
> HOP_ISACTIVE :=
> (PIN_StoredProc.fieldbyname('V_HOP_ISACTIVE').asString = 'Y');
> CC_ISACTIVE :=
> (PIN_StoredProc.fieldbyname('V_CC_ISACTIVE').asString = 'Y');
> Ask_for_Acc_code :=
> (PIN_StoredProc.fieldbyname('V_Ask_For_Acc_Code').asString = 'Y');
> Acc_Code_Size :=
> PIN_StoredProc.fieldbyname('V_Acc_Code_Size').asinteger;
> Verify_Acc_Code :=
> (PIN_StoredProc.fieldbyname('V_Verify_Acc_Code').asString = 'Y');
> IBTransAction.Commit;
> pin_storedproc.UnPrepare;
> end
> else
> begin
> IBTransaction.Rollback;
> pin_storedproc.UnPrepare;
> Exit;
> end;
> except
> on E: exception do
> begin
> Status := UnknownError;
> file://SHOWMESSAGE(E.MESSAGE);
> LogError('Error , ValidatePIN() ' + Pin + ' ' + E.Message);
> IBTransaction.Rollback;
> pin_storedproc.UnPrepare;
> end;
> end;
> end
> else
> Status := BadValuePassedToStoredProc;
> end;
> finally
> LeaveCriticalSection(DB_CS);
> end;
> end;
>
>
> The phone switch will call this proc to set a flag saying that a PIN as
> being used, then when the call is over, it is un flagged by the phone
> system.
>
>
> Sometimes, the pin update will fail, saying its a dead lock. The only way
I
> can resolved it is to sweep (at least this seems to fix it)
>
>
> Am I on the right track? Im I totally missing something?
>
> BTW,
>
> What does sweepinterval do? Is that something that I should set?
>
> What is ServerautoCommit V.S. Autocommit?
>
>
> Thanks for all the help that you have given me thus far! Its working great
> other than this dead lock situation.
>
> Gordon Hamm
>
>
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>