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.
22 Comments. Leave new
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.
Hi Alex,
Still an issue in 11.2.0.3…
Any joy with the Oracle bug/SR?
Thanks,
Chris.
Hi,
I am not able to reproduce it. However i am testing this on 11.2.0.1.0. can any one able to reproduce it now?
Never Mind! I was able to reproduce it.
It is reproducible on 10.2 as well and does not seems version specific.
This happens because deferrable PRIMARY KEY/UNIQUE KEY constraints creates non-unique index to support constraint and hence the result.
This could be subject to debate for bug consideration.
This bug is till in Oracle 12C (12.1.0.1.0)
As Sunil said the reason of this bug, “deferrable PRIMARY KEY/UNIQUE KEY constraints creates non-unique index to support constraint and hence the result.” , This bug doesn’t happen for Check and Foreign key constraints .
hi all,
alex, thanks for this great post.
issue is the same in
11.2.0.4.160119
and
12.1.0.2.160119
@alex
any updates in the SR?
I think the reason of this bug is locking the entry on index.
Second session’s update -> lock the entry of value=2
First session’s update -> lock the entry of value=1, block the entry of value=2.
Entry = [1][2->3]
After commit first session, release blocking of second session.
After commit second session,
Entry = [1->3][2->3]