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@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

On Jun 24, 4:04*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
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/2...ut-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.

Mladen Gogala wrote on 24.06.2010 22:04:
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/B...htm#sthref3103

So the locks are still there but only on row level, and only as long as Oracle needs to check the row.

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


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

Mladen Gogala wrote:
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/2...5/lock-horror/

--
Jeroen

Mladen:

Tom Kyte has written about this several times and when these changes
started showing up. Probably would not be hard to find Toms writeups
if you dig around some.

Hey you are up to 10.2.0.5 now? Some of us are readying plans to go
from 11.1 to 11.2 !!!