Deferrable constraints in Oracle 11gR2 may lead to corrupted data

Mar 15, 2010 / By Alex Fatkulin

Tags: , ,

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.

19 Responses to “Deferrable constraints in Oracle 11gR2 may lead to corrupted data”

  • Eric Grancher says:

    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.

  • Dan Norris says:

    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.

  • Dan Norris says:

    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.

  • Mohammed Al-Saadi says:

    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 [...]

  • Pythian Group: Log Buffer #183, a Carnival of the Vanities for DBAs | Weez.com says:

    [...] 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 [...]

  • Vishalaksha says:

    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 [...]

  • Aditya says:

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

    -Aditya

  • Alex Fatkulin says:

    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.

  • Chris says:

    Hi Alex,

    Still an issue in 11.2.0.3…

    Any joy with the Oracle bug/SR?

    Thanks,

    Chris.

  • Sreenivas says:

    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?

  • Sreenivas says:

    Never Mind! I was able to reproduce it.

  • 3iTeam says:

    Yes its simulating in 11.2.0.1 :)

  • Sunil says:

    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.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>