Re: Traditional locking wisdom trashed.
From: Thomas Kellerer <OTPXDAJCSJVU_at_spammotel.com>
Date: Thu, 24 Jun 2010 22:36:40 +0200
Message-ID: <88htumFbn0U1_at_mid.individual.net>
Mladen Gogala wrote on 24.06.2010 22:04:
> 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:
>
> So, the old wisdom about the child table being locked no longer applies.
> When did this happen?
Date: Thu, 24 Jun 2010 22:36:40 +0200
Message-ID: <88htumFbn0U1_at_mid.individual.net>
Mladen Gogala wrote on 24.06.2010 22:04:
> 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:
>
> So, the old wisdom about the child table being locked no longer applies.
> When did this happen?
With 10g.
This is documented in the concepts manual:
"The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row"
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm#sthref3103
So the locks are still there but only on row level, and only as long as Oracle needs to check the row. Received on Thu Jun 24 2010 - 15:36:40 CDT