Deferrable constraints in Oracle 11gR2 may lead to logically 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.
