Re: Traditional locking wisdom trashed.
From: The Boss <usenet_at_No.Spam.Please.invalid>
Date: Fri, 25 Jun 2010 00:30:52 +0200
Message-ID: <4c23dc9b$0$18808$e4fe514c_at_dreader16.news.xs4all.nl>
Mladen Gogala wrote:
> 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.
Date: Fri, 25 Jun 2010 00:30:52 +0200
Message-ID: <4c23dc9b$0$18808$e4fe514c_at_dreader16.news.xs4all.nl>
Mladen Gogala wrote:
> 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.
Mladen,
Jonathan also wrote a follow-up on his own blog in february, with additional
info only a few days ago:
http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/
-- JeroenReceived on Thu Jun 24 2010 - 17:30:52 CDT