Oracle Deadlocks

Deadlock in oracle

In a database, when a session (e.g. ora) wants a resource held by another session (e.g. data), but that session (data) also wants a resource which is held by the first session (ora). There can be more than 2 sessions involved also but idea will be the same.

Actually, Deadlocks prevent some transactions from continuing to work.

For example:

Suppose, ORA-DATA holds lock A and requests lock B

And SKU holds lock B and requests lock A.

There are two types of deadlocks:

i.e. a potential deadlock or an actual deadlock.

Potential deadlock: It does not occur necessarily in a given run but it can occur in any execution of program which depending on the scheduling of the thread and timing of locks requests by the threads.

Actual deadlock: It is one of deadlock which occurs during the execution of a program. Because of this deadlock processes hang, it may hang either whole processes or one process.

 

Deadlock Avoidance

Deadlock can be reduce or avoid by avoid locking contention. We should also make sure that all programs access the data in orders. Sometimes if database application design is not proper that may also cause this issue, so database design should be proper way.

From Alert Log:

Mon Apr 10 19:40:59 2017

ORA-00060: Deadlock detected. More info in file /opt/SKUdb/9.2.0/admin/SKU/udump/ora-data_ora_172589.trc.

Mon Apr 10 20:07:21 2017

From Trace File:

DEADLOCK DETECTED ( ORA-00060 )

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-008f0003-0008de0d       190     286     X             25     585           X

TX-00fc0012-0008bc4c        25     585     X            190     286           X

session 286: DID 0001-00B4-0000116F     session 585: DID 0001-001A-00000A4E

session 585: DID 0001-001A-00000A4E     session 286: DID 0001-00B4-0000116F

Rows waited on:

Session 585: obj - rowid = 0015E796 - AAnDcRALTAAByX3AAp

  (dictionary objn - 1435542, file - 723, block - 468471, slot - 42)

Session 486: obj - rowid = 0015E78F - AAnDZGAJfAACPikAAz

  (dictionary objn - 1435535, file - 607, block - 587940, slot - 52)

Information on the OTHER waiting sessions:

Session 585:

  pid=11 serial=1943 audsid=1423567819 user: 192/APPS

  O/S info: user: kmisadm, term: , ospid: 253647, machine: ora-data

            program:

  client info: 1920                                                   0

  application name: ora-data.blogspot.com, hash value=1925436782

  action name: Concurrent Request, hash value=1925436782

  Current SQL Statement:

Checking:

To check the deadlock details, Please find the below points:

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

ORA-DATA         OPEN

SQL> set lines 32

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

  id1, id2, lmode, request, type

 FROM V$LOCK

WHERE (id1, id2, type) IN

 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

ORDER BY id1, request;

no rows selected

SQL> /

no rows selected

SQL> /

In above command, there is no row selected, it means there is no locking or deadlock at present. It may clear automatically. So, no need to worry about this.

And if we have still active session or getting output from above query then there is simple work-around or solution as:

Identify the particular user and session details which causing the blocking and then find the details about the session as what session is running or doing then contact to particular user and ask for killing the session if nothing important session is working from below command:

SQL>sqlplus ‘/as sysdba’

SQL>ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’;

SQL>

Then again check the details from above command for locks or waits.

Some more useful link:

BLOCKING SESSION

SESSION DETAILS

JOBS TIPS

CAREER PLANNING TIPS

R12.1 Vs R12.2

Hope above information useful and helpful. Please let us know for any concerns or suggestions either in comment box or contact us @https://ora-data.blogspot.in/

Thanks,