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.