ORA-00060: deadlock detected while waiting for resource. Global Enqueue Services Deadlock detected. More info in file

Environment:

2-node RAC 11.2.0.3.

The following messages are constantly showing up in the alert log.

ORA-00060: deadlock detected while waiting for resource


Tue Jul 21 11:29:29 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.
Tue Jul 21 11:29:40 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.
Tue Jul 21 11:32:12 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.
Tue Jul 21 11:33:03 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.
Tue Jul 21 11:33:29 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.
Tue Jul 21 11:34:22 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.
Tue Jul 21 11:35:04 2015
Global Enqueue Services Deadlock detected. More info in file
 /apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc.


Checking the specified LMD tracefile revealed the locks, sql text and other information.

Note: LMDx - The Global Enqueue Service Daemon (LMD) is the lock agent process that manages enqueue manager service requests for Global Cache Service enqueues to control access to global enqueues and resources. The LMD process also handles deadlock detection and remote enqueue requests. Remote resource requests are the requests originating from another instance.

Tracefile "/apps/oracle/diag/rdbms/testdb/testdb2/trace/testdb2_lmd0_21823642.trc" shows the following:

Submitting asynchronized dump request [28]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.a5c4] :
BLOCKED 0x7000001cbfccba8 5 wq 2 cvtops x1 TX 0x1470008.0x72e(ext 0x5,0x0)[2009-0094-000001E3] inst 2
BLOCKER 0x7000001c60cd8b8 5 wq 1 cvtops x28 TX 0x1470008.0x72e(ext 0x5,0x0)[2010-010B-00000008] inst 2
BLOCKED 0x7000001c7267ed8 5 wq 2 cvtops x1 TX 0x88000e.0x3578(ext 0x5,0x0)[2010-010B-00000008] inst 2
BLOCKER 0x7000001c7267d08 5 wq 1 cvtops x28 TX 0x88000e.0x3578(ext 0x5,0x0)[2009-0094-000001E3] inst 2

*** 2015-07-21 12:13:52.792


From the above, it is clear that two sessions involved in TX 0x1470008.0x72e and TX 0x88000e.0x3578 form a deadlock, both sessions are from instance 2.

The trace file also contain the following:

 user session for deadlock lock 0x7000001b1ff2b68
  .........
  current SQL:
  delete from DATA where CLEANUP <= :1 and rownum <= :2 



Oracle states that "this is a typical application transaction TX enqueue lock, usually caused by SQL commit sequence and high concurrency. To avoid such deadlock, application code and logic need to be modified."

This information was communicated to the application team for further investigation.