Re: lock mode 0 and 1

From: <fitzjarrell_at_cox.net>
Date: 7 Jun 2006 14:09:56 -0700
Message-ID: <1149714596.087732.57880@g10g2000cwb.googlegroups.com>

niy38_at_hotmail.com wrote:
> what's the difference?

Much, apparently.

Lock mode 0 is a request for a lock from a session; if one session holds a lock which another session requests the requesting session is assigned a Lock Mode of 0.

Lock mode of 1 is, according to Oracle, no lock; a script provided on Metalink some time back provides this DECODE statement:

    decode(l.locked_mode,

             1,'No Lock',
             2,'Row Share',
             3,'Row Exclusive',
             4,'Share',
             5,'Share Row Excl',
             6,'Exclusive',null) locked_mode,

Therefore a Lock Mode of 1 indicates no lock is present -- strange, as one would think if no lock exists no record in V$LOCK would exist, either. I've found one case of Lock Mode 1 in my 10g installation, and it's associated with the CKPT process:

       SID SERIAL# PID SPID PROGRAM LMODE TY

---------- ---------- ---------- ------------ --------------------
---------- --
       165          1          7 844          ORACLE.EXE (CKPT)

    1 XR

The XR lock type is used for forced logging or database quiesce operations, according to the details available in X$KSQST:

KS KSQSTEXPL

--
--------------------------------------------------------------------------------
XR Lock held during database quiesce
XR Lock held during database force logging mode

I hope this helps.


David Fitzjarrell

Received on Wed Jun 07 2006 - 16:09:56 CDT