ORACLE-锁机制

源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1153128>

Locks and Unindexed Foreign Keys

When both of the following conditions are true, the database acquires a full table lock on the child table:

No index exists on the foreign key column of the child table.

A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.

Suppose that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id. Figure 9-3 shows a session modifying the primary key attributes of department 60 in the departments table.

Figure 9-3 Locking Mechanisms with Unindexed Foreign Key

Description of "Figure 9-3 Locking Mechanisms with Unindexed Foreign Key"

In Figure 9-3, the database acquires a full table lock on employees during the primary key modification of department 60. This lock enables other sessions to query but not update the employees table. For example, employee phone numbers cannot be updated. The table lock on employees releases immediately after the primary key modification on the departments table completes. If multiple rows in departments undergo primary key modifications, then a table lock on employees is obtained and released once for each row that is modified in departments.

源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1804873>

其实官方文档解释的已经非常清楚。

1.在外键没有索引的情况下，修改主表的主键（delete or update），会在子表上加表锁。

2.在存在外键索引的情况下，修改主表的主键（delete or update），不会在子表上加表锁，此时允许你在子表上进行dml，实际是锁定在索引的叶子节点上。

所以，实验时，你只需要在主表上进行对主键的操作，然后去看 v$lock视图即可，无须另起一个会话在子表上进行操作。oracle官方文档表达的也是这个意思。仅是在加与不加索引，对主表主键操作的情况下，观察 此时在子表上的锁情况，而没要求你一定要在子表上此时在进行操作。

源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1804873&page=1#pid21588601>