Re: Traditional locking wisdom trashed.

From: Mladen Gogala <no_at_email.here.invalid>
Date: Thu, 24 Jun 2010 21:23:34 +0000 (UTC)
Message-ID: <pan.2010.06.24.21.23.33_at_email.here.invalid>

On Thu, 24 Jun 2010 13:34:05 -0700, Charles Hooper wrote:

> On Jun 24, 4:04 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:

>> The more experienced among us know that when having a foreign key
>> without index on the FK column(s), when the parent record is locked,
>> the entire child table is locked in the shared mode. I've seen that
>> happen numerous times in Oracle 5.1.22 - 9.2.0.8. Now, I decided to
>> test version 10.2.0.5. The tables are EMP and DEPT, and the results are
>> surprising:
>>

> (snip)

>>
>> So, the old wisdom about the child table being locked no longer
>> applies. When did this happen?

>
> Mladen,
>
> It appears that the child table locking behavior changed mid-way through
> Oracle Database 9i R2 and again in Oracle Database 11g R1. For example,
> here is a test case that causes a deadlock in Oracle Database 11g R1
> (and 11g R2), but not in Oracle Database 10g R2:
> http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-oracle-11g-
but-not-on-10g/
>
> If you look closely at the locks that are held, you will see that 10g R2
> and 11g R1 produced different lock combinations. Jonathan Lewis was
> kind enough to leave several comments in that blog article that might
> explain why you are seeing the results in your test case.
>
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"
> http://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Thanks for sharing. It seems that I'm a bit late to discover this, you were blogging about this in January. I should be visiting your blog more often, that would have prevented me from repeating your work.

-- 
http://mgogala.byethost5.com

Received on Thu Jun 24 2010 - 16:23:34 CDT