Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Database Deadlock Issue


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 1 - Last Post: Jan 13, 2017 10:50 PM Last Post By: Jeff Overcash (...
Vishal Tiwari

Posts: 10
Registered: 3/27/10
Database Deadlock Issue  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 12, 2017 10:33 PM
Hi Dear All,

We are using Delphi 6 and Firebird 2.5.

We have below components and with their respective properties assigned at "DESIGN TIME" itself.

Below code is just for reference that how the components are connected to each other.

Components:
Code:

sqlMain : TIBSQL;
dbMain : TIBDatabase;
trnMain : TIBTransaction;

TIBSQL:
Code:
sqlMain.Database := dbMain;
sqlMain.Transaction := trnMain;

TIBDatabase:
Code:
dbMain.DefaultTransaction := trnMain;

TIBTransaction:
Code:
trnMain.DefaultDatabase := dbMain;

We are frequently getting an error like:

Actual Error Message:

There was an error processing this cancellation on main server.
lock conflict on no wait transaction
deadlock
update conflicts with concurrent update
requires both input and output filenames

URL (for reference) for above error message:
Lock conflict on no wait transaction:
http://www.firebirdfaq.org/faq109/
My doubts are as below:

1. Since these components are connected to each other at Design Time, do we need to write below code to start and close the Transaction.

Delphi code snippet should be:

Code:

try
sqlMain.Transaction.StartTransaction;

//Have Insert SQL Execution here

sqlMain.Transaction.Commit;
except
sqlMain.Transaction.Rollback;
end;

2. Or since these components are connected to each other at Design Time, the transaction automatically gets started when we execute the respective SQL using below statement, trasaction automaticaaly gets started and ends automatically.

Code:
sqlMain.ExecQuery;
There are places where the Insert/Update SQL is getting executed alone or inside the loop as well.

How to resolve such errors.

Thanks In Advance.

With Best Regards.

Vishal

Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Database Deadlock Issue  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 13, 2017 10:50 PM   in response to: Vishal Tiwari in response to: Vishal Tiwari
Vishal Tiwari wrote:
Hi Dear All,

We are using Delphi 6 and Firebird 2.5.

We have below components and with their respective properties assigned at "DESIGN TIME" itself.

Below code is just for reference that how the components are connected to each other.

Components:
Code:

sqlMain : TIBSQL;
dbMain : TIBDatabase;
trnMain : TIBTransaction;

TIBSQL:
Code:
sqlMain.Database := dbMain;
sqlMain.Transaction := trnMain;

TIBDatabase:
Code:
dbMain.DefaultTransaction := trnMain;

TIBTransaction:
Code:
trnMain.DefaultDatabase := dbMain;

We are frequently getting an error like:

Actual Error Message:

There was an error processing this cancellation on main server.
lock conflict on no wait transaction
deadlock
update conflicts with concurrent update
requires both input and output filenames

URL (for reference) for above error message:
Lock conflict on no wait transaction:
http://www.firebirdfaq.org/faq109/
My doubts are as below:

1. Since these components are connected to each other at Design Time, do we need to write below code to start and close the Transaction.

Delphi code snippet should be:

Code:

try
sqlMain.Transaction.StartTransaction;

//Have Insert SQL Execution here

sqlMain.Transaction.Commit;
except
sqlMain.Transaction.Rollback;
end;


Yes for IBSQL you need to always manually start your transaction. TDataset
descendants for IBX will auto start the transaction (as long as the default for
AllowAutoStart stays true), but IBSQL does not ever auto start a transaction.

You would get a different error message though about transaction not started.


2. Or since these components are connected to each other at Design Time, the transaction automatically gets started when we execute the respective SQL using below statement, trasaction automaticaaly gets started and ends automatically.

Code:
sqlMain.ExecQuery;
There are places where the Insert/Update SQL is getting executed alone or inside the loop as well.


Nope, for TIBSQL you will get a Transaction not started exception if you try to
execquery and the ibsql's transaction property points to a inactive transaction.

How to resolve such errors.

Thanks In Advance.

With Best Regards.

Vishal


Deadlock errors means you have two transactions, one modifies record A but has
not yet committed or rollbacked and a different transaction tries to modify
record A also (be aware the triggers might be modifying secondary records and
those records might be what is causing hate deadlock).

Basically you track this down by looking at long running transactions,
particularly long running transactions that modify records. You can always call
commitretaining to commit your work so far but keep your transaction context.
This allows other transactions to due more work on the record while hte first
process has moved on to other records.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02