Is Catching ORA-02292 A Good Solution? It Depends…

I’ve recently realized that some technique that I use sometimes is subject to an important restriction which I used to ignore.

The Requirement

We have two tables (let’s call them PARENTS and CHILDREN) with a foreign key between them (CHILDREN references PARENTS).
We need to write a procedure that deletes a given child, and if its parent has no other children the parent should be deleted as well.

Demo Data

SQL> select * from parents;

        ID
----------
         1
         2

SQL> select * from children;

        ID  PARENT_ID
---------- ----------
       101          1
       201          2
       202          2

The Implementation

A simple solution for the conditional deletion of the parent is to try to delete it.
If this parent has no other children, the DELETE statement will succeed.
If it has other children, the DELETE statement will fail with ORA-2292 (integrity constraint violated – child record found), and we can catch this exception and ignore it.

I like this kind of solutions mainly because Oracle automatically takes care of the necessary serialization of concurrent sessions (trying, in this case, to delete or insert children of the same parent).

SQL> create or replace package demo as
  2      procedure delete_child(i_id in children.id%type);
  3  end demo;
  4  /

Package created.

SQL> create or replace package body demo as
  2      procedure delete_parent_if_it_has_no_children(i_id in parents.id%type) is
  3          e_children_exist exception;
  4          pragma exception_init(e_children_exist, -2292);
  5      begin
  6          delete parents p
  7          where  p.id = i_id;
  8          dbms_output.put_line('parent was deleted successfully');
  9      exception
 10          when e_children_exist then
 11              dbms_output.put_line('parent was not deleted');
 12      end delete_parent_if_it_has_no_children;
 13
 14      procedure delete_child(i_id in children.id%type) is
 15          v_parent_id children.parent_id%type;
 16      begin
 17          delete children c
 18          where  c.id = i_id
 19          returning c.parent_id into v_parent_id;
 20
 21          delete_parent_if_it_has_no_children(v_parent_id);
 22
 23      end delete_child;
 24  end demo;
 25  /

Package body created.

Parent 1 has only one child – 101, so when we delete child 101 its parent is deleted as well:

SQL> exec demo.delete_child(101)
parent was deleted successfully

PL/SQL procedure successfully completed.

Parent 2 has two children – 201 and 202. When we delete one of the children, the parent is not deleted. When we delete the second child, the parent is deleted.

SQL> exec demo.delete_child(201)
parent was not deleted

PL/SQL procedure successfully completed.

SQL> exec demo.delete_child(202)
parent was deleted successfully

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

The Catch

This solution is based on the fact that the foreign key constraint is enforced in the statement level. It means that we can use this solution as long as the foreign key is not deferred. Deferred constraints are enforced at the end of the transaction, and therefore the DELETE PARENTS statement will succeed without raising an exception, even if the deleted parent has children.

I executed the previous example after creating the tables as follows:

SQL> create table parents (
  2    id number not null primary key
  3  );

Table created.

SQL> create table children (
  2    id number not null primary key,
  3    parent_id not null
  4      constraint fk_children_parents
  5        references parents
  6        deferrable initially immediate
  7  );

Table created.

SQL> begin
  2    insert into parents values (1);
  3    insert into parents values (2);
  4
  5    insert into children values (101,1);
  6    insert into children values (201,2);
  7    insert into children values (202,2);
  8
  9    commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Now let’s set the foreign key as deferred, and try to delete only one child of parent 2.

SQL> set constraint fk_children_parents deferred;

Constraint set.

SQL> exec demo.delete_child(201)
parent was deleted successfully

PL/SQL procedure successfully completed.

The parent was deleted successfully, although it still has an existing child.
But when we try to commit the transaction, the foreign key is checked, and the whole transaction is rolled back (including the deletion of the child).

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (TRANZMATE_DEV.FK_CHILDREN_PARENTS) violated - child record found

ENABLE NOVALIDATE – Too Polite?

Onine DDL operations are much more polite than offline DDL operations. They usually wait patiently for transactions that hold resources they need until these transactions end, and they do not block new DML statements.

As I wrote in the past, adding a constraint as Enabled and Validated (which is the default for new constrtaints) is an offline operation, but if we split it into two DDL statements – one for adding the constraint as Enabled and Not Validated and the second for making the constraint Validated – then each of these two separate statements is an online operation.

