Re: query for waiters queue

From: Jonathan Lewis <>
Date: Mon, 21 Jan 2013 13:26:36 -0000
Message-ID: <E55ED1F001A7408D9C069FAE36CD1ABC_at_Primary>

  • Original Message ----- From: "Sriram Kumar" <>

| Hi Jonathan,
| Yes. tested in, and found a similar behaviour as
| by you. Blocking TM locks not captured by v$WAIT_CHAINS
| Cross checked the documentation and it states V$WAIT_CHAINS is intended
| capture all blocked sessions but ends up capturing only blocking TX locks
| and not blocking TM locks as tested.
| V$WAIT_CHAINS is built on top of X$KSDHNG_CHAIN and from what i
| , this view is populated by DIAG process every 3 seconds for local hang
| analyse information and every10 seconds for RAC.
| I would read the missing TM locks behaviour as a bug. Not sure how others
| would read this
| interestingly, one more observation I had and is new learning to me and
| could be useful to others
| Session 1. Lock table in exclusive mode
| Session 2. Update a row in the table
| Session 3. Update the same row that is locked in session 2
| We will see session 2 and 3 waiting on Enqueue TM Contention. Now issue a
| commit or rollback on session 1. Session 3 would still show a TM
| contention. I was expecting a TX Enqueue when the first session completed
| but Oracle decides to keeps the old TM lock mode as the blocking mode.

Nice experiment, but there's one detail you didn't mention: the TM lock changes from request=3 to request = 6 after the rollback. I think you can definitely say this is a bug. (The situation tidies itself up after the next session commits or rollbacks).


Jonathan Lewis

Author: Oracle Core (Apress 2011)

Received on Mon Jan 21 2013 - 14:26:36 CET

Original text of this message