Traditional locking wisdom trashed.
in [Oracle]
|
Prev: Sun IDM resources | Multiple Locations
Next: understanding oracle terminology - instance, database, sid,schema
From: John Hurley on 24 Jun 2010 20:23 Mladen: > 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 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 !!! |