| FAQ about Detecting and Resolving Locking Conflicts [ID 15476.1] |
|
|
| |
Modified 26-JAN-2009 Type FAQ Status PUBLISHED |
|
In this Document
Applies to:
Oracle Server - Enterprise Edition - Version: 7.1.1.0 to 11.1.0.6
Information in this document applies to any platform.
Checked for relevance on 22-01-2009.
Purpose
This document covers frequently asked questions about detecting and resolving locking conflicts.
Questions and Answers
1. How does Oracle handle locking?
Oracle use enqueues as locking mechanism for managing access to shared resources. A shared resource can be a table definition, a transaction or any type of structure that represent something sharable between sessions Each type of actions performed by Oracle sessions on those shared resources will require a certain type of lock or lock mode (e.g. a 'select on a table' action will require that the executing session has a shared lock on the resource 'table definition' of the selected table). When conflicting actions are occuring, Oracle will serialize the processing by putting a number of sessions in waiting mode until the work of the blocking session has been completed.
Each enqueue represent a sharable resource. Sessions are acquiring, converting and releasing locks on resources in function of the work they need to perform. Releasing locks are performed by the sessions when they issue a commit or a DDL statement (i.e. implicit commit), or by SMON if the sessions have been killed. Conversion is the process of changing a lock from the mode we currently hold to a different mode. Acquiring a lock is the process of getting a lock on a resource on which we currently do not have a lock. We are allowed to convert a lock if the mode we require, is a subset of the mode we hold or is compatible with the modes already held by other sessions. Otherwise, we wait on the converters queue of the resource. We are allowed to acquire a lock, if there are no converters or waiters ahead of us and the mode we require is compatible with the modes already held by others. Otherwise, we wait on the waiters queue of the resource. When a session has a lock on a resource, then it stands in the owner queue of the resource. When a lock is released or converted, the converters and waiters are re-checked to see if they can be acquired. The converters are processed first, then the waiters.
Row locking in Oracle is based on the TX enqueues and is known as transactional locking. When two or more sessions are changing data on one row of a table (DML statements on the same record), the first session will lock the row by putting his transaction reference in the block containing the row header. The other sessions will look at this lock information and will wait on the transaction (i.e. the TX enqueue of the blocking session) of the first session before proceeding. When the first session performs a commit, the TX resource will be released and the waiters will start their own locking. The waiting sessions are thus waiting on an exclusive TX resource, but their TM resources they are holding give the objects they are in fact waiting on.
If a lock has not been acquired or converted, a deadlock check is made by the waiting session after a timeout. For example, following situation generates a deadlock: user A gets an S lock on resource 1, then user B gets an S lock on resource 2; later, A request an X lock on resource 2 and waits, then B requests an X lock on resource 1 and waits; now, A is waiting for B to release resource 2, which is waiting for A to release resource 1; A is indirectly waiting for A. It is a deadlock, generating a tracefile in the user_dump_dest and and ORA-60 in the detecting session.
The lock and resource information stands within the SGA to allow PMON to recover in the event of process failure. The PMON is responsible for releasing the locks of the crashed/killed processes.
2. How to find the resource definitions?
Each resource is represented by an enqueue. An enqueue is identified by a unique name, also known as the resource name. The name has the form:
. Type has two characters and represent a resource type (e.g. “TM” for the table definition type). ID1 and ID2 are positive numbers and identify the resource fully (e.g. ID1 is the object_id of the table if the resource type is "TM").
The description of most enqueue/resource types can be found in the appendixes of the Oracle Reference Guide. The most commonly known resource types are the TM, TX, ST and UL resources.
a. The TM resource, known as the DML enqueue, is acquired during the execution of a statement when referencing a table so that the table is not dropped or altered during the execution of it.
b. The TX resource, known as the transaction enqueue, is acquired exclusive when a transaction initiates its first change and is held until the transaction does a COMMIT or ROLLBACK. Row locking is based on TX enqueues. SMON will acquire it when doing recovery of a killed/crashed process.
c. The ST resource is used to serialize space management tasks when a session's job requires extents to be allocated to objects.
d. The UL resource represent the user-defined locks defined by the DBMS_LOCK package.
3. Which lock modes are required for which table action?
The following table describes what lock modes on DML enqueues are actually gotten for which table operations in a standard Oracle installation.
Operation Lock Mode LMODE Lock Description
------------------------- --------- ----- ----------------
Select NULL 1 null
Select for update SS 2 sub share
Insert SX 3 sub exclusive
Update SX 3 sub exclusive
Delete SX 3 sub exclusive
Lock For Update SS 2 sub share
Lock Share S 4 share
Lock Exclusive X 6 exclusive
Lock Row Share SS 2 sub share
Lock Row Exclusive SX 3 sub exclusive
Lock Share Row Exclusive SSX 5 share/sub exclusive
Alter table X 6 exclusive
Drop table X 6 exclusive
Create Index S 4 share
Drop Index X 6 exclusive
Truncate table X 6 exclusive
-----------------------------------------------------------
4. How compatibility of locks work
The compatibility of lock modes are normally represented by following matrix:
NULL SS SX S SSX X
-----------------------------------------------------
NULL YES YES YES YES YES YES
SS YES YES YES YES YES no
SX YES YES YES no no no
S YES YES no YES no no
SSX YES YES no no no no
X YES no no no no no
5. Which views can be used to detect locking problems?
A number of Oracle views permits to detect locking problems.
V$SESSION_WAIT When a session is waiting on a resource, it can be
found waiting on the enqueue wait event,
e.g. SELECT * FROM V$SESSION_WAIT WHERE EVENT = 'enqueue';
- SID identifier of session holding the lock
- P1, P2, P3 determine the resource when event = 'enqueue'
- SECONDS_IN_WAIT gives how long the wait did occurs
V$SESSION session information and row locking information
- SID, SERIAL# identifier of the session
- LOCKWAIT address of the lock waiting, otherwise null
- ROW_WAIT_OBJ# object identified of the object we are waiting on
(object_id of dba_objects)
- ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and
row location within block of the locked row
V$LOCK list of all the locks in the system
- SID identifier of session holding the lock
- TYPE, ID1 and ID2 determine the resource
- LMODE and REQUEST indicate which queue the session is waiting on, as follows:
LMODE > 0, REQUEST = 0 owner
LMODE = 0, REQUEST > 0 acquirer
LMODE > 0, REQUEST > 0 converter
- CTIME time since current mode was converted (see )
- BLOCK are we blocking another lock
BLOCK = 0 non blocking
BLOCK = 1 blocking others
DBA_LOCK or DBA_LOCKS formatted view on V$LOCK (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
- SESSION_ID == SID in V$LOCK
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
- MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
- LAST_CONVERT == CTIME of V$LOCK
- BLOCKING_OTHERS formatted value of BLOCK from V$LOCK
V$TRANSACTION_ENQUEUE subset of V$LOCK for the blocking TX resources only
(same description as for the V$LOCK view)
V$ENQUEUE_LOCK subset of V$LOCK for the system resources only and
blocked TX resources only. (same description as for the V$LOCK view)
DBA_DML_LOCKS subset of the V$LOCK for the DML (TM) locks only
(created via $ORACLE_HOME/rdbms/admin/catblock.sql
- same description as the DBA_LOCK view)
V$LOCKED_OBJECT same info as DBA_DML_LOCKS, but linked with the
rollback and session information
- XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
- OBJECT_ID object being locked
- SESSION_ID session id
- ORACLE_USERNAME oracle user name
- OS_USER_NAME OS user name
- PROCESS OS process id
- LOCKED_MODE lock mode
V$RESOURCE list of all the currently locked resources in the system.
Each row can be associated with one or more rows in V$LOCK
- TYPE, ID1 and ID2 determine the resource
DBA_DDL_LOCKS has a row for each DDL lock that is being held, and
one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS
same description as the DBA_LOCK view
DBA_WAITERS view that retrieve information for each session waiting on a
lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
- WAITING_SESSION waiting session
- HOLDING_SESSION holding session
- LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
- MODE_HELD lock type held
- MODE_REQUESTED lock type requested
DBA_BLOCKERS view that gives the blocking sessions (created via
$ORACLE_HOME/rdbms/admin/catblock.sql)
-HOLDING_SESSION holding session
6. Which tools are there to diagnostic locking issues?
a. UTLLOCKT.SQL and CATBLOCK.SQL
These are very helpful scripts that oracle provides with the other RDBMS installation scripts (CATALOG.SQL, CATPROC.SQL, etc.). For example, $ORACLE_HOME/rdbms/admin directory on unix/nt and ora_rdbms directory on vms. You can find more information in .
n.b. Sometimes, the utllockt script can hang. It is mostly due to a contention on the ST enqueue since a table is created in this script.
b. OEM Top Session
OEM Top Session permits to see all the session information. The sessions that are in status ACTIVE can be waiting on locks. By right clicking on those sessions and choosing the 'detail' screen, you can go to the 'lock' leaflet and find blocking sessions. You can find more information in .
c. Lock Charts in Performance Manager of OEM 2.x
OEM 2.x contains a lock graph facility named the Lock Charts in the Performance Manager that is part of the Oracle Diagnostics Pack of OEM. The Performance Manager requires the Oracle Data Gatherer to run. You should select the database from the OEM Console Navigator window. Then launch Performance Manager from the Applications menu bar. When the application comes up, select the 'Lock Charts'. You can use the Performance Manager to kill the session. You just need to right-mouse click on the session you want to kill. Since the Lock Charts are automatically refreshed, you can watch the charts and instantly see locks being released.
d. Oracle Lock Manager of OEM 1.x
Oracle Lock Manager is an application which is part of Oracle Diagnostic Pack of OEM 1.x. This tool has not been migrated to OEM 1.x (see )
e. Own made scripts
A number of sample scripts exist to retrieve locking information.
- TFTS SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO ()
- TFTS FULLY DECODED LOCKING SCRIPT ()
- SCRIPT: DISPLAY LOCKS AND GIVE SID AND SERIAL # TO KILL ()
- SCRIPT: DISPLAY SQL TEXT FROM LOCKS ()
- SCRIPT: SCRIPT TO DISPLAY USER LOCK INFORMATION ()
- SCRIPT: REPORT SESSIONS WAITING FOR LOCKS ()
f. Monitors in SQL*DBA (up to Oracle 7.2)
The SQL*DBA utility of Oracle 7.2 does contain three interesting screens to resolve locking situations. (see ).
g. Using the logminer (Oracle8i onwards)
All locking statements are logged in the redologs. Analyzing them permits to understand locking situations (see ).
7. How to detect locking situations?
Different tools can be used to detect locking issues. A methodology is explained in by using OEM Top Session.
8. How to resolve locking situations?
Most locking issues are application specifics. To resolve locking contention, one needs to free the resource by:
a. Asking the HOLDER to commit or rollback,
b. Killing the session which holds the lock,
e.g. ALTER SESSION KILL SESSION sid, serial#;
c. If the database is running on unix or vms, it is possible to kill the unix/vms shadow process directly. It is not recommended. When killing
the unix/vms shadow process, please be careful of shared servers in a multi-threaded environment.
d. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.
9. How to resolve deadlock situations?
Each deadlock situation will generate a deadlock report in the user_dump_dest directory. It is possible to analyze it with . Common deadlock causes can be found in .
10. Unusual locking problems
Some common locking scenarios are explained in . Otherwise, you can fall on some specific locking fenomens as explained below:
a. When your application has referential integrity and attempts to modify the child/parent table, Oracle will get additional locking on the parent/child table when there is NO index on the foreign key. To bypass this problem, the most efficient way is to create indexes for all foreign key defined in the database. The script 'TFTS CHECK FOR FOREIGN KEY LOCKING' () permits to find the problematic foreign keys. See also .
When indexes are added on child table foreign keys columns, Oracle only require normal 'mode 3 Row-X (SX)' locks on the modified table (parent or child) in Oracle 8.1.7 and 9.0.1. In Oracle9.2 onwards, we requires 'mode 2 Row-S (SS)' locks on the linked table (child table when modifying the parent table, or parent table when modifying the child table). All those DML locks can be disabled via 'ALTER TABLE TABLE_NAME DISABLE TABLE LOCK' without inhibiting any DML activity on both tables. Row level transactional locking can't be disabled. (see ).
When indexes are not present on child table foreign keys columns, Oracle requires, on top of the previous locking situation:
a) in 8.1.7, 'mode 4 Share' locks on the child table when updating/deleting from the parent table. The lock mode even becomes a 'mode 5 S/Row-X (SSX)' lock when deleting from the parent table with a 'delete cascade' foreign key constraint.Those locks can't be disabled (ORA-00069) and are held during the full transaction time.
b) in 9.0.1, Oracle only need those additional locks during the execution time of the UPDATE or DELETE. Those locks are downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. It is thus an improvement compared to Oracle 8.1.7.
c) in 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no longer required except when deleting from a parent table with a 'delete cascade' constraint.
So, it is recommended to have indexes on the foreign key columns of the child tables in order to avoid this additional locking activity, even if the negative effects became less pronounced with Oracle versions.
b. When your application is using DBMS_PIPE extensively, your session can wait for CI locks. You should increase your shared pool.
c. When statements like 'CREATE INDEX' and 'ALTER INDEX REBUILD' are issued, Oracle behave differently in Oracle7 compared to Oracle8i. To understand the benefit of the ONLINE option, you can find more information in .
d. When a table's INITRANS is set too low , the block is full with data, and there are many concurrent DML's occurring on rows within the block, one may see a Share Lock being requested when doing a DML. To my knowledge, this is only time we grab the SHARED lock. Instead of waiting for a lock, this process is waiting for some extra space or a release of an INITRANS within the transaction layer of the block. See TX Transaction locks - Example wait scenarios
e. There are other systemwide locks that can be held at any given time. See Tracing sessions: waiting on an enqueue
f. What about distributed transactions. They use locks too. See Detecting and Resolving Distributed Locking Conflicts
11. How to use dbms_lock?
PACKAGE DBMS_LOCK Specification
References
- Script: To display Locks and give the SID and Serial# of the Session to Kill
- Script: Display SQL text from Locked Transactions
- SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO
- Script: Display User Lock Information
- Script: Report sessions waiting for Locks
- Detecting and Resolving Locking Conflicts using TopSessions
- Resolving locking issues using utllockt.sql script
- How to use the SQL*DBA Monitors screen to analyze locking problems
- Analysing locking issues with LOGMINER
- REFERENTIAL INTEGRITY AND LOCKING
- PACKAGE DBMS_LOCK Specification
- What happened to Lock Manager in OEM 2.0.4
