Re: query for waiters queue

• Original Message ----- From: "Sriram Kumar" <k.sriramkumar_at_gmail.com>

| Hi Jonathan,
|
| Yes. tested in 11.2.0.2, 11.2.0.3 and found a similar behaviour as
reported
| by you. Blocking TM locks not captured by v$WAIT_CHAINS
|
| Cross checked the documentation and it states V$WAIT_CHAINS is intended
to
| 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
understand
| , 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).

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

--
http://www.freelists.org/webpage/oracle-l