Re: RAC internals - GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED

"Dusan Bolek" <spambin_at_seznam.cz> wrote in message news:1131529679.533983.16060_at_z14g2000cwz.googlegroups.com...
> It is getting more and more strange. We encountered today a bunch of
> problems that looks like a normal deadlock caused by application
> behaviour (lot of users deadlocking each other). In this case as you
> can see the mode is just same (5 on both ends) as with the previously
> mentioned deadlocks. The only difference is that these are occurying
> between instances. The strange is that these ones were accompanied with
> the SQL that caused the problem. So I'm not sure if in our case your
> statement "If the deadlock is on mode 5 enqueues at both ends, it is
> most likely to be the foreign key locking issue" is correct.
>

    My error - my comment is only appropriate for TM enqueues     in mode 5; not TX in mode 5.

    On the other hand, I've never seen a TX in mode 5 - I had thought     that modes 4 and 6 were the only possibilities for TX. So I wonder     if we are being misled by the Metalink note.

> See below:
>
>
> *** 2005-11-09 08:16:10.087
> user session for deadlock lock 70000016a0b29b8
> pid=825 serial=32833 audsid=14358159 user: 425/USER
> O/S info: user: wasadmin, term: , ospid: 1323120, machine: cn67b1
> program: /was5/WebSphere/AppServer/java/bin/java_at_cn67b1 (
> application name: /was5/WebSphere/AppServer/java/bin/java_at_cn67b1 (,
> hash value=0
> Current SQL Statement:
> DELETE FROM SESSION_DATA WHERE SESSION_ID = :1 AND DATA_KEY = :2
> user session for deadlock lock 70000016c0b07b0
> pid=867 serial=37699 audsid=14358272 user: 425/USER
> O/S info: user: wasadmin, term: , ospid: 1323120, machine: cn67b1
> program: /was5/WebSphere/AppServer/java/bin/java_at_cn67b1 (
> application name: /was5/WebSphere/AppServer/java/bin/java_at_cn67b1 (,
> hash value=0
> Current SQL Statement:
> DELETE FROM SESSION_DATA WHERE SESSION_ID = :1 AND DATA_KEY = :2
> Global Wait-For-Graph(WFG) at ddTS[0.eff6] :
> BLOCKED 70000016a0b29b8 5 [0x20a0008][0x1cb52],[TX] [66361,527468] 0
> BLOCKER 70000016f469718 5 [0x20a0008][0x1cb52],[TX] [131371,47259] 1
> BLOCKED 70000016f469718 5 [0x20a0008][0x1cb52],[TX] [131371,47259] 1
> BLOCKER 70000016bc15980 5 [0x20a0008][0x1cb52],[TX] [131217,103403] 1
> BLOCKED 70000016b47f828 5 [0xbc001d][0x6ed23],[TX] [131217,103403] 1
> BLOCKER 70000016d0b11a0 5 [0xbc001d][0x6ed23],[TX] [131469,20450] 1
> BLOCKED 70000016d0b11a0 5 [0xbc001d][0x6ed23],[TX] [131469,20450] 1
> BLOCKER 70000016d0b8a18 5 [0xbc001d][0x6ed23],[TX] [131394,51017] 1
> BLOCKED 70000016d0b8b98 5 [0x400005][0x6914],[TX] [131394,51017] 1
> BLOCKER 70000016c0b0648 5 [0x400005][0x6914],[TX] [66403,574442] 0
> BLOCKED 70000016c0b07b0 5 [0x3d0017][0x82a7],[TX] [66403,574442] 0
> BLOCKER 70000016a0b2400 5 [0x3d0017][0x82a7],[TX] [66361,527468] 0
>
> --
> Dusan Bolek
>

Two thoughts - application oriented only:

    Does this code come from a logoff trigger ?

    Is webapplication server configured to drop a number of connections     if they have been idle for a few minutes.

The code looks like an attempt to record a logoff in the database - but in RAC, you can use the same session_id from different nodes, so maybe this code is simply not RAC-aware and causes a genuine deadlock somehow.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005