This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 19, 2013 11:06 AM by P.Forstmann RSS

Identifying a deadlock with completely unrelated tables

Michael-O Newbie
Currently Being Moderated
Hi folks,

we receive an ORA-00060 in our database occasionally. I fully understand what could cause a deadlock. I have read several Oracle notes and threads but we do neither

- update a PK
- use a FK
- nor have a bitmap index in place

The weird thing is that the deadlock occurs when two completely unrelated tables are updated. In both cases a status column is updated with the respective PK.

Here is the trace file excerpt:
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

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-000a001a-0011f392        21     175     X             22     176           X
TX-00050012-0002d3fd        22     176     X             21     175           X

session 175: DID 0001-0017-0000127B     session 176: DID 0001-0017-00001273
session 176: DID 0001-0017-00001273     session 175: DID 0001-0017-0000127B

Rows waited on:
  Session 175: obj - rowid = 00004040 - AAAEEOAAFAAD4kOAAg
  (dictionary objn - 16448, file - 5, block - 1018126, slot - 32)
  Session 176: obj - rowid = 00004012 - AAAEEcAAFAAG6OUAAk
  (dictionary objn - 16402, file - 5, block - 1811348, slot - 36)

----- Information for the OTHER waiting sessions -----
Session 176:
  sid: 176 ser: 2271 audsid: 2291749 user: 35/SMARTLD
    flags: (0xe5) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 22 O/S info: user: oracle, term: UNKNOWN, ospid: 3700
    image: oracle@blnn726x.ww004.siemens.net (S001)
  client details:
    O/S info: user: smartld, term: JDBC, ospid: 1234
    machine: blnn725x program: SMART-LD Web Application 2.4.17
    application name: SMART-LD Web Application 2.4.17, hash value=682445801
  current SQL:
  update st_pr_prg_aufrufe set speicherung = :1  where vbs_id = :2

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=cy1wynvmwusug) -----
update st_td_arbeitsdatensatz set status = :1  where id = :2
===================================================
PROCESS STATE
As you can see tables st_pr_prg_aufrufe and st_td_arbeitsdatensatz are updated. Tables, ROWIDs and blocks are completely different.
I am completely lost where to start digging. Is anyone able to guide me into the right direction?

My system is:

