Traditional locking wisdom trashed.
Date: Thu, 24 Jun 2010 20:04:53 +0000 (UTC)
Message-ID: <pan.2010.06.24.20.04.53_at_email.here.invalid>
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_at_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/*****_at_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
Received on Thu Jun 24 2010 - 15:04:53 CDT