Snapshot row cache enqueue lock - Reviving 2001 Oracle-l post.
- From: "Tim Hopkins" <oracle-l@xxxxxxxxxxxxxxxxxx>
- To: steve.adams@xxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 2 Nov 2006 17:42:24 -0000 (GMT)
Hi all,
We still have a few production systems here on 8.1.7.0 and have been
experiencing MV-refresh related deadlocks intermittently for some time.
I've just been asked to have a look at it and came across this oracle-l
post from 2001 which sounds like the exact situation. The systemstate
dump seems to agree with the first statement from Steve's post.
The operation DBAs assure me that, whilst it is no longer set, the
system
was run with event 32333 (null refresh disable) set and the problems
still occurred so that once again brings me back to the first statement.
Hoping Steve with respond here: Did Oracle ever agree to log a bug and
investigate?
If so, was it the unpublished Bug 2664217 - "Undetected deadlock
(dc_objects V library cache lock) invalidating dependencies" by any
chance?
Has anyone successfully applied patch 8.1.7.4.8 to avoid this problem?
Cheers,
Tim
RE: ERROR: WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Mon, 12 Feb 2001 21:28:31 -0800
Message-ID: <F001.002B2285.20010212211127@xxxxxxxxxxx>
Hi Glenn,
I got one of these last night.
The snapshot refresh process had an exclusive lock on the row cache
enqueue for the snapshot and was waiting for a shared library cache lock
on the base table. This is the WRONG locking order. Oracle is supposed to
always take library cache locks before row cache enqueue locks. I am
attempting to get Support to open a bug on it at the moment.
The other part of the problem is the null-refresh optimization introduced
for 8i. The first DML on a snapshot master after a snapshot refresh
actually changes the metadata for the base table to record the SCN of the
DML operation in TAB$.SPARE3. Snapshot refresh operations record their SCN
in SUM$.LASTREFRESHSCN. When a refresh is due, if the last refresh SCN is
still higher than the SCN in TAB$, then no work is needed. However,
maintaining these SCNs means reading and updating the metadata for DML
operations, and that means taking X locks in the library cache and the
dictionary cache, in that order. The X lock in the library cache has
caused lot of problems, of which this is but another.
Oracle have "fixed" the null-refresh optimization in 9i by calling the
dictionary cache primitives directly, so that the X lock in the library
cache will not be needed any more. However, I suspect that the locking
order for the snapshot refresh is a bug too. Let's see what Oracle say ...
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Tuesday, 13 February 2001 2:16
To: Oracledba_at_Lazydba. Com; ORACLE-L_at_fatcity.com
The system was not too busy. Processes running were materialized view
refreshes (stored procs doing rollups, joins, etc...). The system was then
locked up. Some queries could be run, others couldn't. Refreshes never
completed. I had to alter system kill to release the hang.
Here are the errors:
>From the alert file;
WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=30
--
//www.freelists.org/webpage/oracle-l
Other related posts:
