Locking mode in 10g seems to have changed

All,

I have an issue with an application that my company created. Locking code that has been working splendidly for the last 8 years is all of a sudden breaking as clients upgrade their Oracle servers to 10g.

The locks in question are used for determining the next available unique id and for locking a record while the user works on its data (which can take several minutes sometimes). I know some of you are cringing right now. It is far too late to convert to sequences. It would be a maintenance nightmare at this point.

Here is the scenario. If a user has a record locked for update and another user needs to create a new record in the same table (and therefore needs to lock to get next id) then my app receives an ORA-00054. Here is the "lock for editing" script (let's call it script 1):   select <fields> from <table> where <condition> for update nowait

Here is the "lock to retrieve next id" script (let's call it script 2):   lock table <table> in share row exclusive mode nowait

In versions prior to 10g the common scenario is a user will run script 1 and any number of users can run script 2 or script 1 (provided it isn't for the same record). With 10g any number of users can run script 1 (on different records, of course) but not a single user can run script 2 if even one lock still exists from script 1.

If I run the script "select lock_type, mode_held from dba_lock" while a lock is being held then the following two records are returned for the process in question on the 10g server:

    LOCK_TYPE = DML
    MODE_HELD = Row-X (SX)
    LOCK_TYPE = Transaction
    MODE_HELD = Exclusive

If I run the same script on a 9i server the following are returned for the locking process:

    LOCK_TYPE = Transaction
    MODE_HELD = Exclusive
    LOCK_TYPE = DML
    MODE_HELD = Row-S (SS)

Note the locking mode difference. The same difference can be noted by looking in v$lock. The 10g process has a lock mode of "3" and the 9i process has a lock mode of "2".

I get these same results if I manually run the locking scripts from SQLPlus on the server rather than through our application.

Was this an Oracle oversight? Is there a configuration setting to control this behavior? Any other thoughts on this?

Thank you for your insight,
- Clayton Received on Wed Aug 02 2006 - 20:50:27 CDT