In this post I’d like to show that the first step – creating the constraint as Enabled and Not Validated – is even “more online” than it seems.

Let’s create some table t and insert one record into it:

One> create table t (
  2    a number,
  3    b number
  4  );

Table created.

One> insert into t(a,b) values (111,-1);

1 row created.

I did not commit or rollback this transaction, so it is still open and it’s locking the table in RX mode.

Now, from another session (note the SQL Prompts “One” and “Two”), I’ll add an Enabled and Not Validated check constraint to the table:

Two> alter table t
  2    add constraint t_b_chk
  3    check (b>0) enable novalidate;

Session Two is blocked now by session One (the wait event is “enq: TX – row lock contention”). Since it is an online operation it just waits, without throwing an ORA-54 error as an offline operation would have done.

But actually, it seems that this wait is unnecessary. The operation has already happened.

Using session One, we can see that the constraint already appears in the data dictionary:

One> select constraint_name,
  2         search_condition,
  3         status,
  4         validated
  5  from user_constraints
  6  where table_name = 'T';
  
CONSTRAINT_NAME SEARCH_CONDITION STATUS     VALIDATED
--------------- ---------------- ---------- -------------
T_B_CHK         B>0              ENABLED    NOT VALIDATED

And if we try now to perform a DML that violates the constraint, we’ll get an error message, because the constraint is already enabled:

One> insert into t(a,b) values (222,-2);
insert into t(a,b) values (222,-2)
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.T_B_CHK) violated

Session Two will be released as soon as session One either commits or rolls back, but it seems that it could have been released before that.
Even if we kill session Two before the transaction in session One ends, it doesn’t really matter, because the constraint has already been created and enabled.

Excessive Locking when Dropping a Table

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.
A quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a disabled foreign key from the table I was trying to drop. The referenced table was locked by other sessions, and therefore the DROP TABLE operation failed.

Even if the foreign key constraint is enabled, there is no good reason in my opinion to lock the referenced table; all the more so if it’s disabled.
There is a workaround (which I think proves my last sentence): it’s possible to drop the constraint first, and then to drop the table. Dropping the constraint does not lock the referenced table.

Here is a simple test I executed in 11.2.0.4, 12.1.0.2 and 12.2.0.1: Continue reading “Excessive Locking when Dropping a Table”

Adding a Unique Constraint in an Online Way

Note: unlike most of my posts, this one assumes using Enterprise Edition

I have a table t and I want to add a unique constraint on one of its columns – c1.

The Offline Way

The straightforward and most simple way to do it is using a single alter table statement:

SQL> alter table t add constraint c1_uk unique (c1);

Table altered.

By default, Oracle creates in this operation a unique constraint (named c1_uk) and a corresponding unique index (named c1_uk as well) that enforces the constraint.
The downside is that this is an offline operation – the table is locked in Share mode.
This is true even if we specify that the creation of the index is online:

SQL> alter table t add constraint c1_uk unique (c1) using index online;

Table altered.

If the table contains many records, the creation of the index may take a significant amount of time, during which the table is locked and DML operations on the table are blocked.

The Online Way

We can create the unique constraint in an online way, by splitting the operation into three steps: Continue reading “Adding a Unique Constraint in an Online Way”

EBR – Part 2: Locking, Blocking and ORA-04068

This is part 2 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In this post we’ll start handling the first type of change request: changing a package body.

Visit the index page for all the parts of the series

The Task

We need to change the implementation of the PEOPLE_DL package; i.e. we need to change the package body.
There are no API changes (the package spec is not changed) and no table changes.
And of course, as we speak about EBR, the upgrade from the base version to the new one should be online.

The Problems

Locking and Blocking

An online upgrade means Continue reading “EBR – Part 2: Locking, Blocking and ORA-04068”

Creating an Index on a Static Table Referenced by an Active Table

We have a parent-child pair of tables with a foreign key constraint between them, and we need to add an index to the parent table, while the application is active.

The parent table is static during the creation of the index (no DML on it), but lots of DML statements are done on the child table. The parent table is relatively big, so the index creation takes a relatively significant time.

Continue reading “Creating an Index on a Static Table Referenced by an Active Table”