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;
//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