Re: committed data being rolled back???

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<VUJL9.1306$4r5.84222169_at_newssvr15.news.prodigy.com>...
> David wrote:
> > Has anyone experienced the following? The steps are:
> >
> > 1) Using Toad, issue an update statement.
> >
> > 2) Using same session in Toad, issue a commit statement.
> >
> > 3) Using a client program, view the updated records in a different
> > session, verifying that the data did get updated and committed.
> >
> > 4) Time passes...
> >
> > 5) Client program no longer shows the data and neither does Toad.
> >
> >
> > Re-issuing the update and commit allows the data to be seen again
> > (unless the phenomenon wipes the data again at some later point).
> >
> > We have seen this happen multiple times with different updates, but
> > can't find a pattern. It does not happen often. Most recently, it
> > happened twice within a few days on the same updated data. The data
> > was updated and committed and then the next day it was gone. It was
> > updated and committed again on a Friday. On Monday, the data was gone
> > again, requiring the update/commit to be issued again.
> >
> > The fact that data can be seen in a different session means the commit
> > had to have taken place. So what is reversing the update statement??
> >
> > This is Toad "6.3.11.1 g" and Oracle 8i.
> Maybe someone has the pre-update values in their Toad session and,
> instead of pressing Shift-F9, they press F9 (which re-executes all sql
> statements in the SQL window). When they exit Toad, it asks if they want
> to commit and they press Yes.
>
> A similar thing can happen in a web app due to the state-less nature of
> http connections (and why most web apps need to save state in cookies or
> session variables). If they don't I could have someone fill out a form
> with the old values in the morning, then sit around and think. Then at
> the end of the day, click the Submit button.
>
> If I have an UPDATE/COMMIT statement in my session and don't realize
> that someone else is also updating the record, I can inadvertently
> re-issue the UPDATE/COMMIT. If I do a SELECT first, then if the data
> changes while I'm "thinking," Oracle will complain when I try to UPDATE.
>
> I could have a bulk loader running periodically that copies data from an
> out-of-date comma-delimited file.
>
> You need to setup some kind of auditing to find out what the real
> problem is.

I'm trying to work with the DBA to do that. I haven't heard back from her yet on whether she's found anything.

The data isn't being updated by anyone else. This isn't a list of values that exists in the SQL statements. It is copying data from an external table and only I have the SQL to do this.

As odd as it sounds, it is either a Toad issue (don't see how it could be), an Oracle issue, or a SAN issue. The hardest part is reproducing it so that we can give it to Oracle.

It is certainly a very bad thing to have happen in a database, but luckily it is only happening on our Test system and isn't happening with all updates.

Thanks for all the replies on this. Received on Sun Dec 29 2002 - 12:52:31 CST