Select for update behaves differently in 9i and 10g

[cglisc]

We have this statement in our database:
select <primary key> from <table> where <primary key> = 1 for update.

Issuing the above statement in 9i, results in a ROW-S (SS) shared type of lock on the table. However, in an identical database in 10g, it results in ROW-X (SX)type of lock. we see all that by qeurying view DBA_DML_LOCKS. Why the different behavior in 10g? Or is it an ORA.INI setting that causes this behavior?

In addition, in 10g the select for update blocks other users trying to access different rows in the table thru oracle forms 6i application. We see this because DBA_DML_LOCKS reports "Blocking" in column "BLOCKING_OTHERS" for the specific row.

If you can please explain.

Thank you
chris

 

[SantaMufasa]

Oracle's First Law of Locks:

Readers do not block writers; Writers do not block readers.

If Oracle, itself, is not following that rule, then it is a bug of which you must make them aware.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]