Re: lock mode 0 and 1


niy38@xxxxxxxxxxx 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

.

Relevant Pages

  • RE: IIS bug-Concurrent request lock before IHttpModule.AcquireRequ
    ... session during the request and required you to put locks around session ... "optimistic," and then require all session state modification to have a lock ... public override void ProcessRequest ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Advice on mod_lisp-based web application.
    ... B> I do have a good deal of state in global variables. ... B> recommendation is to migrate those to "session variables". ... so you can lock this mutex on all request ...
    (comp.lang.lisp)
  • Re: Locking mode in 10g seems to have changed
    ... unique id and for locking a record while the user works on its data ... The lock table statement below is redundant. ... Here is the "lock for editing" script: ... The 10g process has a lock mode of "3" and the 9i ...
    (comp.databases.oracle.misc)
  • Re: Let me be the first
    ... and other DB's let you set your lock timeout value at the instance/database level. ... Informix gives you the same functionality, ... The solution is to take advantage of Informix' SET LOCK MODE syntax. ...
    (comp.databases.informix)
  • Re: Clarification of LOCK clauses in 2002
    ... then READ places a LOCK, but that WRITE and REWRITE *UNLOCK" the record. ... If the AUTOMATIC phrase is specified, the lock mode is automatic. ... no LOCK clause is valid when LOCK MODE is AUTOMATIC. ... it is not possible to obtain a lock on REWRITE/WRITE? ...
    (comp.lang.cobol)