Re: Lock Modes in DBMS_LOCK

On Feb 22, 12:13 pm, "Ed" <e..._at_mail.com> wrote: > what is "aggregate object" referred to in the doco ?

Basically the Oracle User Locks staus match the available Oracle locking modes. Look in the Application Developers Guide - Fundamentals where it talks about Locking Data Explicitly which is followed by a discussion of user locks.

Basically in practical use you want exclusive ownership of the lock and the object the lock protects but sometimes you are willing to share but no one else may update or you need to read the object and will shared access so long as no one can update it while you are reading it.

Using the "Table 46-2 Lock Compatibility" in your reference you can figure out which locks will allow you the access you want imposed on a user process.

The aggregate object refers to the target object. Using the sub-share locks you can take out a UL at the object level and then more userlocks on a lower (logical) level. The right sub-share lock would prevent another session from taking an exclusive lock.

See the $ORACLE_HOME/rdbms/admin/dbmslock.sql memeber for the following
>>

• A sub-share lock can be used on an aggregate object to indicate that
• share locks are being aquired on sub-parts of the object. Similarly, a
• sub-exclusive lock can be used on an aggregate object to indicate
• that exclusive locks are being aquired on sub-parts of the object. A
• share-sub-exclusive lock indicates that the entire aggregate object
• has a share lock, but some of the sub-parts may additionally have
• exclusive locks.

Most of the time you will use X say to prevent a batch job from running twice or one of the share modes to prevent anyone from taking the X mode lock while your process runs and you will not need any of the others. Using the table you can figure out what combination of locks you want to use.

HTH -- Mark D Powell -- Received on Thu Feb 22 2007 - 15:10:39 CST