Re: Traditional locking wisdom trashed.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 24 Jun 2010 13:34:05 -0700 (PDT)
Message-ID: <3345eff0-bf4a-4817-addd-1c4cb65097be_at_t10g2000yqg.googlegroups.com>



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. Received on Thu Jun 24 2010 - 15:34:05 CDT

Original text of this message