Re: Locking mode in 10g seems to have changed

On 2 Aug 2006 18:50:27 -0700, 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.
>
>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.

So you have an unscalable app. That problem should be resolved! I don't agree it can't be done. It can. Put it away in pl/sql Do you prefer the end-users suffer from an unscalable app? Sometimes one has just to bite the bullet and to suffer the incompetency of previous developers..
>
>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.
You are using explicit locking. Could you explain why? Oracle locks automatically. The lock table statement below is redundant.
>
>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".
>

There are two different lock types involved. You don't show *which* object has the lock. The DML lock definitely isn't a record lock. Are you sure you aren't comparing apples and pears?
>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?

If there would have been changed behavior, that would have been documented in the new features manual and/or the release notes. Did you verify them (prior to installing 10g)?
>
>Thank you for your insight,
>- Clayton

--
Sybrand Bakker, Senior Oracle DBA