数据库alert.log中有ora-60提示，trace 文件提示delete一张表的时候遭遇到了死锁。该表存在主外键约束，在子表外键加索引后，暂时未观察到死锁。
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, ID1, ID2>. 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.
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 Note 223146.1 )
- 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
10. Unusual locking problems
Some common locking scenarios are explained in Note 62354.1 . 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' (Note 1019527.6 ) permits to find the problematic foreign keys. See also Note 33453.1 .
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 Note 223303.1 ).
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 Note 70120.1 .
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 Note 62354.1 TX Transaction locks - Example wait scenarios
e. There are other systemwide locks that can be held at any given time. See Note 102925.1 Tracing sessions: waiting on an enqueue
f. What about distributed transactions. They use locks too. See Note 118219.1 Detecting and Resolving Distributed Locking Conflicts
| ORA-60 / Deadlocks Most Common Causes [ID 164661.1] |
Deadlocks
---------
Deadlocks normally occur when two or more sessions are both holding
and requesting the same set of resources, this bulletin is intended to
discuss two of the most common causes for deadlocks; row-level locks
and block-level locks.
Row Level Locks
Very easy to produce and detect, suppose that you have 2 sessions
accessing the same table Dept:
session 9 : Update dept set Dname='SUPPORT' where deptno=10;
session 10 : Update dept set dname='CONSULTING' where deptno=20;
Session 10 : Update dept set Loc='ORLANDO' where deptno=10;
session 10 will hang waiting on deptno=10 row to be released by session 1
Session 9: update dept set loc='QQQ' where deptno=20;
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
So the last update of session 1 caused a deadlock and a trace file was
generated in the user_dump_dest:
DEADLOCK DETECTED
Current SQL statement for this session:
update dept set loc='QQQ' where deptno=20
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0003000c-00000708 10 9 X 11 10 X
TX-00020007-000006df 11 10 X 10 9 X
session 9: DID 0001-000A-00000004 session 10: DID 0001-000B-00000002
session 10: DID 0001-000B-00000002 session 9: DID 0001-000A-00000004
Rows waited on:
Session 10: obj - rowid = 000051F1 - AAAFHxAAFAAAAAwAAA
Session 9: obj - rowid = 000051F1 - AAAFHxAAFAAAAAwAAB
================================================================================
The above graph shows that the two sessions 9 & 10 are both holding and requesting
the same set of resources which are the two rows of Dept table.
If the deadlock is due to row-level locks being obtained in different
order then this section of the trace file indicates the exact rows that
each session is waiting to lock for themselves.
If you check v$lock you will find the following locks:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------- -- ------- ------- ------- ------- ------- -------
04BD8DE8 04BD8EB8 9 TX 196620 1800 6 0 20637 1
03052480 03052494 9 TM 20977 0 3 0 20637 0
04BD8DE8 04BD8EB8 10 TX 131079 1759 6 0 20594 0
030524F4 03052508 10 TM 20977 0 3 0 20594 0
02F4CB00 02F4CB10 10 TX 196620 1800 0 6 20567 0
The two TM table locks above are holding the same table DEPT, these locks are
always aquired even if there is no integrity constraints on table Dept at all,
the following query shows how to get the object name :
SQL> select object_name from dba_objects
2 where object_id=20977;
OBJECT_NAME
--------------------------------------------------------------------------------
DEPT
To avoid the above locking scenario the application developers has to maintain
certain order in updating a specific table or set of tables.
Block Level Locks
Everybody knows that Oracle was the first database to implement row-level locking
instead of page locking or block locking, but in real life we still encounter
page locking in very rare cases.
Application logic is not the only cause for Deadlocks ,Database Design is also
responsible for that sometimes, it might be related to the way transaction layer
storage parameters were set for the database objects.
Any database block has a transaction layer and a data layer, a good design will
take into consideration both the expected number of records to be accommodated
in one block and the maximum number of concurrent transactions accessing this block.
If the data portion was increased by accommodating more records then this will
decrease the possibility of having the required number of concurrent transactions
that might access a certain block and some of the transactions have to wait until
others are completed , in this case a transaction layer lock will occur and ora-60
is likely to be reported.
Two parameters are responsible about that , INITRANS which is the initial number of
concurrent transactions that access one block and MAXTRANS which is the maximum
number of concurrent transactions that may access the same block. For example,
if a table is very large and only a small number of users simultaneoulsy access
the table, the chances of multiple concurrent transactions requiring access
to the same data block is low.
Therefore, INITRANS can be set low, especially
if space is an issue with the database.
Alternatively, assume that a small table is usually accessed by many users at the
same time.
In this case, you might consider preallocating transaction entry
space by using a high INITRANS and allowing a higher MAXTRANS so that no users
have to wait to access certain hot blocks.
Indexes also should be well designed since it might lead into ora-60 problems
specially when doing concurent deletes.
To produce a Transaction layer lock you can follow the following steps:
create table dept10 (Deptno
number(4),dname varchar2(10),Loc varchar2(10))
initrans 1 maxtrans 1;
inser into dept10 select * from dept;
commit;
Session 10 : Update emp set comm=9999;
session 9
: update dept10 set loc='Orlando' where deptno=10;
session 10 : update dept10 set loc='Orlando' where deptno=20;
-- hangs
session 9
: delete emp;
-- hangs
and session 10 will report ora-60
session 10 above hangs though it is updating a different row, the
Transaction Layer contention caused a block-level lock instead of row-level
lock in this example and led to a Deadlock situation.
select * from v$lock;
ADDR
KADDR
SID TY
ID1
ID2
LMODE REQUEST
CTIME
BLOCK
-------- -------- ------- -- ------- ------- ------- ------- ------- -------
04BD8DE8 04BD8EB8
9 TX
262152
1831
6
0
307
0
030524F4 03052508
9 TM
20983
0
3
0
307
0
03052398 030523AC
9 TM
20976
0
3
0
271
0
02F4CB00 02F4CB10
9 TX
65536
1937
0
6
271
0
04BD8DE8 04BD8EB8
10 TX
65536
1937
6
0
341
1
03052480 03052494
10 TM
20976
0
3
0
341
0
where Dept10
object_id =20983 and Emp object_id=20976
Note that the Deadlock graph in the generated trace file is similar to
the graph shown eralier in this bulletin.
.
TX Transaction locks - Example wait scenarios [ID 62354.1]
Introduction
~~~~~~~~~~~~
This short article gives examples of TX locks and the waits which can
occur in various circumstances. Often such waits will go unnoticed unless
they are of a long duration or when they trigger a deadlock scenario (which
raises an ORA-60 error).
The examples here demonstrate fundamental locking scenarios which should
be understood by application developers and DBA's alike.
The examples require select privilege on the V$ views.
Useful SQL statements
~~~~~~~~~~~~~~~~~~~~~
If you encounter a lock related hang scenario the following SQL statements
can be used to help isolate the waiters and blockers:
Show all sessions waiting for any lock:
select event,p1,p2,p3 from v$session_wait
where wait_time=0 and event='enqueue';
Show sessions waiting for a TX lock:
select * from v$lock where type='TX' and request>0;
Show sessions holding a TX lock:
select * from v$lock where type='TX' and lmode>0;
What is a TX lock ?
~~~~~~~~~~~~~~~~~~~
A TX lock is acquired when a transaction initiates its first change and is
held until the transaction does a COMMIT or ROLLBACK. It is used mainly as
a queuing mechanism so that other sessions can wait for the transaction to
complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction
ID of the active transaction.
Example Tables
~~~~~~~~~~~~~~
The lock waits which can occur are demonstrated using the following
tables. Connect as SCOTT/TIGER or some dummy user to set up the test
environment using the following SQL:
DROP TABLE tx_eg;
CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) )
INITRANS 1 MAXTRANS 1;
INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
COMMIT;
In the examples below three sessions are required:
Ses#1
indicates the TX_EG table owners first session
Ses#2
indicates the TX_EG table owners second session
DBA
indicates a SYSDBA user with access to <View:V$LOCK>
The examples covered below include:
Waits due to Row being locked by an active Transaction
Waits due to Unique or Primary Key Constraint enforcement
Waits due to Insufficient 'ITL' slots in the Block
Waits due to rows being covered by the same BITMAP index fragment
Waits due to Row being locked by an active Transaction
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When a session updates a row in a table the row is locked by the sessions
transaction. Other users may SELECT that row and will see row as it was
BEFORE the UPDATE occurred. If another session wishes to UPDATE the same
row it has to wait for the first session to commit or rollback. The
second session waits for the first sessions TX lock in EXCLUSIVE mode.
Eg:
Ses#1:
update tx_eg set txt='Garbage' where num=1;
Ses#2:
update tx_eg set txt='Garbage' where num=1;
DBA:
select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';
SID
TY ID1
ID2
LMODE
REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX
131075
597
6
0
10 TX
131075
597
0
6
> This shows SID 10 is waiting for the TX lock held by SID 8 and it
> wants the lock in exclusive mode (as REQUEST=6).
The select below is included to demonstrate that a session waiting
on a lock will show as waiting on an 'enqueue' in V$SESSION_WAIT
and that the values of P1RAW, P2 and P3 indicate the actual lock
being waited for . When using Parallel Server the EVENT will be
'DFS enqueue lock acquisition' rather than 'enqueue'.
This select will be omitted from the following examples.
DBA:
select sid,p1raw, p2, p3
from v$session_wait
where wait_time=0 and event='enqueue';
SID
P1RAW
P2
P3
---------- -------- ---------- ----------
10 54580006
131075
597
>
~~~~
~~
~~~~~~
~~~
>
type|mode
id1
id2
>
T X
6
131075
597
这里是如何确定 type 和 mode 的 ?
The next select shows the object_id and the exact row that the
session is waiting for. This information is only valid in V$SESSION
when a session is waiting due to a row level lock. The statement
is only valid in Oracle 7.3 onwards. As SID 10 is the waiter above
then this is the session to look at in V$SESSION:
DBA:
select ROW_WAIT_OBJ#,
ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from v$session
where sid=10;
ROW_WAIT_O ROW_WAIT_F ROW_WAIT_B ROW_WAIT_R
---------- ---------- ---------- ----------
3058
4
2683
0
> The waiter is waiting for the TX lock in order to lock row 0
> in file 4, block 2683 of object 3058.
Ses#1:
rollback;
Ses#2:
rollback;
Waits due to Unique or Primary Key Constraint enforcement
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If a table has a primary key constraint, a unique constraint
or a unique index then the uniqueness of the column/s referenced by
the constraint is enforced by a unique index. If two sessions try to
insert the same key value the second session has to wait to see if an
ORA-0001 should be raised or not.
Eg:
Ses#1:
ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );
Ses#1:
insert into tx_eg values (10,'New','MALE');
Ses#2:
insert into tx_eg values (10,'OtherNew',null);
DBA:
select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';
SID
TY ID1
ID2
LMODE
REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX
196625
39
6
0
10 TX
262155
65
6
0
10 TX
196625
39
0
4
This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4). SID 10 holds a TX lock
for its own transaction.
Ses#1:
commit;
Ses#2:
ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
Ses#2:
rollback;
Waits due to Insufficient 'ITL' slots in a Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Oracle keeps note of which rows are locked by which transaction in an area
at the top of each data block known as the 'interested transaction list'.
The number of ITL slots in any block in an object is controlled by
the INITRANS and MAXTRANS attributes. INITRANS is the number of slots
initially created in a block when it is first used, while MAXTRANS places
an upper bound on the number of entries allowed. Each transaction which
wants to modify a block requires a slot in this 'ITL' list in the block.
MAXTRANS places an upper bound on the number of concurrent transactions
which can be active at any single point in time within a block.
INITRANS provides a minimum guaranteed 'per-block' concurrency.
If more than INITRANS but less than MAXTRANS transactions want to be
active concurrently within the same block then the ITL list will be extended
BUT ONLY IF THERE IS SPACE AVAILABLE TO DO SO WITHIN THE BLOCK.
If there is no free 'ITL' then the requesting session will wait on one
of the active transaction locks in mode 4.
Eg:
Ses#1:
update tx_eg set txt='Garbage' where num=1;
Ses#2:
update tx_eg set txt='Different' where num=2;
DBA:
select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';
SID
TY ID1
ID2
LMODE
REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX
327688
48
6
0
10 TX
327688
48
0
4
This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4).
Ses#1:
commit;
Ses#2:
commit;
Ses#1:
ALTER TABLE tx_eg MAXTRANS 2;
Ses#1:
update tx_eg set txt='First' where num=1;
Ses#2:
update tx_eg set txt='Second' where num=2;
Both rows update as there is space to grow the ITL list to
accommodate both transactions.
Ses#1:
commit;
Ses#2:
commit;
Also from 9.2 you can check the ITL Waits in v$segment_statistics
with a query like :
SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
FROM v$segment_statistics t
WHERE t.STATISTIC_NAME = 'ITL waits'
AND t.VALUE > 0;
If need be, increase INITTRANS and MAXTRANS.
Waits due to rows being covered by the same BITMAP index fragment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Bitmap indexes index key values and a range of ROWIDs. Each 'entry'
in a bitmap index can cover many rows in the actual table.
If 2 sessions wish to update rows covered by the same bitmap index
fragment then the second session waits for the first transaction to
either COMMIT or ROLLBACK by waiting for the TX lock in mode 4.
Eg:
Ses#1:
CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );
Ses#1:
update tx_eg set sex='FEMALE' where num=3;
Ses#2:
update tx_eg set sex='FEMALE' where num=4;
DBA:
select SID,TYPE,ID1,ID2,LMODE,REQUEST
from v$lock where type='TX';
SID
TY ID1
ID2
LMODE
REQUEST
---------- -- ---------- ---------- ---------- ----------
8 TX
262151
62
6
0
10 TX
327680
60
6
0
10 TX
262151
62
0
4
This shows SID 10 is waiting for the TX lock held by SID 8 and it
wants the lock in share mode (as REQUEST=4).
Ses#1:
commit;
Ses#2:
commit;
Other Scenarios
~~~~~~~~~~~~~~~
There are other wait scenarios which can result in a SHARE mode wait for a TX
lock but these are rare compared to the examples given above.
Eg: If a session wants to read a row locked by a transaction in a PREPARED
state then it will wait on the relevant TX lock in SHARE mode (REQUEST=4).
As a PREPARED transaction should COMMIT , ROLLBACK or go to an in-doubt
state very soon after the prepare this is not generally noticeable..
| SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES [ID 1019527.6] | ||
|
| ||
Applies to:
Oracle Server - Enterprise Edition - Version: 7.0.16.0 to 10.2.0.5 - Release: 7.0 to 10.2
Information in this document applies to any platform.
Purpose
SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES
Software Requirements/Prerequisites
Rdbms:07.0.X - 11.XX
Configuring the Script
Access Privileges:
If run as owner of objects no special priveleges required
Usage:
sqlplus / @[SCRIPTFILE]
Running the Script
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.
Description
This script checks the current users Foreign Keys to make sure of the
following:
1) All the FK columns have indexes to prevent a possible locking
2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
problem the columns MUST be indexed in the same order as the FK is
defined.
3) If the script finds a mismatch, the script reports the correct
order of columns that need to be added to prevent the locking
problem.
IMPORTANT, PLEASE NOTE
This locking problem, due to a FK column not being indexed, is discussed
in the 8x/9x Concepts Manual under Data Integrity chapter.
Starting with Oracle9i, Release 1 (9.0.1), Oracle no longer requires
a share lock on unindexed foreign keys when doing an update or delete on
the primary key. It still obtains the table-level share lock, but then
releases it immediately after obtaining it. If multiple primary keys are
update or deleted, the lock is obtained and released once for each row.
Caution
This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.
Script
=======
Script:
=======
----------- cut ---------------------- cut -------------- cut --------------
SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM
REM
REM -------------------------------------------------------------------------
REM Main text of script follows:
drop table ck_log;
create table ck_log (
LineNum number,
LineMsg varchar2(2000));
declare
t_CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%type;
t_CONSTRAINT_NAME USER_CONSTRAINTS.CONSTRAINT_NAME%type;
t_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
t_R_CONSTRAINT_NAME USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;
tt_CONSTRAINT_NAME USER_CONS_COLUMNS.CONSTRAINT_NAME%type;
tt_TABLE_NAME USER_CONS_COLUMNS.TABLE_NAME%type;
tt_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
tt_POSITION USER_CONS_COLUMNS.POSITION%type;
tt_Dummy number;
tt_dummyChar varchar2(2000);
l_Cons_Found_Flag VarChar2(1);
Err_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
Err_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
Err_POSITION USER_CONS_COLUMNS.POSITION%type;
tLineNum number;
cursor UserTabs is
select table_name
from user_tables
order by table_name;
cursor TableCons is
select CONSTRAINT_TYPE,
CONSTRAINT_NAME,
R_CONSTRAINT_NAME
from user_constraints
where OWNER = USER
and table_name = t_Table_Name
and CONSTRAINT_TYPE = 'R'
order by TABLE_NAME, CONSTRAINT_NAME;
cursor ConColumns is
select CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;
cursor IndexColumns is
select TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;
DebugLevel number := 99; -- >>> 99 = dump all info`
DebugFlag varchar(1) := 'N'; -- Turn Debugging on
t_Error_Found varchar(1);
begin
tLineNum := 1000;
open UserTabs;
LOOP
Fetch UserTabs into t_TABLE_NAME;
t_Error_Found := 'N';
exit when UserTabs%NOTFOUND;
-- Log current table
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Checking Table '||t_Table_Name);
l_Cons_Found_Flag := 'N';
open TableCons;
LOOP
FETCH TableCons INTO t_CONSTRAINT_TYPE,
t_CONSTRAINT_NAME,
t_R_CONSTRAINT_NAME;
exit when TableCons%NOTFOUND;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME);
commit;
end;
end if;
open ConColumns;
LOOP
FETCH ConColumns INTO
tt_CONSTRAINT_NAME,
tt_TABLE_NAME,
tt_COLUMN_NAME,
tt_POSITION;
exit when ConColumns%NOTFOUND;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found TABLE_NAME = '|| tt_TABLE_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found COLUMN_NAME = '|| tt_COLUMN_NAME);
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found POSITION = '|| tt_POSITION);
commit;
end;
end if;
begin
select 1 into tt_Dummy
from user_ind_columns
where TABLE_NAME = tt_TABLE_NAME
and COLUMN_NAME = tt_COLUMN_NAME
and COLUMN_POSITION = tt_POSITION;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Row Has matching Index' );
end;
end if;
exception
when Too_Many_Rows then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Row Has matching Index' );
end;
end if;
when no_data_found then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'NO MATCH FOUND' );
commit;
end;
end if;
t_Error_Found := 'Y';
select distinct TABLE_NAME
into tt_dummyChar
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Changing data in table '||tt_dummyChar
||' will lock table ' ||tt_TABLE_NAME);
commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Create an index on table '||tt_TABLE_NAME
||' with the following columns to remove lock problem');
open IndexColumns ;
loop
Fetch IndexColumns into Err_TABLE_NAME,
Err_COLUMN_NAME,
Err_POSITION;
exit when IndexColumns%NotFound;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')');
end loop;
close IndexColumns;
end;
end loop;
commit;
close ConColumns;
end loop;
if ( t_Error_Found = 'N' )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'No foreign key errors found');
end;
end if;
commit;
close TableCons;
end loop;
commit;
end;
/
select LineMsg
from ck_log
where LineMsg NOT LIKE 'Checking%' AND
LineMsg NOT LIKE 'No foreign key%'
order by LineNum
/
----------- cut ---------------------- cut -------------- cut --------------
Script Output
=========
Examples:
=========
LINEMSG
--------------------------------------------------------------------------
Changing data in table EMP will lock table DEPT
Create an index on the following columns to remove lock
problem
Column = DEPTNO (1)
Changing data in table EMP will lock table EMP
Create an index on the following columns to remove lock
problem
Column = MGR (1)
Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem
Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)
Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem
Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)
Changing data in table CUSTOMER will lock table ORD
Create an index on the following columns to remove lock
problem
Column = CUSTID (1)
| What is the meaning of the column CTIME in V$LOCK ? [ID 223146.1] |
PURPOSE
-------
The purpose of this Note is to explain the meaning of the column CTIME in
V$LOCK.
SCOPE & APPLICATION
-------------------
DBAs, Support Analyst etc.
What is the meaning of the column CTIME in V$LOCK ?
-----------------------------------------------------------------------
The column CTIME in V$LOCK indicate that the time since the current lock mode
has been acquired/converted. The time is shown in units of seconds. This value
will be constantly increasing. This column gets reset when the lock is converted in
another mode. The column is continuously updated by the time last recorded by
log writer and hence the time shown by this column may not be very accurate. 怪不得每次最少加 3 秒
The following example will explain this in detail.
14:28:48 SQL> lock table emp in share mode;
Table(s) Locked.
14:28:54 SQL>
select type,id1,id2,lmode,ctime from v$lock
14:28:56
2
where type='TM';
TY
ID1
ID2
LMODE
CTIME
-- ---------- ---------- ---------- ----------
TM
3174
0
4
3
14:28:57 SQL> /
TY
ID1
ID2
LMODE
CTIME
-- ---------- ---------- ---------- ----------
TM
3174
0
4
6
14:29:02 SQL> lock table emp in exclusive mode; <<< Locking the table in
different mode.
Table(s) Locked.
14:29:08 SQL>
select type,id1,id2,lmode,ctime from v$lock
14:29:11
2
where type='TM';
TY
ID1
ID2
LMODE
CTIME
-- ---------- ---------- ---------- ----------
TM
3174
0
6
0 <<< CTIME was reset.
14:29:11 SQL>
/
TY
ID1
ID2
LMODE
CTIME
-- ---------- ---------- ---------- ----------
TM
3174
0
6
3
REFERENTIAL INTEGRITY AND LOCKING ID 33453.1
This bulletin explains what referential integrity means and
how locking takes place with tables joined by the referential integrity
rule. In addition, this bulletin explains how inserting/updating/deleting one
table can cause another table to get locked.
REFERENTIAL INTEGRITY: is a rule defined on a column (or set of columns) in one
table that allows the insert or update of a row only if the value for the column
or set of columns (in the child table) matches the value in a column of a
related table (parent table).
Example 1:
SQL> create table DEPT (deptno number constraint pk_dept primary key,
dname varchar2(10))
SQL> create table EMP (deptno number(2) constraint fk_deptno references
dept(deptno), ename varchar2(20))
In the above example "DEPT" is the parent table having the primary key
constraint 'pk_dept' on the 'deptno' column. Similarly "EMP" is the child table
having the foreign key constraint 'fk_deptno' on the 'deptno' column. However,
this foreign key constraint references the 'deptno' column of the parent table
(DEPT) thus enforcing the referential integrity rule. Therefore you cannot add
an employee into a department number that doesn't exist in the DEPT table.
Example 2:
SQL> insert into DEPT values (1, 'COSTCENTER');
1 row created.
SQL> insert into EMP values (1, 'SCOTT');
1 row created.
SQL> insert into EMP values (2, 'SCOTT');
insert into EMP values (2, 'SCOTT')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not
found
The query that can be issued to find out the primary and foreign key relation
is as follows:
SQL> select a.owner for_owner, a.table_name for_table, a.constraint_name
for_constr, b.owner pri_owner, b.table_name pri_table, b.constraint_name
pri_constr from user_constraints a, user_constraints b
where a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and b.constraint_type = 'P';
FOR_OWNER
FOR_TABLE
------------------------------ ------------------------------
FOR_CONSTR
PRI_OWNER
------------------------------ ------------------------------
PRI_TABLE
PRI_CONSTR
------------------------------ ------------------------------
SCOTT
EMP
FK_DEPTNO
SCOTT
DEPT
PK_DEPT
where USER_CONSTRAINTS
: data dictionary view
CONSTRAINT_TYPE = 'R' : stands for the foreign key constraint
CONSTRAINT_TYPE = 'P' : stands for the primary key constraint
The data dictionary contains the following views of interest with integrity
constraints:
a) ALL_CONSTRAINTS
b) ALL_CONS_CONSTRAINTS
c) CONSTRAINT_COLUMNS
d) CONSTRAINT_DEFS
e) USER_CONSTRAINTS
f) USER_CONS_COLUMNS
g) USER_CROSS_REFS
h) DBA_CONSTRAINTS
i) DBA_CONS_COLUMNS
j) DBA_CROSS_REFS
LOCKING:
Indexes play an important part when dealing with referential
integrity and locking. The existence of an index determines the type of lock
necessary, if any. Below are examples that will describe this locking
phenomenon.
Each example displays output from a Data Dictionary object, V$LOCK. This view
gives information about the different types of locks held within the
In order to fully understand the output of this view, below is a description
of this object.
SQL> desc v$lock;
Name
Null?
Type
------------------------------- -------- ----
ADDR
RAW(4)
KADDR
RAW(4)
SID
NUMBER
TYPE
VARCHAR2(2)
ID1
NUMBER
ID2
NUMBER
LMODE
NUMBER
REQUEST
NUMBER
where
ADDR = address of lock state object
KADDR = address of lock
SID = identifier of process holding the lock
TYPE = resource type
ID1 = resource identifier #1
ID2 = resource identifier #2
LMODE = lock mode held: 1 (null), 2 (row share), 3 (row exclusive),
4 (share), 5 (share row exclusive),
6 (exclusive)
REQUEST = lock mode requested (same values as LMODE)
TYPE
LOCK ID1
LOCK ID2
a) TX(transaction)
Decimal representation of
Decimal rrepresentation
rollback segment number
of "wrap" number (number of
and slot number
times the rollback slot has
been reused)
b) TM(table locks)
Object id of table being
Always 0
modified
c) UL(user supplied
Please refer to Appendix B-81 of the Oracle7 Server
lock)
Administrator's Guide.
Examples:
NOTE: In all the examples given below, the object_id for the DEPT and the EMP
tables are 2989 and 2991 respectively. The ID1 column from the V$LOCK data
dictionary object corresponds to the OBJECT_ID column from the DBA_OBJECTS
view.
SQL> select object_name from sys.dba_objects where object_id = 2989;
OBJECT_NAME
-------------------------------------------------------------------------------
DEPT
SQL> select object_name from sys.dba_objects where object_id = 2991;
OBJECT_NAME
-------------------------------------------------------------------------------
EMP
**** WITHOUT INDEXES
****
1) AN INSERT/DELETE/UPDATE INTO THE CHILD TABLE CAUSES THE PARENT TABLE TO GET
LOCKED. Notice that a share lock (LMODE=4) of the entire parent table is
required until the transaction containing the insert/delete/update statement
for the child table is committed, thus preventing any modifications to the
parent table.
NOTE:
In 7.1.6 and higher, an insert, update, and delete statement on the
child table will not acquire any locks on the parent table anymore, although
insert and update statements will wait for a row-lock on the index of the
parent table to clear.
SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> commit;
SQL> insert into EMP values (1, 'SCOTT');
SQL> select * from v$lock
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
ADDR
KADDR
SID TY
ID1
ID2
LMODE
REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
40078664 40078678
15 TM
2989
0
4
0
4007AD74 4007AE08
15 TX
196667
54
6
0
400786C8 400786DC
15 TM
2991
0
3
0
2) AN INSERT/DELETE/UPDATE ON THE PARENT TABLE CAUSES THE CHILD TABLE TO GET
LOCKED. A share lock (LMODE=4) of the entire child table is required
until the transaction containing the insert/delete/update statement
for the parent table is committed, thus preventing any modifications to the
child table. It even can be a SSX (LMODE=5) lock when deleting from the
parent table with a delete cascade constraint.
NOTE:
In 7.1.6 and higher, INSERT into the parent table do not lock the child
table. In Oracle 9.0.1 or higher, those looks became temporal: they are only needed
during the execution time of the UPDATE/DELETE statements. Those locks are
downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished.
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.
SQL> update dept set deptno = 1;
SQL> select * from v$lock
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
ADDR
KADDR
SID TY
ID1
ID2
LMODE
REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
40078664 40078678
15 TM
2991
0
4
0
4007AD74 4007AE08
15 TX
196667
54
6
0
400786C8 400786DC
15 TM
2989
0
3
0
****
WITH INDEXES
****
1) AN INSERT/DELETE/UPDATE ON THE CHILD TABLE DOES NOT PLACE LOCKS OF ANY KIND
ON THE PARENT TABLE IF THERE IS AN INDEX ON THE FOREIGN KEY OF THE CHILD
TABLE.
Therefore, any type of DML statement can be issued on the parent
table, including inserts, updates, deletes and queries.
NOTE:
In 9.2.0 onwards, Oracle requires 'mode 2 Row-S (SS)' locks on the
parent table (see Note.223303.1
).
SQL> create index ind_emp on emp (deptno, ename);
SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> commit;
SQL> insert into EMP values (1, 'SCOTT');
SQL> select * from v$lock
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
ADDR
KADDR
SID TY
ID1
ID2
LMODE
REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
40078664 40078678
15 TX
196667
54
6
0
4007AD74 4007AE08
15 TM
2991
0
3
0
2) AN INSERT/DELETE/UPDATE ON THE PARENT TABLE WILL ONLY ACQUIRE A ROW LEVEL
LOCK ON THE PARENT TABLE IF THERE IS AN INDEX ON THE FOREIGN KEY OF THE
CHILD TABLE. The child table will have NO locks on it and so any type of
modifications can be made to the child table.
NOTE:
In v7.1.6 and higher, inserts, updates and deletes on the parent table
do not require any locks on the child table, although updates and deletes
will wait for row-level locks to clear on the child table index.
If the
child table specifies ON DELETE CASCADE, waiting and locking rules are the
same as if you deleted from the child table after performing the delete from
the parent. In 9.2.0 onwards, Oracle requires 'mode 2 Row-S (SS)' locks on the
child table (see Note.223303.1
).
SQL> update DEPT set deptno = 1;
SQL> select * from v$lock
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
ADDR
KADDR
SID
TY ID1
ID2
LMODE
REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
40078664 40078678
15 TX
196667
54
6
0
4007AD74 4007AE08
15 TM
2989
0
3
0
What to do with "ORA-60 Deadlock Detected" Errors [ID 62365.1]
Introduction
~~~~~~~~~~~~
This short article describes how to determine the cause of ORA-60
"deadlock detected while waiting for resource" errors.
Note that 99% of the time deadlocks are caused by application or
configuration issues. This article attempts to highlight the most
common deadlock scenarios.
What is Deadlock?
~~~~~~~~~~~~~~~~~
A deadlock occurs when a session (A) wants a resource held by another
session (B) , but that session also wants a resource held by the first
session (A). There can be more than 2 sessions involved but the idea is
the same.
Example of Deadlock
~~~~~~~~~~~~~~~~~~~
To reinforce the description the following simple test demonstrates a
a deadlock scenario. This is on Oracle 8.0.4 so if you are used to Oracle7
the ROWIDs may look a little strange:
Setup:
create table eg_60 ( num number,
txt varchar2(10) );
insert into eg_60 values ( 1, 'First' );
insert into eg_60 values ( 2, 'Second' );
commit;
select rowid, num, txt from eg_60;
ROWID
NUM TXT
------------------ ---------- ----------
AAAAv2AAEAAAAqKAAA
1 First
AAAAv2AAEAAAAqKAAB
2 Second
Ses#1:
update eg_60 set txt='ses1' where num=1;
Ses#2:
update eg_60 set txt='ses2' where num=2;
update eg_60 set txt='ses2' where num=1;
> Ses#2 is now waiting for the TX lock held by Ses#1
Ses#1:
update eg_60 set txt='ses1' where num=2;
> This update would cause Ses#1 to wait on the TX lock
> held by Ses#2, but Ses#2 is already waiting on this session.
> This causes a deadlock scenario so one of the sessions
> signals an ORA-60.
Ses#2:
ORA-60 error
Ses#1:
Still blocked until Ses#2 commits or rolls back as ORA-60
only rolls back the current statement and not the entire
transaction.
Diagnostic information produced by an ORA-60
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-60 error normally writes to the alert log.
And the user that gets the ORA-60 error writes information to their trace file.
The exact format of this varies between Oracle releases. The trace
file will be written to the directory indicated by the USER_DUMP_DEST and sometimes
to the background_dump_dest.
The trace file will contain a deadlock graph and additional information
similar to that shown below. This is the trace output from the above example
which signaled an ORA-60 to Ses#2:
-----------------------------------------------------------------------
DEADLOCK DETECTED
Current SQL statement for this session:
update eg_60 set txt='ses2' where num=1
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)--------
---------Waiter(s)---------
Resource Name
process session holds waits
process session holds waits
TX-00020012-0000025e
12
11
X
11
10
X
TX-00050013-0000003b
11
10
X
12
11
X
session 11: DID 0001-000C-00000001
session 10: DID 0001-000B-00000001
session 10: DID 0001-000B-00000001
session 11: DID 0001-000C-00000001
Rows waited on:
Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAB
Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAA
-----------------------------------------------------------------------
What does the trace information mean ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In this section we explain each part of the above trace.
Note that not all this information is produced in all Oracle releases.
-----------------------------------------------------------------------
DEADLOCK DETECTED
Current SQL statement for this session:
update eg_60 set txt='ses2' where num=1
-----------------------------------------------------------------------
This shows the statement which was executing which received the ORA-60
error. It is this statement which was rolled back.
-----------------------------------------------------------------------
Deadlock graph:
---------Blocker(s)--------
---------Waiter(s)---------
Resource Name
process session holds waits
process session holds waits
TX-00020012-0000025e
12
11
X
11
10
X
TX-00050013-0000003b
11
10
X
12
11
X
-----------------------------------------------------------------------
This shows who was holding each lock, and who was waiting for each lock.
The columns in the graph indicate:
Resource Name
Lock name being held / waited for.
process
V$PROCESS.PID of the Blocking / Waiting session
session
V$SESSION.SID of the Blocking / Waiting session
holds
Mode the lock is held in
waits
Mode the lock is requested in
So in this example:
SID 11
holds TX-00020012-0000025e in X mode
and wants TX-00050013-0000003b in X mode
SID 10
holds TX-00050013-0000003b in X mode
and wants TX-00020012-0000025e in X mode
The important things to note here are the LOCK TYPE, the MODE HELD and
the MODE REQUESTED for each resource as these give a clue as to the
reason for the deadlock.
-----------------------------------------------------------------------
Rows waited on:
Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAB
Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAA
-----------------------------------------------------------------------
If the deadlock is due to row-level locks being obtained in different
orders then this section of the trace file indicates the exact rows that
each session is waiting to lock for themselves. Ie: If the lock requests
are TX mode X waits then the 'Rows waited on' may show useful information.
For any other lock type / mode the 'Rows waited on' is not relevant and
usually shows as "no row".
In the above example:
SID 10
was waiting for ROWID 'AAAAv2AAEAAAAqKAAB' of object 0xBF6
(which is 3062 in decimal)
SID 11
was waiting for ROWID 'AAAAv2AAEAAAAqKAAA' of object 0xBF6
This can be decoded to show the exact row/s.
Eg: SID 10 can be shown to be waiting thus:
SELECT owner, object_name, object_type
FROM dba_objects WHERE object_id = 3062;
Owner
Object_Name
Object_Type
-------
---------------
---------------
SYSTEM
EG_60
TABLE
SELECT * FROM system.eg_60 WHERE ROWID='AAAAv2AAEAAAAqKAAB';
NUM
TXT
---------- ----------
2 Second
Avoiding Deadlock
~~~~~~~~~~~~~~~~~
The above deadlock example occurs because the application which issues
the update statements has no strict ordering of the rows it updates.
Applications can avoid row-level lock deadlocks by enforcing some ordering
of row updates. This is purely an application design issue.
Eg: If the above statements had been forced to update rows in ascending
'num' order then:
Ses#1:
update eg_60 set txt='ses1' where num=1;
Ses#2:
update eg_60 set txt='ses2' where num=1;
> Ses#2 is now waiting for the TX lock held by Ses#1
Ses#1:
update eg_60 set txt='ses1' where num=2;
> Succeeds as no-one is locking this row
commit;
> Ses#2 is released as it is no longer waiting for this TX
Ses#2:
update eg_60 set txt='ses2' where num=2;
commit;
The strict ordering of the updates ensures that a deadly embrace cannot
occur. This is the simplest deadlock scenario to identify and resolve.
Note that the deadlock need not be between rows of the same table - it
could be between rows in different tables. Hence it is important to place
rules on the order in which tables are updated as well as the order of the
rows within each table.
Other deadlock scenarios are discussed below.
Different Lock Types and Modes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The most common lock types seen in deadlock graphs are TX and TM locks.
These may appear held / requested in a number of modes. It is the
lock type and modes which help determine what situation has caused the
deadlock.
Lock
Mode
Type
Requested
Probable Cause
~~~~
~~~~~~~~~
~~~~~~~~~~~~~~
TX
X (mode 6)
Application row level conflict.
Avoid by recoding the application to ensure
rows are always locked in a particular order.
TX
S (mode 4)
There are a number of reasons that a TX lock
may be requested in S mode. See Note:62354.1
for a list of when TX locks are requested in
mode 4.
TM
SSX (mode 5)
This is usually related to the existence of
or
foreign key constraints where the columns
S (mode 4)
are not indexed on the child table.
See Note:33453.1
for how to locate such
constraints. See below for locating
the OBJECT being waited on.
Although other deadlock scenarios can happen the above are the most common.
TM locks - which object ?
~~~~~~~~~~~~~~~~~~~~~~~~~
ID1 of a TM lock indicates which object is being locked.
This makes it
very simple to isolate the object involved in a deadlock when a TM lock
is involved.
1. Given the TM lock id in the form TM-AAAAAAAA-BBBBBBBB
convert AAAAAAAA from hexadecimal to a decimal number
2. Locate the object using DBA_OBJECTS:
SELECT * FROM dba_objects WHERE object_id= NNNN;
This is the object id that the TM lock covers.
Note that with TM locks it is possible that the lock is already held in
some mode in which case the REQUEST is to escalate the lock mode.
Additional Information
~~~~~~~~~~~~~~~~~~~~~~
If you are still having problems identifying the cause of a deadlock
Oracle Support may be able to help. Additional information can be collected
by adding the following to the init.ora parameters:
event="60 trace name errorstack level 3;name systemstate level 266"
Note that this can generate a very large trace file which may get
truncated unless MAX_DUMP_FILE_SIZE is large enough to accommodate the output.
When this is set any session encountering an ORA-60 error will write
information about all processes on the database at the time of the error.
This may help show the cause of the deadlock as it can show information
about both users involved in the deadlock. Oracle Support will need
all the information you have collected in addition to the new trace file
to help identify where in the application you should look for problems.
It may be necessary to run the offending jobs with SQL_TRACE enabled
to show the order in which each session issues its commands in order
to get into a deadlock scenario.
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
