Tuesday, September 28, 2010

Oracle Deadlocks

Recently , i had a j2ee application that suddenly caused deadlocks ORA-00060 on a test environment. After that i had to refresh my memory about deadlocks.

In general 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.

You will find an entry in the alert.log pointing to the trace file that has all the info regarding the deadlock , the deadlock graph,, the sessions involved , the sql statements , the rwoid of the objects waited and other info.

The most common lock types seen in deadlock graphs are TX and TM locks.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 usually caused by waits

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

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.

ID1 of a TM lock indicates which object is being locked.
The TM lock id in the form TM-AAAAAAAA-BBBBBBBB by converting AAAAAAAA from hexadecimal to a decimal number DDDD we have the object_id. Then locate the object using SELECT * FROM dba_objects WHERE object_id= DDDD;

We also have deadlocks that concerns library cache locks with error ora-04020. The first thing is to check if they are invalid objects used, and try to recompile/validate them.
Secondly, check if DDL commands are executed by different sessions at the same time, and serialize this processing.

Great info in the web


Metalink Note IDs : 62365.1 , 62354.1, 33453.1, 166924.1

No comments:

Post a Comment