What is the least intrusive locking mechanism I can use to prevent rows from being inserted in an Oracle table?

Question

I have a parent/child relationship defined which, unfortunately, cannot be maintained via a foreign key. Parents and children are stored in the same table. The parent/child relationship is identified by a column "ITEM_ID". A child ITEM_ID consists of its parent ITEM_ID and then what we can effectively think of as the child's unique identifier within that parent item.

I'm implementing a PL/SQL procedure to delete records from this ITEM table. The first part of the procedure checks for the existence of any children; if so, it raises an application error (simulating a foreign key).

I'm wondering how to prevent child records from being inserted by another process between the time the cursor in my delete procedure is populated and locked "FOR UPDATE", and the point at which the parent record is actually deleted.

Do I need to lock this entire table during this process?

Or should I alter the insert procedure to select the parent record "FOR UPDATE"?

UPDATE: I created the example above just to describe the general situation, but to justify my foreign key/constraint issue I'll give my actual/more complicated structure below:

There are several tables in a remote database: COMPANY, BUILDING, FLOOR. In our organization, Floors belong to buildings and Buildings belong to Companies.

The application I'm working on associates roles with employees. The table that ties an Employee (Employee ID) with a Role (Role ID) also has a "Location" column. The location column corresponds to an ID one of the remote database tables, and we identify which table it belongs to based on the Type column in the Role table.

For example, here are a couple records from my table:

Role = Janitor
Type = BUILDING
Location = COMPANY1-BUILDING1
Parent Role = Manager


Role = Manager
Type = COMPANY
Location = COMPANY1
Parent Role = CEO

As you can guess, Floor identifiers have a format of company-building-floor in the remote database table.

Janitor is tied to the BUILDING level, and as such its Location column is a BUILDING identifier (which is actually the company identifier followed by a building identifier in the BUILDING table).

Answer 1

You would need, as you say, to lock the parent record during inserts of children, so that another user cannot delete the parent before you commit.

I'm curious, why do you say this can't be implemented by a foreign key constraint?

Answer 2

Having the deleting process get a plain lock on the parent record won't prevent child records being inserted. Locking the entire table would seriously serialize your application.

So forcing the inserting process to SELECT ... FOR UPDATE on the parent record and the deleting process to lock the parent record is your only option. The main problem with this implemntation is that it is so easy to circumevent. Or to put it another way, every process which interacts with the table is going to have to issue these additional locks.

Answer 3

As APC mentioned, there appears to be a problem with the data model. The right way to model that sort of heirarchical data is:

key_field            [data_type] primary key
parent_key_field     [data_type] null foreign key references key_field

The serialization problem you have here is why such relationships should be mapped with foriegn keys.

That said, if you open a cursor with SELECT FOR UPDATE against your table, it should exclusively lock the rows you're interested in deleting. You can't prevent someone from "referencing" that table in your "foreign key", because ITEM_ID's just another value.

What might work is embedding this:

procedure delete_me (p_item_key)
as
  l_child_count number;
begin
  -- verify the value being deleted exists and has no children as you already do

  delete from tbl a
   where not exists (select null
                       from tbl b
                      where b.item_id = a.item_id)
  if sql%rowcount = 0 then
    -- your delete failed; raise an error.
  end if;
end;

Answer 4

Reinforcing APC's comment about changing every process, here's one of the complications.

prevent child records from being inserted by another process between the time the cursor in my delete procedure is populated and locked "FOR UPDATE", and the point at which the parent record is actually deleted.

Remember that the child record may have already been inserted (but not committed) when you try to lock the parent. Unless the process of inserting the child record also takes out an exclusive lock on the parent (preventing your delete from getting a lock), it won't work. Since only one transaction can have an exclusive lock on a row at a time, this would serialize inserts.

The question you face is whether will you have a bigger impact on your application by serializing inserts under a parent, or by locking an entire table for the duration of a delete transaction. If your deletes are rare and the transaction quick, I would opt for the table lock as the simplest implementation.