SQL Challenge – Submit Update Statements, Updated Values are Reversed on Successful Commit

21 09 2010

September 21, 2010

Is it possible to construct an Oracle Database test case such that:

  1. An UPDATE statement is issued to change a DATE column value from NULL to the current date (SYSDATE) for two rows.
  2. The data source is queried and the result set returned by the database shows that the DATE column is set to the current date for the two rows.
  3. A COMMIT is successfully executed.
  4. The data source is queried and the result set returned by the database shows that the DATE column is again NULL for the two rows.


For example, something like this:

  C2 DATE,
  C3 DATE,
    NVL(C2,TO_DATE('01-JAN-2000','DD-MON-YYYY')) < NVL(C3,TO_DATE('01-JAN-2000','DD-MON-YYYY')))



There are now three rows in the table T1:


 C1 C2        C3
--- --------- ---------
  1           31-DEC-00
  2           31-DEC-00
  3           31-DEC-00

We are able to update the column C2 to the current date (SYSDATE) with the following SQL statement:

  C1 IN (1,2);

2 rows updated.

The update was successful, and we are able to confirm that the update was successful:


C1 C2        C3
-- --------- ---------
 1 21-SEP-10 31-DEC-00
 2 21-SEP-10 31-DEC-00
 3           31-DEC-00

Now we will issue a COMMIT:


Then query the table again to find the original value of column C2 was restored:


 C1 C2        C3
--- --------- ---------
  1           31-DEC-00
  2           31-DEC-00
  3           31-DEC-00

The only catch is that “Commit complete.” must appear after the COMMIT, rather than something like the following:

ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (TESTUSER.CHECK_DATE) violated

Be creative.  How is it possible to update a set of rows, select the rows to prove that the rows were updated, COMMIT, receive a confirmation that the COMMIT was successful, and then find that the original values were restored?  In case you are wondering, this OTN thread was the inspiration for this blog article (but don’t look at forum thread yet).

Is the data source that makes this possible a table, or is it something else?  Is there possibly a corrupt index involved?  Is there an ON COMMIT trigger involved (as far as I am aware, there is no such trigger available on Oracle Database).  VPD (virtual private database) tricks?  View tricks?  Magic?



8 responses

21 09 2010

A DBMS_JOB submitted as part of a trigger could do it, but you may need a couple of seconds between the commit and the query showing the reverted data. That could be ‘live’ if the date was there for some form of queuing / propagation, and the reset was part of a “Done and cleared”.

You can do all sorts of magic with a view, a PL/SQL function returning a pipelined table and an INSTEAD OF trigger. The INSTEAD OF could store the values in session state and the PL/SQL function retrieve them from session state. But I can’t imagine a use case for that.

If I saw it ‘live’, I’d be thinking about a connection pool / caching issue where the second session was retrieving old data (possibly at another timetamp, if it was in the middle of a SERIALIZABLE transaction). Or maybe a race condition where two concurrent database sessions were updating the records, possibly as a result of the same end-user action. A problem in the application layer where an ORM or similar has two resultsets and doesn’t realize they represent the same database data and writes updates to both, overwriting one of them.

Now to check the thread….
I’d also look at the ORA_ROWSCN to see if this was a change that was rolled back to an old state or another change on top.

21 09 2010

Asynchronous CDC subscription that updates the source table?

22 09 2010

Now, this may sound stupid but this can be achieved by sandwiching the update of the table (and commiting the changes) to reset dates from another session, between COMMIT and subsequent SELECT in the original session.

22 09 2010

I think the above comments miss the point that the commit completed successfully. I can’t understand how this is possible because the contraint should have caused the commit to fail. It sort of indicates that the values were reset to null prior to the commit.

22 09 2010
Jakub Wartak

My guess is: constraint is DISABLEd NOVALIDATEd ? 🙂

22 09 2010

One other posibility is create a writable materialized view, you can then update it as much as you like but your changes will get wiped out on a refresh or you could write an application using ole automation and create an event subscription using orasubscription.Register, do it right (or wrong) and when you update a row an event fires in the application which scould then delete the rows, or update them back to null.

22 09 2010
Charles Hooper

Quite a nice collection of interesting ideas regarding what might be happening.

Gary, good ideas regarding concurrency issues. What if this “table” is actually a view that shows only the most recent non-processed records and the ID column (C1 in my test case) is actually just a row number that is updated to restart at 1 every time the DATE column (C2) is entered. The view might have a restriction of WHERE C2 IS NULL – of course that restriction can also be applied to the table using VPD. I started building a view demonstration, but views using ROWNUM and ROW_NUMBER are non-updateable. Your other ideas are interesting – things that I had not considered. Any chance that you could put together a test case or two that demonstrates how one or more of your potential problems may occur (might be a good topic for a blog article)?

fedak, I am not sure that I understand your suggestion – I think that I need to dig out the Oracle manuals.

Narendra, true that we do not know what else is happening in the database. It might also be interesting to know if database links are established between this database and another.

Lakmal, I was *guessing* in my test case that deferred constraints might be part of the configuration in the original poster’s database. What if there was a way to suppress the “ORA-02290: check constraint” error and instead output “Commit complete.” to the SQL*Plus session?

Jakub, can you put together a test case that demonstrates your suggestion?

Chris_c, it is interesting that you mentioned materialized views as a possibility – that was the second test case that I was working on for this blog article. I encountered SQL syntax problems and just decided that someone else would probably find a way to produce a test case showing that type of problem (materialized view set to refresh on COMMIT). Would you be able to post a test case?


What about a corrupt index? Any chance that this could be an external table (need a test case if you say yes).

22 09 2010

> fedak, I am not sure that I understand your suggestion – I think that I need to dig out the Oracle manuals.

I was trying to think of things that take effect on commit and the 3 things that came to mind were the clearing of a global temp table, the refresh on a materialized view, and CDC publish.
(Oracle doesn’t have commit triggers- which is what you really want here)

Couldn’t think of any way to get your desired effect with the temp table or the view, though there may be a way. (Both seemingly involved writing to the view)

An asynchronous CDC publication *should* give you the commit trigger effect needed here.
You would however, be counting on the background CDC processes to handle the publish/subscribe on that change
(Which isn’t that conceptually different than the dbms_job solution mentioned above)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: