Lock problem

[Valgardur]

Hi, I have encountered a strange locking twice in the last two years..

My application hangs waiting to update and even if I kill my process and restart it still hangs.

Finding another "guilty" application (manually) and killing that process makes my application pick up where it left.

Is there a safe way to detect a lock by another process and cast an error? Only, of course, if that lock is holding for some time.

I have played with a few options, but as it is difficult to recreate and as everything is running smoothly apart from the two instances over a two year period... I have been reluctant to make significant changes..

[AlexP]

Hello,

Your problem is most probably due to a deadlock occurrence. To detect such locks, you can use the following query:

select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null

[Valgardur]

Thanks, I know... this is how I solve it.

But this is a procedure that runs every minute and simply hangs when this occurs. Is there anyway to cast an error so I will know?

[AlexP]

Hello,

If you get an ORA-00060 error, then to localize it, you should enable trace and analyze the retrieved log file. More detailed info about resolving problems related to deadlocks can be found at the Oracle forum.

[Valgardur]

Thanks, but this is not quite what I am looking for.. my question is if I can set any option / parameter / property on ODAC's database connection so that it casts an error in case of deadlock after a given timeout.

[AlexP]

Hello,

Until Oracle returns an error about lock, the program will wait for response from Oracle, and you can obtain the information about lock only from system views. To solve the problem with application hang on locks, you can use FOR UPDATE queries, setting wait timeout (WAIT XX) or with no timeout (NOWAIT).

[Valgardur]

Thanks, may be what I am looking for... but I cannot find where to define this.

[AlexP]

Hello,

If you are using explicit UPDATE queries when modifying data, you should try to lock the record before query execution, using the query:

Code: Select all

SELECT * FROM TABLE WHERE ID= XXX FOR UPDATE NOWAIT

If the record is already locked by another session, you will get a corresponding error; otherwise - you can modify the record:

Code: Select all

UPDATE TABLE
SET FIELD = YYY
WHERE ID = XXX

If you use Edit/Post methods for record modification, then to reach such behaviour, you should set the lockMode property of the DataSet to lmLockDelayed or lmLockImmediate, and the RefreshOptions property - to BeforeEdit.

[Valgardur]

OK, I is this the only way? I was hoping for a property on either the connection or query component..

[AlexP]

Hello,

As I wrote you earlier, if you use Edit/Post methods (or Grid) for data modifying, then to get such behavior, you should use the lockMode and RefreshOptions DataSet methods:

Code: Select all

OraQuery.lockMode := lmLockImmediate;
OraQuery.RefreshOptions  := [BeforeEdit];

[Valgardur]

Thanks, I understand that, but where do I set the timeout?

[AlexP]

Hello,

For the time being, timeout is not supported. If you want to use locking with TimeOut, you should yourself specify SQLLock queries:

Code: Select all

  OraQuery1.SQL.Text := 'SELECT * FROM DEPT';
  OraQuery1.SQLLock.Text := 'SELECT * FROM DEPT WHERE DEPTNO = :DEPTNO FOR UPDATE WAIT 60';

[Valgardur]

OK, thanks, can you add this to a wishlist??

[AlexP]

Hello,

You can add such a suggestion on our UserVoice forum where other users can vote for it: http://devart.uservoice.com/forums/1046 ... components