Example
Richard Xu, August 15, 2012 - 3:39 pm UTC
Hi Tom,
Thanks for your reply. I totally agree that the children tables would not be locked when upgrading the master table for non-key columns.
However, I have an example which really confused me. I have a master table called MGMT_CENTRES, which has 67 foreign keys pointing to it.
select uc.constraint_name,uc.constraint_type, uc.table_name, ucc.column_name, ucc.position
from user_constraints uc
inner join user_cons_columns ucc
on (uc.constraint_name = ucc.constraint_name and uc.owner = ucc.owner)
where uc.table_name='MGMT_CENTRES'
order by uc.constraint_type;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME POSITION
------------------------------ - ------------------------------ ------------------------------ ----------
SYS_C009401 C MGMT_CENTRES HOSPITAL_CODE
SYS_C009409 C MGMT_CENTRES DIAL_HOSP
SYS_C009406 C MGMT_CENTRES BCTS_HOSP
SYS_C009402 C MGMT_CENTRES CENTRE_NAME
SYS_C0073438 C MGMT_CENTRES SOURCE
SYS_C009412 C MGMT_CENTRES TX_HOSP
MGMT_CNTR_PK P MGMT_CENTRES HOSPITAL_CODE 1
CNT_CNT_PART_OF_FK R MGMT_CENTRES PART_OF 1
MGMT_CNTR_MGMT_CNTR_FK R MGMT_CENTRES MGMT_CNTR_HOSPITAL_CODE 1
UK_MGMT_CENTRES_HOSP_ID U MGMT_CENTRES HOSP_ID 1
10 rows selected.
select uc.constraint_name,uc.constraint_type, uc.table_name, ucc.column_name, ucc.position, ucr.table_name master_table, uccr.column_name
from user_constraints uc
inner join user_cons_columns ucc
on (uc.constraint_name = ucc.constraint_name and uc.owner = ucc.owner)
inner join user_constraints ucr
on (uc.r_constraint_name = ucr.constraint_name and uc.r_owner = ucr.owner)
inner join user_cons_columns uccr
on (ucr.constraint_name = uccr.constraint_name and ucr.owner = uccr.owner)
where ucR.table_name='MGMT_CENTRES'
and uc.constraint_type='R'
order by uc.table_name;
CONSTRAINT_NAME C TABLE_NAME COLUMN_NAME POSITION MASTER_TABLE COLUMN_NAME
------------------------------ - ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------
PRO_ACT_CNT_FK R ACTIVITIES CNT_ID 1 MGMT_CENTRES HOSPITAL_CODE
FK_MGMT_CNTR_BX_EVENT R BX_EVENT BX_MGMT_CNTR_CODE 1 MGMT_CENTRES HOSPITAL_CODE
FK_CDU_WLIST_MGMT_TO R CDU_WLIST TO_MGMT_CNTR 1 MGMT_CENTRES HOSPITAL_CODE
FK_CDU_WLIST_MGMT_FROM R CDU_WLIST FROM_MGMT_CNTR 1 MGMT_CENTRES HOSPITAL_CODE
CDV_ANNUAL_CHECKS_MGMT_FK R CDV_ANNUAL_CHECKS MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
CDV_ASSFS_HOSP_CODE_FK R CDV_ASSESS_FSHEET MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
CPR_CNT_FK R CENTRE_PROGRAMS CNT_HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
PRO_CNP_CNT_FK R CENTRE_PROVIDERS CNT_ID 1 MGMT_CENTRES HOSPITAL_CODE
PRO_CNS_CNT_FK R CENTRE_SERVICES CNT_ID 1 MGMT_CENTRES HOSPITAL_CODE
COMPS_HOSP_FK R COMPLICATIONS VISIT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
CORR_FU_HOSP_CODE_FK R CORR_FOLLOW_UP_CHNG HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
DCSSC_MGMT_CENTRES_FK R DC_SHIFT_STATION_CAPACITY DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
DSTATUS_DIAL_CNT_FK R DIALYSIS_STATUS DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
DC_PAT_LOC_MGMT_CENTRES_FK R DIAL_CENTRE_PAT_LOCATIONS DC_CODE 1 MGMT_CENTRES HOSPITAL_CODE
DPS_MGMT_CNTR_FK R DIAL_PERM_SCHEDULE MGMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
DWSDET_MGMT_FK R DIAL_WKLY_SDET MGMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
DWSHDR_MGMT_CNTR_FK R DIAL_WKLY_SHDR DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
DRUG_LAB_FREQ_MGMT_CNTR_FK R DRUG_LAB_FREQUENCY HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
EMFAC_MGMT_CNTR_FK R EM_FACILITIES MGMT_CNTR_HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
EMFAC_HOSP_CODE_FK R EM_FACILITIES HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
EMRUN_HOSP_CODE_FK R EM_RUN_SCHEDULE HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
FAX_LOG_TB_MGMT_FK R FAX_LOG_TB MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
GP_TRACE_MGMT_FK R GP_TRACE MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
HOSP_ADM_HOSP_CODE_FK R HOSPITAL_ADMISSIONS HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
HSR_MGMT_CENTRES_FK R HOSPITAL_SPECIFIC_RECORD HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
MGMT_CENTRE_FK R HOSP_IMG HOSP_CODE 1 MGMT_CENTRES HOSPITAL_CODE
IAMHD_HD_RX_MGMT_FK R IAMHD_HD_RX MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
IAMHD_PTS_DIAL_CNT_FK R IAMHD_PTS DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
IAMHD_START_DIAL_CNT_FK R IAMHD_STARTS DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_CODE_MGMT_FK R INT_CODE MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_FOOT_MGMT_FK R INT_FOOT MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_INSULIN_MGMT_FK R INT_INSULIN MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_LIFE_STYLE_MGMT_FK R INT_LIFE_STYLE MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_MS_EXAM_MGMT_FK R INT_MS_EXAM MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_NOTE_MGMT_FK R INT_NOTE MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_NOTE_TEMP_MGMT_FK R INT_NOTE_TEMP MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_NUTRITION_MGMT_FK R INT_NUTRITION MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_PHARMACY_MGMT_FK R INT_PHARMACY MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_PHYSICAL_MGMT_FK R INT_PHYSICAL MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_PHY_EXAM_MGMT_FK R INT_PHY_EXAM MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_PSYCHIATRY_MGMT_FK R INT_PSYCHIATRY MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_REACTION_MGMT_FK R INT_REACTION MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_RENAL_MGMT_FK R INT_RENAL MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_SCHOOL_MGMT_FK R INT_SCHOOL MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_SELF_MGNMT_MGMT_FK R INT_SELF_MGNMT MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_SYMPTOMS_MGMT_FK R INT_SYMPTOMS MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_VISIT_MGMT_FK R INT_VISIT MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_VISIT_PACKAGE_MGMT_FK R INT_VISIT_PACKAGE MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_VISIT_PACKAGE_TEMP_MGMT_FK R INT_VISIT_PACKAGE_TEMP MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_VISIT_TEMP_ALL_MGMT_FK R INT_VISIT_TEMP_ALL MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
INT_VISIT_TEMP_ONE_MGMT_FK R INT_VISIT_TEMP_ONE MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
LAB_MGMT_FREQ_MGMT_CNTR_FK R LAB_MGMT_FREQUENCY HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
CNT_CNT_PART_OF_FK R MGMT_CENTRES PART_OF 1 MGMT_CENTRES HOSPITAL_CODE
MGMT_CNTR_MGMT_CNTR_FK R MGMT_CENTRES MGMT_CNTR_HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
NOTE_TEMPLATES_MGMT_FK R NOTE_TEMPLATES MGNMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
FK_MGMT_CENTRES_PATH_EVENT R PATH_EVENT PATH_MGMT_CNTR_CODE 1 MGMT_CENTRES HOSPITAL_CODE
MCP_MGMT_CNTR_FK R PATIENT_MGMT_CENTRES MGMT_CNTR_HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
FK_HAND_DART_DC R PAT_HANDY_DART_SCH DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
PRA_USER_HOSP_CODE_FK R PRA_USERS HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
REC_BAX_HOSP_CODE_FK R REC_BAXTER HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
REC_BAXNS_HOSP_CODE_FK R REC_BAX_NS HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
REC_BAXPD_HOSP_CODE_FK R REC_BAX_PD HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
STDD_DIAL_CNT_FK R STAT_DIAL_DAYS DIAL_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
STDD_HOSP_CODE_FK R STAT_DIAL_DAYS HOSPITAL_CODE 1 MGMT_CENTRES HOSPITAL_CODE
TRANSPLANTS_TX_CENTRE_FK R TRANSPLANTS TX_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
FK_TX_REF_TO_MC R TX_REF REF_TO_MGMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
FK_TX_REF_FROM_MC R TX_REF REF_FROM_MGMT_CENTRE 1 MGMT_CENTRES HOSPITAL_CODE
67 rows selected.
I used another session as sys to get the session info:
select sid, serial#,username,status,program,osuser from v$session where username='PRA_OWNER' and osuser='oracle';
SID SERIAL# USERNAME STATUS PROGRAM OSUSER
---------- ---------- ------------------------------ -------- ------------------------------------------------ ------------------------------
489 10656 PRA_OWNER INACTIVE sqlplus@cheddar (TNS V1-V3) oracle
When I executed an update in the first session:
update mgmt_centres
set mgmt_centre_type=2, clinical_area=3
where hospital_code=90100;
I could see there are locks on the children tables. There is only one SQL statement in the first session, the update:
SQL> select session_id, owner, name, mode_held, mode_requested, last_convert, blocking_others from dba_dml_locks where session_id=489;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
489 PRA_OWNER INT_VISIT_TEMP_ALL None Share 122 Not Blocking
489 PRA_OWNER ACTIVITIES Row-S (SS) None 509 Not Blocking
489 PRA_OWNER CENTRE_PROGRAMS Row-S (SS) None 509 Not Blocking
489 PRA_OWNER CENTRE_PROVIDERS Row-S (SS) None 509 Not Blocking
489 PRA_OWNER CENTRE_SERVICES Row-S (SS) None 509 Not Blocking
489 PRA_OWNER DC_SHIFT_STATION_CAPACITY Row-S (SS) None 509 Not Blocking
489 PRA_OWNER DIALYSIS_STATUS Row-S (SS) None 509 Not Blocking
489 PRA_OWNER DIAL_CENTRE_PAT_LOCATIONS Row-S (SS) None 509 Not Blocking
489 PRA_OWNER DIAL_PERM_SCHEDULE Row-S (SS) None 509 Not Blocking
489 PRA_OWNER DIAL_WKLY_SHDR Row-S (SS) None 509 Not Blocking
489 PRA_OWNER HOSPITAL_SPECIFIC_RECORD Row-S (SS) None 509 Not Blocking
11 rows selected.
So, my question is: where are these locks come from? If they are not triggered by the foreign key?
This single update statement took 8 minutes or longer for executing. How to improve it?
Thanks.
Richard Xu
August 17, 2012 - 2:52 pm UTC
do you see the types of locks - that is the lowest level of locking, highly concurrent.
as for the runtime, I cannot comment. You do not tell me access paths, you do not tell me how many rows updated, you do not tell me what is the primary key - you tell me in short, pretty much nothing.
reproduce the issue with TWO tables, few columns and a little bit of data. then we can talk.
Franck Pachot, August 19, 2012 - 12:41 pm UTC
Hello.
For Richard, here is where that SS lock is documented by Oracle:
MOS ID 223303.1 - Correction to 9.2.0 foreign key constraint locking behaviour, per documentation <Bug:2546492>
As those 11 SS locks are related with indexed foreign keys only, and there are 67 foreign keys in total, I suppose that there are 56 unindexed ones. And the long runtime may come from one of those (searching for child rows in a large table through a table full scan).
Regards,
Franck.
@Franck Pachot
Rajeshwaran Jeyabal, August 22, 2012 - 8:06 am UTC
@Franck Pachot
And the long runtime may come from one of those
(searching for child rows in a large table through a table full scan).
Why to search for child rows in large table? since he is updating a non-primary key columns in master table
August 28, 2012 - 1:36 pm UTC
he is giving a what if, a reason you might want to index a foreign key in some situations.
Can't reproduce it in two tables
Richard Xu, August 29, 2012 - 4:49 pm UTC
Hi Tom,
I tried to reproduce it with two tables but not succeeded. I think that the reason maybe it is really a short period for locking and releasing it for just one child table.
I wonder if there is a way I could trace the lock(for the lock being taken, and the lock release) for each table during a session?
The update statement is actually waiting the lock release for one of the child table, which is updated by another session but not committed. Once the other session committed and release the lock of the child table, my update statement can go through.
When Lock happens:
SQL> select * from dba_dml_locks order by name;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
521 PRA_OWNER ACTIVITIES Row-S (SS) None 14 Not Blocking
521 PRA_OWNER CENTRE_PROGRAMS Row-S (SS) None 14 Not Blocking
521 PRA_OWNER CENTRE_PROVIDERS Row-S (SS) None 14 Not Blocking
521 PRA_OWNER CENTRE_SERVICES Row-S (SS) None 14 Not Blocking
521 PRA_OWNER DC_SHIFT_STATION_CAPACITY Row-S (SS) None 14 Not Blocking
521 PRA_OWNER DIALYSIS_STATUS Row-S (SS) None 14 Not Blocking
521 PRA_OWNER DIAL_CENTRE_PAT_LOCATIONS Row-S (SS) None 14 Not Blocking
521 PRA_OWNER DIAL_PERM_SCHEDULE Row-S (SS) None 14 Not Blocking
320 PRA_OWNER DIAL_PERM_SCHEDULE Row-X (SX) None 9380 Not Blocking
*** 521 PRA_OWNER DIAL_WKLY_SDET None Share 14 Not Blocking
*** 275 PRA_OWNER DIAL_WKLY_SDET Row-X (SX) None 45 Blocking
454 PRA_OWNER PATIENTS_T Row-X (SX) None 1262 Not Blocking
478 PRA_OWNER PATIENT_ACTIVITIES Row-X (SX) None 27 Not Blocking
478 PRA_OWNER PATIENT_ACTIVITY_SERVICES Row-X (SX) None 27 Not Blocking
360 PRA_OWNER PAT_COM Row-S (SS) None 12 Not Blocking
360 PRA_OWNER PAT_COM_EVENT Row-S (SS) None 5 Not Blocking
506 BCTS PRE_ASSESS Row-X (SX) None 25 Not Blocking
414 PRA_OWNER TX_REF Row-S (SS) None 438 Not Blocking
My update statement is in session 521.
After the other session releases the lock of the child table, there is no blocking lock:
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
521 PRA_OWNER ACTIVITIES Row-S (SS) None 154 Not Blocking
521 PRA_OWNER CENTRE_PROGRAMS Row-S (SS) None 154 Not Blocking
521 PRA_OWNER CENTRE_PROVIDERS Row-S (SS) None 154 Not Blocking
521 PRA_OWNER CENTRE_SERVICES Row-S (SS) None 154 Not Blocking
521 PRA_OWNER DC_SHIFT_STATION_CAPACITY Row-S (SS) None 154 Not Blocking
521 PRA_OWNER DIALYSIS_STATUS Row-S (SS) None 154 Not Blocking
521 PRA_OWNER DIAL_CENTRE_PAT_LOCATIONS Row-S (SS) None 154 Not Blocking
521 PRA_OWNER DIAL_PERM_SCHEDULE Row-S (SS) None 154 Not Blocking
320 PRA_OWNER DIAL_PERM_SCHEDULE Row-X (SX) None 9354 Not Blocking
275 PRA_OWNER DIAL_WKLY_SDET Row-X (SX) None 19 Not Blocking
521 PRA_OWNER DIAL_WKLY_SHDR Row-S (SS) None 154 Not Blocking
521 PRA_OWNER EM_RUN_SCHEDULE Row-S (SS) None 129 Not Blocking
521 PRA_OWNER HOSPITAL_SPECIFIC_RECORD Row-S (SS) None 154 Not Blocking
521 PRA_OWNER HOSP_IMG Row-S (SS) None 129 Not Blocking
521 PRA_OWNER MGMT_CENTRES Row-X (SX) None 129 Not Blocking
521 PRA_OWNER MGMT_CENTRES_JN Row-X (SX) None 129 Not Blocking
454 PRA_OWNER PATIENTS_T Row-X (SX) None 1236 Not Blocking
478 PRA_OWNER PATIENT_ACTIVITIES Row-X (SX) None 1 Not Blocking
478 PRA_OWNER PATIENT_ACTIVITY_SERVICES Row-X (SX) None 1 Not Blocking
521 PRA_OWNER PATIENT_MGMT_CENTRES Row-S (SS) None 129 Not Blocking
360 PRA_OWNER PAT_COM Row-S (SS) None 7 Not Blocking
414 PRA_OWNER TX_REF Row-S (SS) None 412 Not Blocking
And after I commit or rollback, there is no SS lock by my session(521)
SQL> select * from dba_dml_locks order by name;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
320 PRA_OWNER DIAL_PERM_SCHEDULE Row-X (SX) None 11141 Not Blocking
275 PRA_OWNER DIAL_WKLY_SDET Row-X (SX) None 1806 Not Blocking
436 PRA_OWNER INT_NOTE Row-X (SX) None 53 Not Blocking
328 PRA_OWNER INT_NOTE Row-X (SX) None 593 Not Blocking
200 PRA_OWNER MEDICATIONS Row-X (SX) None 12 Not Blocking
345 PRA_OWNER PATIENTS_T Row-X (SX) None 1446 Not Blocking
248 PRA_OWNER PATIENTS_T Row-X (SX) None 81 Not Blocking
247 PRA_OWNER PAT_COM Row-S (SS) None 62 Not Blocking
360 PRA_OWNER PAT_COM Row-S (SS) None 364 Not Blocking
450 PRA_OWNER PAT_COM Row-S (SS) None 5 Not Blocking
450 PRA_OWNER PAT_COM_EVENT Row-S (SS) None 5 Not Blocking
336 BCTS PRE_ASSESS Row-X (SX) None 398 Not Blocking
506 BCTS PRE_ASSESS Row-X (SX) None 84 Not Blocking
461 BCTS PRE_ASSESS Row-X (SX) None 1740 Not Blocking
198 BCTS PRE_ASSESS Row-X (SX) None 671 Not Blocking
345 BCTS REFERRAL_T Row-X (SX) None 1446 Not Blocking
345 BCTS T_LDONOR Row-X (SX) None 1471 Not Blocking
Richard Xu
Deleting child table records causes share mode lock on parent table
Amit, August 22, 2013 - 8:41 pm UTC
Hi Tom,
So far, I knew that if parent table records are deleted, there will be a lock on child table.
But, I recently noticed that while deleting child table records, there is share mode lock on parent table. Lock on parent table did not block any other DML operations, but I am curious to know why Oracle locks parent table while deleting child table records.
Thanks,
-Amit.
/****************************************************/
drop table child;
drop table parent;
create table parent
(
p_id number primary key,
p_val varchar2(30)
);
create table child
(
c_id number,
p_id number,
c_val varchar2(30),
constraint fk_child foreign key (p_id) references parent(p_id)
);
create index idx_child on child(p_id);
insert into parent
select object_id, object_name
from all_objects
where rownum <= 500
;
insert into child
select o.object_id, p_id, object_name
from all_objects o, parent p
where rownum <= 50000
;
commit;
/*******************************************************/
DBO@homedb> select *
2 from v$version;
BANNER
------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
DBO@homedb> delete child
2 where rownum < 2;
1 row deleted.
DBO@homedb> select distinct object_name, username, sid, locked_mode
2 from v$locked_object l,
3 dba_objects o,
4 v$session s
5 where o.object_id = l.object_id
6 and l.session_id = s.sid;
OBJECT_NAME USERNAME SID LOCKED_MODE
------------- ------------- -----------
PARENT PFSDADBO 4858 2
CHILD PFSDADBO 4858 3
DBO@homedb> rollback;
Rollback complete.
DBO@homedb> alter table child disable constraint fk_child;
Table altered.
DBO@homedb> delete child
2 where rownum < 2;
1 row deleted.
DBO@homedb> select distinct object_name, username, sid, locked_mode
2 from v$locked_object l,
3 dba_objects o,
4 v$session s
5 where o.object_id = l.object_id
6 and l.session_id = s.sid;
OBJECT_NAME USERNAME SID LOCKED_MODE
------------------------- -----------
CHILD PFSDADBO 4858 3
August 28, 2013 - 6:18 pm UTC
it is a ddl parse lock, it'll prevent ddl on the parent table. you cannot alter that table ddl-wise while there is an outstanding transaction reliant on it.