Locking

Causes of Dead Locks:-

The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table:

1) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index.

2) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well.

Problems arising from deletion of a row in a parent table are far more common. If I delete a row in table P, then the child table, C, will become locked – preventing other updates against C from taking place for the duration of my transaction (assuming no one else was modifying C, of course; in which case my delete will wait). This is where the blocking and deadlock issues come in. By locking the entire
table C, I have seriously decreased the concurrency in my database – no one will be able to modify anything in C. In addition, I have increased the probability of a deadlock, since I now ‘own’ lots of data until I commit. The probability that some other session will become blocked on C is now much higher; any session that tries to modify C will get blocked. Therefore, I’ll start seeing lots of sessions that hold
some pre-existing locks getting blocked in the database. If any of these blocked sessions are, in fact, holding a lock that my session needs – we will have a deadlock. The deadlock in this case is caused by my session obtaining many more locks then it ever needed. When someone complains of deadlocks in the database, I have them run a script that finds un-indexed foreign keys and ninety-nine percent of the time we locate an offending table. By simply indexing that foreign key, the deadlocks, and lots of other contention issues, go away. Here is an example of how to automatically find these un-indexed foreign keys:

SQL> column columns format a30 word_wrapped
SQL> column tablename format a15 word_wrapped
SQL> column constraint_name format a15 word_wrapped
SQL> select table_name, constraint_name,
cname1 || nvl2(cname2,’,’||cname2,null) ||
nvl2(cname3,’,’||cname3,null) || nvl2(cname4,’,’||cname4,null) ||
nvl2(cname5,’,’||cname5,null) || nvl2(cname6,’,’||cname6,null) ||
nvl2(cname7,’,’||cname7,null) || nvl2(cname8,’,’||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/
TABLE_NAME CONSTRAINT_NAME COLUMNS
------------------------------ --------------- ----------------------------
C SYS_C004710 Y
This script works on foreign key constraints that have up to 8 columns in them.

When do you not need to index a foreign key? The answer is, in general, when the following conditions are met:
1) You do not delete from the parent table.
2) You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools!
3) You do not join from the parent to the child (like DEPT to EMP)



Types of Lock

The five general classes of locks in Oracle are listed below. The first three are common (used in every Oracle database) and the last two are unique to OPS (Oracle Parallel Server). We will introduce the OPS-specific locks, but will concentrate on the common locks:

a) DML locks – DML stands for Data Manipulation Language, in general SELECT, INSERT,
UPDATE, and DELETE. DML locks will be, for example, locks on a specific row of data, or a lock at the table level, which locks every row in the table.

b) DDL locks – DDL stands for Data Definition Language, in general CREATE, ALTER, and so on. DDL locks protect the definition of the structure of objects.

c) Internal locks and latches – These are the locks Oracle uses to protect its internal data structures. For example, when Oracle parses a query and generates an optimized query plan, it will ‘latch’ the library cache in order to put that plan in there for other sessions to use. A latch is a lightweight low-level serialization device employed by Oracle – similar in function to a lock.

d) Distributed locks – These are used by OPS to ensure that resources on the various nodes remain consistent with respect to each other. Distributed locks are held by a database instance, not by individual transactions.

e) PCM (Parallel Cache Management) Locks – These are locks that protect one or more cached data blocks in the buffer cache across multiple instances.

Types of Locks

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

Lock Modes

0 - none
■ 1 - null (NULL)
■ 2 - row-S (SS)
■ 3 - row-X (SX)
■ 4 - share (S)
■ 5 - S/Row-X (SSX)
■ 6 - exclusive (X)


A) TX – (Transaction) Locks

A TX lock is acquired when a transaction initiates its first change, and is held until the transaction performs a COMMIT or ROLLBACK.

B) TM – (DML Enqueue) Locks

These locks are used to ensure that the structure of a table is not altered while you are modifying its contents. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent another user from executing DROP or ALTER commands on that table. If they attempt to perform DDL on the table while you have a TM lock on it, they will receive the following error message:

drop table dept
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

C) DDL Locks

DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. For example, if I perform the DDL operation ALTERTABLE T, the table T will have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table. DDL locks are held for the duration of the DDL statement, and are released immediately afterwards. This is done, in effect, by always wrapping DDL statements in implicit commits (or a commit/rollback pair). It is for this reason that DDL always commits in Oracle. Every CREATE,ALTER, and so on, statement is really executed as shown in
this pseudo-code:

Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;

So, DDL will always commit, even if it is unsuccessful. DDL starts by committing – be aware of this.

There are three types of DDL locks:
a) Exclusive DDL locks – These prevent other sessions from gaining a DDL lock or TM (DML) lock themselves. This means that you may query a table during a DDL operation but may not modify it in any way.

b) Share DDL locks – This protects the structure of the referenced object against modification by other sessions, but allows modifications to the data.

c) Breakable Parse locks – This allows an object, such as a query plan cached in the shared pool, to register its reliance on some other object. If you perform DDL against that object, Oracle will review the list of objects that have registered their dependence, and invalidate them. Hence, these ‘locks’ are ‘breakable’; they do not prevent the DDL from occurring.

Latches and Internal Locks (Enqueues)

Latches and enqueues are lightweight serialization devices used to coordinate multi-user access to shared data structures, objects and files.
Latches are locks that are held for extremely short periods of time, for example the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool (as described in Chapter 2, Architecture). Latches
are typically requested internally in a ‘willing to wait’ mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an ‘immediate’ mode, meaning that the process will go do something else rather than sit
and wait for the latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the ‘luck of the draw’, if you will. Whichever session asks for a latch right after it was released will get it.
There is no line of latch waiters, just a ‘mob’ of waiters constantly retrying.
Oracle uses atomic instructions like ‘test and set’ for operating on latches. Since the instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets it. Since it is only one instruction, it can be quite fast. Latches are held for short periods of time and provide a
mechanism for clean-up in case a latch holder ‘dies’ abnormally while holding it. This cleaning up process would be performed by PMON.

Enqueues are another, more sophisticated, serialization device, used when updating rows in a database table, fro example. They differ from latches in that they allow the requestor to ‘queue up’ and wait for the resource. With a latch request, the requestor is told right away whether they got the latch or not. With an enqueue, the requestor will be blocked until they actually attain it. As such, they are not as fast as a latch can be, but they do provided functionality over and above that which a latch can offer.

Enqueues may be obtained at various levels, so you can have many ‘share’ locks and locks with various degrees of ‘shareability’.

Manual Locking
Creating you own locks via dbms_lock package


To find the blocking Session:-

SQL>select
(select username from v$session where sid=a.sid) blocker, a.sid,' is blocking ',
(select username from v$session where sid=b.sid) blockee, b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/

BLOCKER SID ‘ISBLOCKING’ BLOCKEE SID
-------- ---------- ------------- -------- ----------
MAN 8 is blocking AHA 9


Ref : Expert Oracle - Thomas kyte