Deferrable constraints in Oracle 11gR2 may lead to corrupted data

Mar 15, 2010 / By Alex Fatkulin

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.

18 comments on “Deferrable constraints in Oracle 11gR2 may lead to corrupted data”

Eric Grancher on March 15, 2010 at 5:42 pm said:

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

Reply ↓
Alex Fatkulin on March 15, 2010 at 8:10 pm said:

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.

Reply ↓
Markus Winand on March 16, 2010 at 4:43 am said:

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.

Reply ↓
Dan Norris on March 16, 2010 at 8:20 am said:

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?

Reply ↓
Alex Fatkulin on March 16, 2010 at 8:44 am said:

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.

Reply ↓
Dan Norris on March 16, 2010 at 9:37 am said:

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.

Reply ↓
Mohammed Al-Saadi on March 16, 2010 at 5:14 pm said:

You are awesome Alex…………:-)

You did very simple test, Then you got very great result…

have a nice day

Reply ↓
Pingback: Log Buffer #183, a Carnival of the Vanities for DBAs | The Pythian Blog
Pingback: Pythian Group: Log Buffer #183, a Carnival of the Vanities for DBAs | Weez.com
Vishalaksha on March 22, 2010 at 11:53 pm said:

Hey I am just curious what happened to oracle SR for this bug?

Reply ↓
Alex Fatkulin on March 23, 2010 at 10:39 am said:

Vishalaksha,

SR has been opened, I’ll post and update here once Oracle opens related bug.

Reply ↓
Pingback: Blogroll Report 12/03/2010 – 19/03/2010 « Coskan’s Approach to Oracle
Aditya on February 7, 2012 at 4:57 am said:

It seems the same issue persists in Oracle 11g Release 11.2.0.2.0.

-Aditya

Reply ↓
Alex Fatkulin on February 7, 2012 at 9:45 am said:

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.

Reply ↓
Chris on January 29, 2013 at 8:20 am said:

Hi Alex,

Still an issue in 11.2.0.3…

Any joy with the Oracle bug/SR?

Thanks,

Chris.

Reply ↓
Sreenivas on February 14, 2013 at 8:13 pm said:

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?

Reply ↓
Sreenivas on February 14, 2013 at 8:18 pm said:

Never Mind! I was able to reproduce it.

Reply ↓
3iTeam on March 18, 2013 at 10:22 am said:

Yes its simulating in 11.2.0.1 :)

Reply ↓