Prev: Sun IDM resources | Multiple Locations
Next: understanding oracle terminology - instance, database, sid,schema
From: Mladen Gogala on 24 Jun 2010 16: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:

SQL> connect scott/tiger(a)stag3
Connected.
SQL> select index_name from user_indexes
2 where table_name='EMP';

INDEX_NAME
------------------------------
PK_EMP
EMP_ENAME_ID

Elapsed: 00:00:00.33
SQL> update dept set deptno=10 where dname='ACCOUNTING';

1 row updated.

Elapsed: 00:00:00.18
SQL> select constraint_name,constraint_type,r_constraint_name
2 from user_constraints
3 where table_name='EMP';

CONSTRAINT_NAME C R_CONSTRAINT_NAME
------------------------------ - ------------------------------
SYS_C00181250 C
PK_EMP P
FK_DEPTNO R PK_DEPT

Elapsed: 00:00:00.18
SQL>

So, there is foreign key, there is no index on the deptno column, the
table EMP should be locked in the shared mode, right? Well, not exactly:

SQL> connect system/*****@stag3
Connected.
SQL> select type,id1,id2,lmode from v$lock where sid=559;

TY ID1 ID2 LMODE
-- ---------- ---------- ----------
TM 207829 0 3
TX 1179686 7228500 6

Elapsed: 00:00:00.20
SQL> select owner,object_name,object_type from dba_objects
2 where object_id in (207829);

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------- -------------------
SCOTT DEPT TABLE

Elapsed: 00:00:00.12
SQL>

So, there are no locks on the EMP table??? Does that mean that we can
update the EMP table? Yes, it does:

SQL> select ename from emp where deptno=10;

ENAME
----------
CLARK
KING
MILLER

Elapsed: 00:00:00.13
SQL> update emp set sal=sal+100 where ename='CLARK';

1 row updated.

Elapsed: 00:00:00.07
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.09
SQL> update emp set deptno=10 where ename='CLARK';

1 row updated.

Elapsed: 00:00:00.07
SQL>

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


--
http://mgogala.byethost5.com
From: Charles Hooper on 24 Jun 2010 16:34
On Jun 24, 4:04 pm, Mladen Gogala <n...(a)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.
From: Thomas Kellerer on 24 Jun 2010 16:36
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.




From: Mladen Gogala on 24 Jun 2010 17:23
On Thu, 24 Jun 2010 13:34:05 -0700, Charles Hooper wrote:

> On Jun 24, 4:04 pm, Mladen Gogala <n...(a)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
From: The Boss on 24 Jun 2010 18:30
Mladen Gogala wrote:
> On Thu, 24 Jun 2010 13:34:05 -0700, Charles Hooper wrote:
>
>> On Jun 24, 4:04 pm, Mladen Gogala <n...(a)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/

--
Jeroen


 |  Next  |  Last
Pages: 1 2
Prev: Sun IDM resources | Multiple Locations
Next: understanding oracle terminology - instance, database, sid,schema