The problem of slow queries on v$lock just came up again on the OTN database forum, so I thought I’d better push out a post that’s been hanging around on my blog for the last few months. This is actually mentioned in MOS in note 1328789.1: “Query Against v$lock Run from OEM Performs Slowly” which points out that it is basically a problem of bad statistics and all you have to do is collect the stats.
May 2, 2013
April 13, 2013
Here’s a deadlock graph that might cause a little confusion:
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-001a0015-00014787 34 90 X 32 3 S TX-00190008-0000601b 32 3 X 34 90 S session 90: DID 0001-0022-00000327 session 3: DID 0001-0020-000009E9 session 3: DID 0001-0020-000009E9 session 90: DID 0001-0022-00000327 Rows waited on: Session 90: obj - rowid = 00030CE5 - AAAwzlAAGAABDiuAAA (dictionary objn - 199909, file - 6, block - 276654, slot - 0) Session 3: obj - rowid = 00030CE5 - AAAwzlAAGAABp8gAAA (dictionary objn - 199909, file - 6, block - 433952, slot - 0)
March 20, 2013
Here’s an oddity that I ran into a little while ago while trying to prepare a sample trace file showing a particular locking pattern; it was something that I’d done before, but trace files can change with different versions of Oracle so I decided to use a copy of 184.108.40.206 that happened to be handy at the time to check if anything had changed since the previous (11gR1) release. I never managed to finish the test; here are the steps I got through:
February 22, 2013
By some strange coincidence, the “London Bus” effect perhaps, there have been three posts on the OTN database forum in the last couple of days relating to deadlocks; and they have prompted me to indulge in a little rant about the myth of Oracle and deadlock detection; it’s the one that goes:
“Oracle detects and resolves deadlocks automatically.”
Oracle may detect deadlocks automatically, but it doesn’t resolve them, it simply reports them (by raising error ORA-00060 and rolling back one statement) then leaves the deadlocked sessions stuck until the session that received the report resolves the problem or an external agent resolves the problem.
Consider the following example (which, I have to admit, I wrote without access to a live instance):
September 9, 2011
Here’s one I keep forgetting – and spending 15 minutes trying to think of the answer before getting to the “deja vu” point again. I’ve finally decided that I’ve got to write the answer down because that will save me about 14 minutes the next time I forget.
Q. In a Statspack or AWR report there is a section titles “Segments by Row Lock Waits”. Why could an index be subject to a Row Lock Wait ?
A. Try inserting into a table from two different sessions (without committing) two rows with the same primary key. The second insert will wait on event enq: TX – row lock contention, and show up in v$lock with a lock request for a TX lock in mode 4. When you issue a commit or rollback on the first session, and the second statement errors or completes (depending on whether you commit or rollback the first session) it will increase the value for row lock waits in v$segstat (and v$segment_statistics) for the index by 1.
There are variations on the theme, of course, but the key feature is uniqueness with one session waiting for another session to commit or rollback on a conflicting value. This includes cases of foreign key constraint checking such as inserting a child for a parent that has been deleted but not committed (and there’s an interesting anomaly with that scenario which – in 10g, at least – reports more row lock waits on the parent PK than you might expect.)
August 29, 2011
Here’s a deadlock graph the appeared on Oracle-L and OTN a couple of days ago.
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-001a0002-0002a0fe 196 197 X 166 1835 S TM-0000c800-00000000 166 1835 SX 196 197 SX SSX
It’s a little unusual because instead of the common TX mode 6 (eXclusive) crossover we have one TX and one TM lock, the TX wait is for mode 4 (S) and the TM wait is for a conversion from 3 (SX) to 5 (SSX).
April 8, 2011
Here’s an example of a slightly less common data deadlock graph (dumped from 11gR2, in this case):
June 21, 2010
I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers, and never seem to remember which number goes with which name – apart from mode 6 = exclusive. So I’ve finally put up a little list somewhere I’ll always be able to find it if I need it.
February 15, 2010
Here’s an extract from a simple script I’ve run from time to time over the last 10 years to create a little testbed to check up on “foreign key locking”. I haven’t run it for some time (my notes say the last version I ran it on until very recently was 10.1.0.2). All it does is create a pair of tables with a foreign key constraint between them and an index protecting that constraint from the foreign key locking problem:
April 19, 2009
A question that comes up fairly regularly on OTN Database Forum and the comp.databases.oracle.server newsgroup is one that asks some variant of:
“When a session has some rows locked in a table, how can I find the statement that locked those rows ?”
“I have several sessions waiting for a TX lock, and have the SID of the blocking session, how do I find the blocking SQL ?”