Deadlock Investigation Example

DBINST started to display the following error message:

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/DBINST/udump/DBINST_ora_1705.trc.

When the deadlocks occurred, the application was failing and logging errors too.

Oracle Concept Manual: “A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. … Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. “

The start of the trace files were similar to each other, so it appeared to be the same piece of code causing the problem:

DEADLOCK DETECTED
Current SQL statement for this session:
delete from PORTDETAIL where ID=:1 and version=:2
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
TM-0000e7bc-00000000        28     132    SX   SSX       25     135    SX   SSX
TM-0000e7bc-00000000        25     135    SX   SSX       28     132    SX   SSX
session 132: DID 0001-001C-0000052F     session 135: DID 0001-0019-00000B29
session 135: DID 0001-0019-00000B29     session 132: DID 0001-001C-0000052F
Rows waited on:
Session 135: obj - rowid = 0000E7DC - AAAOfcAAFAAAAjIAAA
(dictionary objn - 59356, file - 5, block - 2248, slot - 0)
Session 132: obj - rowid = 0000E7DC - AAAOfcAAFAAAAjIAAA
(dictionary objn - 59356, file - 5, block - 2248, slot - 0)
Information on the OTHER waiting sessions:
Session 135:
pid=25 serial=1301 audsid=134658 user: 60/EGSCHEMA
O/S info: user: , term: , ospid: 1234, machine: dbserver.local
program:
Current SQL Statement:
delete from PORTDETAIL where ID=:1 and version=:2
End of information on OTHER waiting sessions.

I looked noticed that this was a bit different than other deadlocks I’ve seen; it was dead locking on table locks instead of row locks (transactions). This made me suspect a foreign key wasn’t indexed. (It could be the result of an application doing something unusual, like issuing lock table commands or DDL even).

So in this case we should ignore the “Rows waited on” section, which is useful when we have blocking transactions (TX).

I checked which table was locked:

TM=table lock (DML lock)
0x0000e7bc=59324

select owner, object_name from dba_objects where DATA_OBJECT_ID=59324;
OWNER
------------------------------
OBJECT_NAME
------------------------------
EGSCHEMA
PORTSERVICEORDER

Note that this isn’t the table in the SQL statement for either session: PORTDETAIL.

So, is there a trigger or a foreign key that could cause a lock on PORTSERVICEORDER when a row from PORTDETAIL is deleted?

@dd
EGSCHEMA
PORTDETAIL
TYPE              NAME                           OWNER                          'SOF 'REFERENCED'  NULL
----------------- ------------------------------ ------------------------------ ---- ------------- ------------------------------
INDEX             PD_PSO_IDX                     EGSCHEMA                       HARD ON_OBJECT
INDEX             PK_PORTDETAIL                  EGSCHEMA                       HARD ON_OBJECT
TABLE             PORTITEM                       EGSCHEMA                       HARD FK_REF_OBJECT FK_PORTITEM_PORTDETAIL
TABLE             PORTSERVICEORDER               EGSCHEMA                       HARD FK_REF_OBJECT FK_PSO_APPROVEDPORTDETAILS
TABLE             PORTSERVICEORDER               EGSCHEMA                       HARD FK_REF_OBJECT FK_PSO_GSPPORTDETAILS
VIEW              VIEW_GC_LC_FOR_COMPLETED_PORTS EGSCHEMA                       SOFT REFERENCED

Yes! Two foreign keys.

Are these foreign keys indexed?

@$DBA_SQLPATH/apt/missing_fk_indexes.sql
TABLE_NAME                                       COLUMN_NAME
------------------------------------------------ ------------------------------
EGSCHEMA.PORTSERVICEORDER                        APPROVEDPORTDETAILS
EGSCHEMA.PORTSERVICEORDER                        GSPPORTDETAILS

No, neither of them are indexed.

My Interpretation

A row is being updated or deleted from the child PORTSERVICEORDER, and then a row is deleted from the parent table PORTDETAIL in the same transaction. There is no index on the foreign key so a lock is taken on the whole PORTSERVICEORDER table. When two sessions attempt these same steps concurrently, they would deadlock because they both hold an SX (row exclusive) lock in PORTSERVICEORDER and both are asking for an SSX (share sub-exclusive) lock on PORTSERVICEORDER.

In general, foreign keys should be indexed unless the parent table has very little DML on it. So… I asked the developers to index these foreign keys and we haven’t had deadlocks since.