The HR. DEPARTMENTS table is the parent of the HR. EMPLOYEES table. The EMPLOYEES. DEPARTMENT_ID column has a foreign key constraint with the ON DELETE CASCADE option that refers to the DEPARTMENTS. DEPARTMENT_ID column. An index exists on the DEPARTMENTS. DEPARTMENT_ID column. A transaction deletes a primary key in the DEPARTMENTS table, which has child rows in the EMPLOYEES table.
Which statement is true?
A. The transaction acquires a table lock only on the DEPARTMENTS table until the transaction is complete.
B. The transaction acquires a table lock on the DEPARTMENTS table. This lock enables other sessions to query but not update the DEPARTMENTS table until the transaction on the DEPARTMENTS table is complete.
C. The transaction acquires a table lock on the EMPLOYEES table. This lock enables other sessions to query but not update the EMPLOYEES table until the transaction on the DEPARTMENTS table is complete.
D. Only the rows that are deleted in the DEPARTMENTS and EMPLOYEES tables are locked until the transactions on the DEPARTMENTS table is complete.
|
Download Printable PDF. VALID exam to help you PASS. |
 |
D is correct
CORRECT ANSWER: A
Reference: https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1351 in section “Locks and Indexed Foreign Keys”
A is correct as “Figure 9-4 Locking Mechanisms with Indexed Foreign Key” and its description indicate that the lock at table level only occurs on the parent table.
Answers B and C are ruled out because they both indicate that “the locking allows query but not update the parent or child tables (DML)”, the reference however states that “A lock on the parent table prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or child table during the primary key modification”.