Re: deadlock
Date: 1996/03/08
Message-ID: <4hq26k$rat_at_inet-nntp-gw-1.us.oracle.com>#1/1
"Shitole Sanjay (OG)" <sanjay> wrote:
>Oracle does acquire table level locks when tables involving dml operations
>are defined using foregin key integrity constraints.
>For example:
> create table dept(dept_no number(5) primary key,
> dept_name varchar2(30));
> create table emp(emp_no number(5) primary key,
> dept_no number(5),
> constraint dept_fk foreign key (dept_no) references dept
> on delete cascade);
> (Assumption: No index on dept_no column of emp table)
>Now perform following operations
>1. User A : Delete dept where dept_no = 100;
>2. User B : Update dept set dept_name = 'SALES' where dept_no = 200;
>Result: User B has to wait for lock acquired by User B.
Yes it does if and only if there is not an index on the child table. (chapter 6 of the server application developers guide goes into the details). If you remove the Assumption that No Index on the dept_no column of the emp table, then the shared table lock on the parent table is not present.
In general, it makes sense to index your foreign keys. For example in the above the cascade delete from DEPT to EMP would force full scans of the emp table. For most reporting purposes I have seen, you typically start at the parent table and find the children (eg: For Dept 10, Show the Employees). If you don't have the deptno field in EMP indexed, this will always cause the EMP table to be full scanned once again. In many cases, the foreign key is part of the child primary key (and if the primary key is created in the correct order, the foreign key will already be indexed).
You should also be aware that the implementation changes in 7.2 and above. For example, in 7.2 the above example without the index does NOT block. In 7.2 (from chapter 6 of the appl. developers guide for 7.2)
<quote>
... illustrates the locking mechanisms used by Oracle when no index is defined
on the foreign key and when rows are being updated or deleted in the parent
table. Inserts into the parent table do not require any locks on the child
table.
Notice that a share lock of the entire child table is required until the transaction containing the DELETE statement for the parent table is committed. If the foreign key specifies ON DELETE CASCADE, the DELETE statement results in a table-level share-subexclusive lock on the child table. A share lock of the entire child table is also required for an UPDATE statement on the parent table that affects any columns referenced by the child table. Share locks allow reading only; therefore, no INSERT, UPDATE, or DELETE statements can be issued on the child table until the transaction containing the UPDATE or DELETE is committed. Queries are allowed on the child table.
This situation is tolerable if updates and deletes can be avoided on the parent.
INSERT, UPDATE, and DELETE statements on the child table do not acquire any locks on the parent table; although INSERT and UPDATE statements will wait for a row-lock on the index of the parent table to clear.
</quote>
So in 7.2 with an un-indexed foreign key -the SHARE table lock is moved from the parent to the child.... -it only affects updates and deletes (inserts do not SHARE lock the table)
In 7.1 and before
- the SHARE table lock is on the parent table
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
opinions and statements are mine and do not necessarily reflect the opinions of Oracle Corporation. Received on Fri Mar 08 1996 - 00:00:00 CET