oracle锁的类型（9I）

Common Lock Types

常见的几种锁的类型 

Several common locks are described in this section.

TX: Row Transaction Lock（行锁）

• This lock is required in exclusive mode (mode 6) to change data.
• One lock is acquired for each active transaction. It is released when the transaction ends due to a commit or rollback.
• If a block containing the row(s) to be changed does not have any ITL (interested transaction list) entries left, then the session requests the lock in shared mode (mode 4). It is released when the session gets an ITL entry for the block.
• If any of the rows to be changed are locked by another session, then locking session's transaction lock is requested in exclusive mode. When the locking transaction ends, this request ends, and the rows are covered under the requesting session's existing TX lock.
• The lock points to the rollback segment and transaction table entries for the transaction.

Do the following to avoid contention on this enqueue:

• To avoid contention on TX-6 enqueues, review the application.
• To avoid contention on TX-4 enqueues, consider increasing INITRANS for the object.

TM: DML Lock（表级锁）

• This lock is required in exclusive mode for executing any DDL statements on a database object; for example, lock table in exclusive mode, alter table, drop table.
• This lock is also acquired in shared mode when executing DML statements such as INSERT, UPDATE, or DELETE. This prevents other sessions from executing a DDL statement on the same object concurrently.
• For every object whose data is being changed, a TM lock is required.
• The lock points to the object.

To avoid contention on TM enqueues, consider disabling the table lock for the object. Disabling the table lock prevents any DDL from executing on the object.

ST - Space Transaction Lock

• There is only one lock for each database (not instance).
• This lock is required in exclusive mode for any space management activity (creation or dropping any extents) except with locally managed tablespaces.
• Object creation, dropping, extension, and truncation all serialize on this lock.
• Most common causes for contention on this lock are sorting to disk (not using true temporary tablespaces) or rollback segment extension and shrinking.

Do the following to avoid contention on this enqueue:

• Use true temporary tablespaces, utilizing tempfiles. Temporary segments are not created and dropped after every sort to disk.
• Use locally managed tablespaces
• Size rollback segments to avoid dynamic extension and shrinking, or use automatic undo management.
• Avoid application practices that create and drop database objects.

UL - User Defined Locks

Users can define their own locks.

See Also: Oracle9i Database Concepts for more information on locks

Common Modes for Request/Lmode

• 0: None
• 2: Row Share: used for shared DML locks（SS锁或RS锁，也称共享锁，select for update,不在同行兼容，如果同行部兼容）
• 4: Share: used for shared TX when waiting for ITL entry（共享锁，不允许更改行）
• 6: Exclusive used for row level, DML locks（事务排他锁）

Any row in V$LOCK either has LMODE=0 (indicating it is a request) or REQUEST=0 (indicating it is a held lock).

Resource Identifier ID1

For DML locks, ID1 is the object_id.

For TX locks, ID1 points to the rollback segment and transaction table entry.

Join Columns for V$LOCK

Table 24-8 lists the join columns for V$LOCK.

Table 24-8 Join Columns for V$LOCK

Column	View	Joined Column(s)
SID	V$SESSION	SID
ID1, ID2, TYPE	V$LOCK	ID1, ID2, TYPE
ID1	DBA_OBJECTS	OBJECT_ID
TRUNCID1/65536)	V$ROLLNAME	USN

1. This is used to find the session holding the lock, if a session is waiting for a lock.
2. This can be used to find the locked object for DML locks (type = `TM').
3. This can be used to find the rollback segment in use for row transaction locks (TYPE = `TX'). However, a less cryptic join might be through V$TRANSACTION.

Example 24-11 Finding the Sessions Holding the Lock

Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).

Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.

SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
 WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
 ORDER BY id1,request

SID          ID1        ID2      LMODE    REQUEST TY
------ ---------- ---------- ---------- ---------- --
1237       196705     200493          6          0 TX <- Lock Holder
 1256      196705     200493          0          6 TX <- Lock Waiter
 1176      196705     200493          0          6 TX <- Lock Waiter
938        589854     201352          6          0 TX <- Lock Holder
 1634      589854     201352          0          6 TX <- Lock Waiter

Example 24-12 Finding the Statements being Executed by These Sessions

SELECT sid, sql_hash_value 
FROM V$SESSION 
WHERE SID IN (1237,1256,1176,938,1634);

SID  SQL_HASH_VALUE
-----  --------------
  938      2078523611 <-Holder
 1176      1646972797 <-Waiter
 1237      3735785744 <-Holder
 1256      1141994875 <-Waiter
 1634      2417993520 <-Waiter

Example 24-13 Finding the Text for These SQL Statements

HASH_VALUE SQL_TEXT
---------- ----------------------------------------------------------------
1141994875 SELECT TO_CHAR(CURRENT_MAX_UNIQUE_IDENTIFIER + 1 )   FROM PO_UNI
           QUE_IDENTIFIER_CONTROL  WHERE TABLE_NAME = DECODE(:b1,'RFQ','PO_
           HEADERS_RFQ','QUOTATION','PO_HEADERS_QUOTE','PO_HEADERS') FOR UP
           DATE OF CURRENT_MAX_UNIQUE_IDENTIFIER
1646972797 SELECT TO_CHAR(CURRENT_MAX_UNIQUE_IDENTIFIER + 1 )   FROM PO_UNI
           QUE_IDENTIFIER_CONTROL  WHERE TABLE_NAME = 'PO_HEADERS'  FOR UPD
           ATE OF CURRENT_MAX_UNIQUE_IDENTIFIER
2078523611 select CODE_COMBINATION_ID,  enabled_flag,  nvl(to_char(start_da
           te_active, 'J'), -1),  nvl(to_char(end_date_active, 'J'), -1), S
           EGMENT2||'.'||SEGMENT1||'.'||||SEGMENT6,detail_posting_allowed_f
           lag,summary_flag  from GL_CODE_COMBINATIONS  where CHART_OF_ACCO
           UNTS_ID = 101  and SEGMENT2 in ('000','341','367','388','389','4
           52','476','593','729','N38','N40','Q21','Q31','U21')  order by S
           EGMENT2, SEGMENT1, SEGMENT6
2417993520 select 0 into :b0  from pa_projects where project_id=:b1 for upd
           ate
3735785744 begin :X0 := FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS(:L_ENTITY
           _NAME, :L_PKEY1, :L_PKEY2, :L_PKEY3, :L_PKEY4, :L_PKEY5, :L_FUNC
           TION_NAME, :L_FUNCTION_TYPE); end;

The locked sessions' statements show that the sessions 1176 and 1256 are waiting for a lock on the PO_UNIQUE_IDENTIFIER_CONTROL held by session 1237, while session 1634 is waiting for a lock on PA_PROJECTS held by session 938. Query V$SESSION_WAIT, V$SESSION, and V$SESSION_EVENT to get more details about the sessions and users. For example:

• Who is holding the lock?
• Is the session holding the lock active or idle
• Is the session executing long running queries while holding the lock?