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?

Actions

Information

8 responses

21092010

Gary(19:46:34) :

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.

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.

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.

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.

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).

> 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)

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: