Deferrable constraints in Oracle 11gR2 may lead to corrupted data

Posted in: Technical Track

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.

email

Interested in working with Alex? Schedule a tech call.

19 Comments. Leave new

Eric Grancher
March 15, 2010 5:42 pm

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
March 15, 2010 8:10 pm

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
March 16, 2010 4:43 am

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

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
March 16, 2010 8:44 am

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

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
March 16, 2010 5:14 pm

You are awesome Alex…………:-)

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

have a nice day

Reply
Log Buffer #183, a Carnival of the Vanities for DBAs | The Pythian Blog
March 19, 2010 3:07 pm

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

Reply
Pythian Group: Log Buffer #183, a Carnival of the Vanities for DBAs | Weez.com
March 20, 2010 3:08 pm

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

Reply

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

Reply

Vishalaksha,

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

Reply
Blogroll Report 12/03/2010 – 19/03/2010 « Coskan’s Approach to Oracle
April 29, 2010 1:14 pm

[…] 7-Logical corruption bug with deferrable constraints Alex Fatkulin- Deferrable constraints in Oracle 11gR2 may lead to logically corrupted data […]

Reply

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

-Aditya

Reply

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

Hi Alex,

Still an issue in 11.2.0.3…

Any joy with the Oracle bug/SR?

Thanks,

Chris.

Reply

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

Never Mind! I was able to reproduce it.

Reply

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.

Reply

This bug is till in Oracle 12C (12.1.0.1.0)

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *