Insert locking Delete on the same table ?

lior · Post by **lior** » Tue 20 Jan 2015 14:44

Hello,
Using ODAC Standad edition Version 8.0.2 for Delphi 7.
One instance of my application is editing a record using a TOraQuery component.
Another instance of my application is hanged when it gets to the line TOraQuery.Delete.
The second program advance only when the first program transaction is commited/rolledBack.
Each transaction is working on a diferent record in that table.
Was there a bug fix to that problem? I saw a post about a similar problem regarding tables with blobs.

delphi7 · Post by **delphi7** » Tue 20 Jan 2015 15:27

maybe i help you with this:

try to use :
LOCK table name_of_table IN EXCLUSIVE MODE;

when you use the poarallel DML operation on the same table.

lior · Post by **lior** » Wed 21 Jan 2015 08:17

Doesn't lock table in exclusive mode lock the entire table ?
I only want the inserted and deleted records to be locked.
I have no problem if one program is inserting a record and the other is editing a different record.
The problem is when one program is inserting a new record and another program is deliting an existing record.

AlexP · Post by **AlexP** » Wed 21 Jan 2015 09:15

Hello,

You are using an obsolete ODAC version, please try to reproduce the problem on the latest trial ODAC version 9.4.14 http://www.devart.com/odac/download.html , and if the problem repeats, contact us again.

lior · Post by **lior** » Wed 21 Jan 2015 14:25

Hello,
I installed ODAC 9.4.14 for delphi 7.
Delete action still hangs until the insert transaction in another program is commited or rolled back.

delphi7 · Post by **delphi7** » Thu 22 Jan 2015 07:08

hey lior,
its an intersting problem, can we see an example about your code?

thanks

AlexP · Post by **AlexP** » Thu 22 Jan 2015 11:28

Please provide the complete project to support*devart*com, in order for us to be able to reproduce the problem.

lior · Post by **lior** » Mon 26 Jan 2015 15:44

Problem solved. Some of the detail tables didn't have an index on foreign key fields. These fields were defined with the on delete cascade command.
I use Oracle11G + ODAC. Wonder why that problem didn't popup when I was using Oracle8I+BDE ?

AlexP · Post by **AlexP** » Tue 27 Jan 2015 12:03

Unfortunately, we can't tell the exact reasons for such behavior without reproducing the problem.. This may be due to the Oracle server behavior in different versions.

Devart Forums

Insert locking Delete on the same table ?

Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?

Re: Insert locking Delete on the same table ?