1 Reply Latest reply on May 17, 2013 8:47 AM by Alvaro

    Global Enqueue Services Deadlock detected while executing job

      Hi Gurus,

      Need your help in analying below situation.

      I got a Global Enqueue Services Deadlock in our 2-node RAC stating deadlock detected due to auto execute of job. But when I checked job status its was executed successfully. Also, the trace files gives some session id , client id which I am not able to mapped to any particular instance. trace files output as below. What would be the approach for such situtation and how to find the relevant sql which caused such deadlocks.

      ENV - 11gR2 RAC 2-node with ASM
      Linux RHEL 6.2

      Trace File output
      Trace file /diag/app/ora11g/diag/rdbms/remcorp/REMCORP1/trace/REMCORP1_j001_25571.trc
      Oracle Database 11g Enterprise Edition Release - 64bit Production
      With the Real Application Clusters and Automatic Storage Management options
      ORACLE_HOME = /u01/app/ora11g/product/11.2.0/db_1
      System name: Linux
      Node name: remedy-ebu-db1
      Release: 2.6.32-220.el6.x86_64
      Version: #1 SMP Wed Nov 9 08:03:13 EST 2011
      Machine: x86_64
      Instance name: REMCORP1
      Redo thread mounted by this instance: 1
      Oracle process number: 38
      Unix process pid: 25571, image: oracle@remedy-ebu-db1 (J001)

      *** 2013-05-17 02:00:34.174
      *** SESSION ID:(1785.23421) 2013-05-17 02:00:34.174
      *** CLIENT ID:() 2013-05-17 02:00:34.174
      *** SERVICE NAME:(SYS$USERS) 2013-05-17 02:00:34.174
      *** MODULE NAME:() 2013-05-17 02:00:34.174
      *** ACTION NAME:() 2013-05-17 02:00:34.174

      ORA-12012: error on auto execute of job 83
      ORA-00060: deadlock detected while waiting for resource
      ORA-06512: at "ARADMIN.CMS_CUSTOMER_DETAILS", line 3
      ORA-06512: at line 1

      Nikhil Mehta.
        • 1. Re: Global Enqueue Services Deadlock detected while executing job
          Look at your alert.log, search for messages such as this:
          Wed Jun 16 15:05:58 2010
          Global Enqueue Services Deadlock detected. More info in file
          Search for the .trc file mentioned and opened it, it should have all the info you need.

          Even on the error you posted, you already have some very important information:
          ORA-06512: at "ARADMIN.CMS_CUSTOMER_DETAILS", line 3
          In short, you have another job, or a query that runs at the same time as your job which wants to do some DML on rows of that table and at the same time your Job wants to do some DML on another set of rows tha table the query has locked first.

          As in:

          a) Query/Job X Locks some rows of ARADMIN.CMS_CUSTOMER_DETAILS.

          b) Your job locks some rows of ARADMIN.CMS_CUSTOMER_DETAILS.

          c) Your job wants the lock on the rows that X has locked first.

          d) X wants the lock on the rows your job has locked first.

          e) Deadlock.

          So if I have something you want, and you have something I want, we are both "deadlocked" thus LMD comes in and kill one of us to resolve the impass. It's probably killing the X job/query and so that's why your job is still executing successfully.

          So you have two options:

          1) Identify the X Job/Query -- either stop it or change it's execution time

          2) Your job - either stop it or change it's execution time
          1 person found this helpful