"FOR UPDATE NOWAIT" does no row locking?!

Moehre · Post by **Moehre** » Mon 23 Jul 2007 13:39

Dear sirs!

I wrote on application in delphi5 with odac 5.8 on an oracle 10g database. In this app you can enter sql commands which will be executed using an OraQuery component. So if I select one row from a test table in app1 ("SELECT * FROM TEST WHERE ID = 1 FOR UPDATE NOWAIT") and do the same thing in app2 there should be an ORA-00054 error but it is not! I can select the same row with nowait option in the 2nd application! If I then do an update the 2nd application is blocked until the transaction is finished.

Why does the TOraQuery component not block the row when I use "FOR UPDATE NOWAIT"? Using SQLPlus the same commands work as expected; I realized that there are 2 blocking informations using SQLPlus but only one using my app?!

I searched for "NOWAIT" in this forum but did not found a solution for my problem.

Thx in advance
Detlev Moehring

Plash · Post by **Plash** » Tue 24 Jul 2007 07:21

Check that the AutoCommit property of the TOraQuery component is set to False.

Moehre · Post by **Moehre** » Tue 24 Jul 2007 14:57

Hey Plash!

AutoCommit was disabled in TOraQuery component, but enabled in TOraSession component! After I disabled it there too, it reacts VERY strange:

- I select row x with nowait option in application 1 = OK
- I select row x with nowait option in application 2 -> noe
oracle 00054 error ???
- I select row x with nowait option in application 1 again ->
ORA00054 ????

Very strange. I will test other properties...

Moehre · Post by **Moehre** » Tue 24 Jul 2007 16:21

I activated DEBUG in both TOraQuery and TOraSession.

When I open TOraQuery with nowait statement, the following debug information is given by TOraSession:

begin :result := sys.dbms_transaction.local_transaction_id(true); end;

:result(VARCHAR[0])=

Is this OK?

I added a second TOraQuery component and entered the SQL Statement fixed as property. It works as expected. Then I replaced the existing TOraSession component with a new one (AutoCommit set to false): Only at the 2nd time it realy locks ?! What the hell is it ...

Moehre · Post by **Moehre** » Wed 25 Jul 2007 07:07

OK, I found it! In my opinion it's a bug in TOraSession component:

To be shure that updates, inserts or deletes are working in transaction mode I request the TOraSession.InTransaction property.

If this is set to "FALSE" I will start a new transaction. The problem here is that the "FOR UPDATE NOWAIT" select will NOT set this property to "TRUE". So when I start a new transaction the "FOR UPDATE NOWAIT" select will be rollbacked!

Greetz
Detlev Moehring

Plash · Post by **Plash** » Wed 01 Aug 2007 10:03

The InTransaction property is set to True only when you call the StartTransaction method. But this property is not set to True if you just execute a query without autocommit.