Pythian Blog: Technical Track

Deferrable constraints in Oracle 11gR2 may lead to corrupted data

I’ve hit a bug in Oracle 11.2.0.1 when working with deferrable constraints which I think is worth sharing as it may have profound consequences under certain scenarios.

Let’s start by creating a simple table with a deferrable primary key:

SQL> create table def_bug(n number primary key deferrable initially deferred);
Table created
SQL> insert into def_bug values (1);
1 row inserted
SQL> insert into def_bug values (2);
1 row inserted
SQL> commit;
Commit complete

You can confirm that the primary key constraint is working fine by trying to insert a duplicate value:

SQL> insert into def_bug values (1);
1 row inserted
SQL> commit;
commit
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SRC.SYS_C004070) violated

So far so good. Open a second session and execute the following update:

SQL> update def_bug set n=3 where n=2;
1 row updated

Do not commit yet and execute in your first session:

SQL> update def_bug set n=3 where n<=2;

The above update will block due to our second session holding a lock on the row where n=2. Now commit your second session…

SQL> update def_bug set n=3 where n=2;
1 row updated
SQL> commit;
Commit complete

…and then commit your first session:

SQL> update def_bug set n=3 where n<=2;
1 row updated
SQL> commit;
Commit complete

Take a look at the data now:

SQL> select * from def_bug;
         N
----------
         3
         3

Ouch! This was certainly unexpected. You can confirm that the primary key is still working by trying to insert a duplicate value again:

SQL> insert into def_bug values (3);
1 row inserted
SQL> commit;
commit
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SRC.SYS_C004070) violated

It certainly looks like the update statement did not take into account deferrable constraint declared on the table during restart caused by the write consistency mechanism.

No Comments Yet

Let us know what you think

Subscribe by email