- Red Hat Enterprise Linux Workstation release 6.3 (Santiago)
- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production; With the Partitioning and Real Application Testing options
  • 1. Re: Identifying a deadlock with completely unrelated tables
    Dom Brooks Guru
    Currently Being Moderated
    These are mode 6 TX locks - normally, this is as simple a deadlock scenario as you get.

    This is about transactions, not necessarily just about what statements are currently executing.

    At time T1, session S1 updates row in table T1, no commit.
    At time T2, session S2 updates row in table T2, no commit.
    At time T3, session S1 tries to updates row in table T2, waits on S2.
    At time T4, session S2 tries to updates row in table T1, waits on S1.
    Deadlock detected.

    This is a situation where the deadlock trace file is often correct about this being a flaw in the design of the application.
  • 2. Re: Identifying a deadlock with completely unrelated tables
    Michael-O Newbie
    Currently Being Moderated
    Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
    If so, how am I supposed to find the error in my app at all?
  • 3. Re: Identifying a deadlock with completely unrelated tables
    sb92075 Guru
    Currently Being Moderated
    Michael-O wrote:
    Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
    If so, how am I supposed to find the error in my app at all?
    Inspect the application code to find where UPDATE statements below exist
    update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
    update st_td_arbeitsdatensatz set status = :1 where id = :2
  • 4. Re: Identifying a deadlock with completely unrelated tables
    Dom Brooks Guru
    Currently Being Moderated
    Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
    Both statements are involved in the deadlock otherwise you wouldn't have a deadlock.

    But you have more than two locked resources involved in the deadlock and therefore two different table updates involved.

    What the deadlock graph cannot tell you is all the statement executions that were involved in getting to the deadlock situation.
  • 5. Re: Identifying a deadlock with completely unrelated tables
    Michael-O Newbie
    Currently Being Moderated
    sb92075 wrote:
    Michael-O wrote:
    Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
    If so, how am I supposed to find the error in my app at all?
    Inspect the application code to find where UPDATE statements below exist
    update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
    update st_td_arbeitsdatensatz set status = :1 where id = :2
    Well, actually those are completely distinct parts of the webapp (Java) where this update statement is triggered but both do have one in common. They both call a helper class with contains a static method for manipulating column values. First, the column is fetched, then modified and finally updated. Select and update happen in two differnt connections. I have modified that right now.

    Generally, can this be a case due to static methods and deadlocks? Even though, this has never happens in conjunction with other tables but only these two.
  • 6. Re: Identifying a deadlock with completely unrelated tables
    sb92075 Guru
    Currently Being Moderated
    Michael-O wrote:
    sb92075 wrote:
    Michael-O wrote:
    Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
    If so, how am I supposed to find the error in my app at all?
    Inspect the application code to find where UPDATE statements below exist
    update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
    update st_td_arbeitsdatensatz set status = :1 where id = :2
    Well, actually those are completely distinct parts of the webapp (Java) where this update statement is triggered but both do have one in common. They both call a helper class with contains a static method for manipulating column values. First, the column is fetched, then modified and finally updated. Select and update happen in two differnt connections. I have modified that right now.

    Generally, can this be a case due to static methods and deadlocks? Even though, this has never happens in conjunction with other tables but only these two.
    Oracle is too dumb to lie about the deadlock & only solution is to modify the application
    such that the deadlock contention does not occur.
  • 7. Re: Identifying a deadlock with completely unrelated tables
    Michael-O Newbie
    Currently Being Moderated
    sb92075 wrote:
    Michael-O wrote:
    sb92075 wrote:
    Michael-O wrote:
    Dom, thanks for the reply. Do you mean that the updates I see are not necessarily related to the deadlock but simply happen in the deadlock transactions?
    If so, how am I supposed to find the error in my app at all?
    Inspect the application code to find where UPDATE statements below exist
    update st_pr_prg_aufrufe set speicherung = :1 where vbs_id = :2
    update st_td_arbeitsdatensatz set status = :1 where id = :2
    Well, actually those are completely distinct parts of the webapp (Java) where this update statement is triggered but both do have one in common. They both call a helper class with contains a static method for manipulating column values. First, the column is fetched, then modified and finally updated. Select and update happen in two differnt connections. I have modified that right now.

    Generally, can this be a case due to static methods and deadlocks? Even though, this has never happens in conjunction with other tables but only these two.
    Oracle is too dumb to lie about the deadlock & only solution is to modify the application
    such that the deadlock contention does not occur.
    That's easier said than done. Identifying that code is really hefty. I try a few options on the static method and see if this really applies.
  • 8. Re: Identifying a deadlock with completely unrelated tables
    sb92075 Guru
    Currently Being Moderated
    Michael-O wrote:

    That's easier said than done. Identifying that code is really hefty. I try a few options on the static method and see if this really applies.
    The other option is to do nothing & live with the (occasional?) deadlocks.
  • 9. Re: Identifying a deadlock with completely unrelated tables
    869405 Newbie
    Currently Being Moderated
    Try this simple query to see who is blocking who:

    select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2;
  • 10. Re: Identifying a deadlock with completely unrelated tables
    sb92075 Guru
    Currently Being Moderated
    Pierre wrote:
    Try this simple query to see who is blocking who:

    select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2;
    Oracle automagically detects & resolves Deadlock before you can even think to launch SQL above!
  • 11. Re: Identifying a deadlock with completely unrelated tables
    P.Forstmann Guru
    Currently Being Moderated
    Try to look for other places in code where same tables are updated but in a different order. A frequent deadlock cause is to lock same rows from different tables but not in the same order.
  • 12. Re: Identifying a deadlock with completely unrelated tables
    869405 Newbie
    Currently Being Moderated
    pmdba@gemprod > select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    2 from v$lock l1, v$session s1, v$lock l2, v$session s2
    3 where s1.sid=l1.sid and s2.sid=l2.sid
    4 and l1.BLOCK=1 and l2.request > 0
    5 and l1.id1 = l2.id1
    6 and l2.id2 = l2.id2;

    BLOCKING_STATUS
    ----------------------------------------------------------------------------------------------------
    PMDBA@WAI\PGH068 ( SID=301 ) is blocking PMDBA@WAI\PGH068 ( SID=203 )
    PMDBA@WAI\PGH068 ( SID=203 ) is blocking PMDBA@WAI\PGH068 ( SID=301 )
  • 13. Re: Identifying a deadlock with completely unrelated tables
    sb92075 Guru
    Currently Being Moderated
    Pierre wrote:
    pmdba@gemprod > select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    2 from v$lock l1, v$session s1, v$lock l2, v$session s2
    3 where s1.sid=l1.sid and s2.sid=l2.sid
    4 and l1.BLOCK=1 and l2.request > 0
    5 and l1.id1 = l2.id1
    6 and l2.id2 = l2.id2;

    BLOCKING_STATUS
    ----------------------------------------------------------------------------------------------------
    PMDBA@WAI\PGH068 ( SID=301 ) is blocking PMDBA@WAI\PGH068 ( SID=203 )
    PMDBA@WAI\PGH068 ( SID=203 ) is blocking PMDBA@WAI\PGH068 ( SID=301 )
    >
    pmdba@gemprod > select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    2 from v$lock l1, v$session s1, v$lock l2, v$session s2
    3 where s1.sid=l1.sid and s2.sid=l2.sid
    4 and l1.BLOCK=1 and l2.request > 0
    5 and l1.id1 = l2.id1
    6 and l2.id2 = l2.id2;

    BLOCKING_STATUS
    ----------------------------------------------------------------------------------------------------
    PMDBA@WAI\PGH068 ( SID=301 ) is blocking PMDBA@WAI\PGH068 ( SID=203 )
    PMDBA@WAI\PGH068 ( SID=203 ) is blocking PMDBA@WAI\PGH068 ( SID=301 )
    above is NOT a DEADLOCK

    not every LOCK is a DEADLOCK.

    Deadlocks get recorded into alert_SID.log file
    post the ORA-00060 error from your alert logfile.
    [oracle@localhost ~]$ oerr ora 60
    00060, 00000, "deadlock detected while waiting for resource"
    // *Cause:  Transactions deadlocked one another while waiting for resources.
    // *Action: Look at the trace file to see the transactions and resources
    //          involved. Retry if necessary.
  • 14. Re: Identifying a deadlock with completely unrelated tables
    Michael-O Newbie
    Currently Being Moderated
    P. Forstmann wrote:
    Try to look for other places in code where same tables are updated but in a different order. A frequent deadlock cause is to lock same rows from different tables but not in the same order.
    Obviously I can look only for those statements which run in parallel, right?

    Edited by: Michael-O on 19.02.2013 01:35
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points