DEAD LOCK: Can you help me doing TRANSACTIONS in delphi?

[Guest_imported]

Hello,

I'm using interbase components to access my database files.
I receive lots of "dead locks", and don't know what is it?
Here some code:

-----------------------------------------------------------

label tryagain1;
var
error1:boolean;

ibtransaction1.starttransaction;

{I make selects, inserts, updates, etc... here! without commit}

error1:=true;
try
ibtransaction1.commit;
error1:=false;
except
raise;
ibtransaction1.rollback;
error1:=true;
end;

if error1=true then
begin
goto tryagain1;
end;

-----------------------------------------------------------

Any suggestion?

 

[LucieLastic]

Have you tried using CachedUpdates ?

 

[Guest_imported]

I still receiving this error:
"Dead Lock: update conflicts with concurrent update!"

 

[LucieLastic]

Are you applying the updates in the right order when cached?

 

[Guest_imported]

Here the code:


label tryagain1;
var
error1:boolean;

ibtransaction1.starttransaction;

{I make selects, inserts, updates, etc... here! without commit}

error1:=true;
try
if ibquery1.UpdatesPending then ibquery1.ApplyUpdates;
finally
error1:=true;
try
ibtransaction1.Commit;
error1:=false;
except
raise;
ibtransaction1.Rollback;
error1:=true;
end;
end;

if error1=true then
begin
goto tryagain1;
end;

 

[Guest_imported]

I don't need to change transaction properties to snapshot,
read commited, read-only table stability or write-only table stability???

 

[scottostanek]

Take this advice as coming from a Delphi programmer who
doesn't work with Interbase. I think you may have some
kind of timing issue. Your error relates to the part here:

try
if ibquery1.UpdatesPending then ibquery1.ApplyUpdates;
finally

because it refers to the updates. Have you tried to add
some time in the step?

try
if ibquery1.UpdatesPending then
begin
ibquery1.ApplyUpdates;
sleep(500);
close;
finally

Scotto the Unwise

 

[Guest_imported]

Hello again

All messages help me a lot Before I start thanks to you
all

I'm receiving an error in this code, I think I need to
change the transaction properties.
My program open connections to database and then halt.
I think it associated to WAIT/READ/WRITE in the component,
I can't do a SELECT if a connection is open,
my program stop wating for something...

Can you show me an hard code using SELECT, INSERT, UPDATE
in a WHILE using TRANSACTIONS???

Any help about ibtransaction properties.

-----------------------------------------------------------
Here the code:

label tryagain1;
var
error1:boolean;

ibquery2.close;
ibquery2.sql.clear;
ibquery2.sql.add ('SELECT * FROM SILVERPRO');
ibquery2.open;

while not ibquery2.eof do
begin
ibtransaction1.starttransaction;

{I make selects, inserts, updates, etc... here! without commit}

error1:=true;
try
if ibquery1.UpdatesPending then ibquery1.ApplyUpdates;
finally
try
ibtransaction1.Commit;
except
raise;
ibtransaction1.Rollback;
end;
end;
ibquery2.next;
end;

-----------------------------------------------------------
This area is interesting, but complicated Only for advanced programmers

 

[Guest_imported]

Hello again

All messages help me a lot Before I start thanks to you
all

I'm receiving an error in this code, I think I need to
change the transaction properties.
My program open connections to database and then halt.
I think it associated to WAIT/READ/WRITE in the component,
I can't do a SELECT if a connection is open,
my program stop wating for something...

Can you show me an hard code using SELECT, INSERT, UPDATE
in a WHILE using TRANSACTIONS???

Any help about ibtransaction properties.

-----------------------------------------------------------
Here the code:

label tryagain1;
var
error1:boolean;

ibquery2.close;
ibquery2.sql.clear;
ibquery2.sql.add ('SELECT * FROM SILVERPRO');
ibquery2.open;

while not ibquery2.eof do
begin
ibtransaction1.starttransaction;

{I make selects, inserts, updates, etc... here! without commit}

error1:=true;
try
if ibquery1.UpdatesPending then ibquery1.ApplyUpdates;
finally
try
ibtransaction1.Commit;
except
raise;
ibtransaction1.Rollback;
end;
end;
ibquery2.next;
end;

-----------------------------------------------------------
This area is interesting, but complicated Only for advanced programmers

 

[svanels]

I found some information about deadlocks. maybe this shine a light on your problems

Regardless of the SQL server backend, client applications that you develop in a BDE-enabled development tool like Delphi, C++Builder or Paradox using BDE-aware components may encounter a deadlock after issuing a SELECT statement when another client application locks a record by issuing an UPDATE,
DELETE, or INSERT statement.

The default for BDE-initiated statements is a transaction level set up as NO WAIT, READ COMMITTED, NO RECORD VERSIONS. Because BDE does not use record versioning in this mode, the deadlock occurs when a SELECT tries to read a record that another client has not yet committed to the table.

Regards
S. van Els
SAvanEls@cq-link.sr

 

[Guest_imported]

Hello,

If you right-click ibtransaction and then click
"transaction editor" you will see a dialog box.
In that dialog BOX you can select one option of this:
SNAPSHOT, READ COMMITED, READ-ONLY TABLE STABILITY or
READ-WRITE TABLE STABILITY. What of them you recommend to
use?

I know I can change the memo field in dialog and put other
parameters. What are these parameters? I only know this:
concurrency,nowait,wait,read_committed,rec_version,
consistency,read,write. There are more?

What is the configuration you choose to don't have dead
lock's and to make selects with nowait?

Regards,
Carlos

 

[Guest_imported]

Any help about this subject or some examples?