mos摘v$lock

"V$LOCK" Reference Note

• The information here contains the view description from the Oracle9i documentation along with any additional Support / Historical Notes related to this view.
• Earlier versions of Oracle may not include all columns.

  Oracle9i/10g/11g Information

  • This view lists the locks currently held by the Oracle server and outstanding requests for a lock or latch.

    Column	Datatype	Description
    ADDR	RAW(4)	Address of lock state object
    KADDR	RAW(4)	Address of lock
    SID	NUMBER	Identifier for session holding or acquiring the lock
    TYPE	VARCHAR2(2)	Type of user or system lock The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are: TM - DML enqueue TX - Transaction enqueue UL - User supplied The locks on the system types are held for extremely short periods of time.
    ID1	NUMBER	Lock identifier #1 (depends on type)
    ID2	NUMBER	Lock identifier #2 (depends on type)
    LMODE	NUMBER	Lock mode in which the session holds the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X)
    REQUEST	NUMBER	Lock mode in which the process requests the lock: 0 - none 1 - null (NULL) 2 - row-S (SS) 3 - row-X (SX) 4 - share (S) 5 - S/Row-X (SSX) 6 - exclusive (X)
    CTIME	NUMBER	Time since current mode was granted
    BLOCK	NUMBER	The lock is blocking another lock  In 11.2 the block value can be interpretted thus: value is 0 -> holder is not blocking any waiter on any instance. value is 1 -> holder is blocking at least one waiter on the local instance. It may or may not be blocking waiters on other instances. value is 2 -> Only occurs in RAC. The holder is not blocking any waiter on this instance. It may or may not be blocking waiters on other instances.

    Table 3-1  Values for the TYPE Column: System Types

    System Type	Description	System Type	Description
    BL	Buffer hash table instance	NA..NZ	Library cache pin instance (A..Z = namespace)
    CF	Control file schema global enqueue	PF	Password File
    CI	Cross-instance function invocation instance	PI, PS	Parallel operation
    CU	Cursor bind	PR	Process startup
    DF	Data file instance	QA..QZ	Row cache instance (A..Z = cache)
    DL	Direct loader parallel index create	RT	Redo thread global enqueue
    DM	Mount/startup db primary/secondary instance	SC	System commit number instance
    DR	Distributed recovery process	SM	SMON
    DX	Distributed transaction entry	SN	Sequence number instance
    FS	File set	SQ	Sequence number enqueue
    HW	Space management operations on a specific segment	SS	Sort segment
    IN	Instance number	ST	Space transaction enqueue
    IR	Instance recovery serialization global enqueue	SV	Sequence number value
    IS	Instance state	TA	Generic enqueue
    IV	Library cache invalidation instance	TS	Temporary segment enqueue (ID2=0)
    JQ	Job queue	TS	New block allocation enqueue (ID2=1)
    KK	Thread kick	TT	Temporary table enqueue
    LA .. LP	Library cache lock instance lock (A..P = namespace)	UN	User name
    MM	Mount definition global enqueue	US	Undo segment DDL
    MR	Media recovery	WL	Being-written redo log instance

  ---

  Support and Historical Notes for "V$LOCK"

    View Definition: Use the following SQL to see the view definition of the related GV$ view: SELECT view_definition FROM v$fixed_view_definition
         WHERE view_name='GV$LOCK'; Useful Join Columns: SID    - Join to <> . SID Support Notes: The meaning of ID1 and ID2 depend on the lock TYPE.
  
      ---------------------------------------------------------------------------
      Most Common Lock Types:
      ---------------------------------------------------------------------------
        TYPE  Name			ID1                    	ID2
        ~~~~  ~~~~			~~~                    	~~~
        TX    Transaction		Decimal RBS & slot	Decimal WRAP number
                      		(0xRRRRSSSS  RRRR = RBS number, SSSS = slot )
      	A TX lock is requested in eXclusive mode if we are waiting on a ROW.
  	A SHARE mode request implies we are waiting some other resource held
      	by the TX (Eg: waiting for an ITL entry)
  
        TM	Table Locks		Object id of table. 	Always 0.
  
        TS	Temp Segment		TS#			Relative DBA
  
        ST	Space Transaction	Only ONE enqueue.
        UL   	User Locks
  
  
  
      ---------------------------------------------------------------------------
      Lock Conversion Summary
      ---------------------------------------------------------------------------
  
        LMODE  Description    Name   NULL    SS      SX      S       SSX     X
        ~~~~~  ~~~~~~~~~~~    ~~~~~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         0,1   No Lock 	     NULL  YES     YES     YES     YES     YES     YES
         2     Row-Share 	       SS  YES     YES     YES     YES     YES     no
         3     Row-Exclusive     SX  YES     YES     YES     no      no      no
         4     Share 	        S  YES     YES     no      YES     no      no
         5     Share Row-Excl   SSX  YES     YES     no      no      no      no
         6     Exclusive 	        X  YES     no      no      no      no      no
      ---------------------------------------------------------------------------
                                       DML Table Lock Mode
                                       Yes     Yes     No      No      ROW-LOCKING
       Operation       Table Access    No      Yes     No      Yes     SERIALIZABLE
       -------------- --------------- ------- ------- ------- -------
       Select            Read            NULL    S       NULL    S
       Select For Update Row-Read        SS      S       SS      S
       Insert            Row-Write       SX      SX      SSX     SSX
       Update            Read-Row-Write  SX      SSX     SSX     SSX
       Delete            Read-Row-Write  SX      SSX     SSX     SSX
       Lock For Update   Row-Read        SS      S       SS      S
  
       Lock Share                        S       S       S       S
       Lock Exclusive                    X       X       X       X
  
       Lock Row Share                    SS      SS      SS      SS
       Lock Row Exclusive                SX      SX      SX      SX
       Lock Share Row    Exclusive       SSX     SSX     SSX     SSX
  
       Alter             Write           X       X       X       X
       Drop              Write           X       X       X       X
       Create Index                      S       S       S       S
       Drop Index        Write           X       X       X       X
       --------------------------------------------------------------- Known Issues:  Known Bugs