Re: committed data being rolled back???

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. Received on Tue Dec 17 2002 - 12:19:33 CST