Re: Locking mode in 10g seems to have changed

Comments in-line.

claytonarends_at_gmail.com wrote:
> 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.

9i and 10g make a very substantial departure from Oracle 8i and before. It sounds like you have a dinosaur for an app that needs to be upgraded quicly while the company still has customers.

> 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.

This was a bad design even back in Oracle 7.

> 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.
Very bad design.

> 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?

No this is a bad design that, with changes in Oracle over the last decade has finally broken.

> Is there a configuration setting to control this behavior?

Wrong direction.

> Any other thoughts on this?

Get your team trained on 10g and rewrite your code to be scalable. If this was put on a RAC cluster it would beg for mercy.

If I can help referring you to a trainer in your area contact me off-line.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org