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.
Category: Oracle, Technical Blog
Tags: 11g, Oracle 11g
Alex,
very interesting! have you opened a SR about the issue, can you update the page with the patch number (if/when available)?
many thanks,
Eric
Eric,
I need to take a closer look whether there is a known new issue or a regression against this and if not then I’ll proceed with logging an SR.
That’s a great one!
I was able to reproduce the problem on oracle 10.2 and 10.1. Seems to sit there for a while.
There is no bug because the second statement only updates a single row. Your example could be improved if the two update statements set n to different values. The test above gets the expected result (both rows should be updated to n=3 by the end). What you assume is that the second update has set both of them to be 3 which is incorrect. Try the example with the second update statement like this: update def_bug set n=4 where n<=2;
That should only update one row which it does, as expected. What were you expecting?
Dan,
I have a primary key constraint defined on that column. Validated and enabled.
I would be glad if you can point me into documentation where it says that it is okay to have duplicate values in the primary key column.
Yeah, I wasn’t awake. Of course you’re right–I thought it was just the read-consistency part, but skipped over the two little words “primary key” up there. Sorry, I’ll go back to sleep now. It is a bug and a bad one at that.
Searched for a matching bug and don’t see one immediately, so with your simple test case (as you wrote it, of course), it should be quick and easy for support to open a bug.
You are awesome Alex…………:-)
You did very simple test, Then you got very great result…
have a nice day
[...] with Oracle, Pythian’s own Alex Fatkulin illustrates a bug (?…likely) that could lead to logically corrupted data. Doug Burns provides an update on his [...]
[...] with Oracle, Pythian’s own Alex Fatkulin illustrates a bug (?…likely) that could lead to logically corrupted data. Doug Burns provides an update on his [...]
Hey I am just curious what happened to oracle SR for this bug?
Vishalaksha,
SR has been opened, I’ll post and update here once Oracle opens related bug.
[...] 7-Logical corruption bug with deferrable constraints Alex Fatkulin- Deferrable constraints in Oracle 11gR2 may lead to logically corrupted data [...]
It seems the same issue persists in Oracle 11g Release 11.2.0.2.0.
-Aditya
Aditya,
I filed an SR quite a while ago but it looks like I can’t even find it anymore. That’s strange. I’ll re-test in 11.2.0.3 just in case.