Oracle Server - Row values changing back to old values randomly???

Author

Row values changing back to old values randomly???

2005-05-27, 9:23 am

Hello everyone!

Has anyone every seen the following behavior?

Oracle 9.? running on AIX, no problems here Windows NT machine,
Oracle client installed on the NT machine with ODBC driver v 9.2.0.2

We have set up an ODBC source on the NT machine. An application (app
server) opens a connection pool with e.g. 4 read connections and 4 write
connections.

An application changes e.g. two rows in two different tables and commits.

The changed data is visible in TOAD from another machine.

After about 2 minutes, the data is reverted back to old values without
any activity from the app server whatsoever.

How is this possible? Note, this is definitely not a bug in the app
server code because the app runs without problems on 3 other Oracle
database installations and on many many other installations using
PostgreSQL, SQL Server and DB2. The app doesn't even know what the old
row values were!

Has anyone ever see something like this happening and how to solve it?

Thank you and best regards,

David

DA Morgan

2005-05-27, 11:23 am

DG wrote:
> Hello everyone!
>
> Has anyone every seen the following behavior?
>
> Oracle 9.? running on AIX, no problems here Windows NT machine,
> Oracle client installed on the NT machine with ODBC driver v 9.2.0.2
>
> We have set up an ODBC source on the NT machine. An application (app
> server) opens a connection pool with e.g. 4 read connections and 4 write
> connections.
>
> An application changes e.g. two rows in two different tables and commits.
>
> The changed data is visible in TOAD from another machine.
>
> After about 2 minutes, the data is reverted back to old values without
> any activity from the app server whatsoever.
>
> How is this possible? Note, this is definitely not a bug in the app
> server code because the app runs without problems on 3 other Oracle
> database installations and on many many other installations using
> PostgreSQL, SQL Server and DB2. The app doesn't even know what the old
> row values were!
>
> Has anyone ever see something like this happening and how to solve it?
>
> Thank you and best regards,
>
> David

Never seen it before and it isn't possible: At least not as some
magical Oracle capability.

When the "old" value shows up again in the application what does
SQL*Plus (or TOAD) show at that very same point-in-time? How many
schemas are involved?

What app server? Is it caching anything? What happens if you flush
the cache?
--
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Rauf Sarwar

2005-05-27, 11:23 am

DG wrote:
> Hello everyone!
>
> Has anyone every seen the following behavior?
>
> Oracle 9.? running on AIX, no problems here Windows NT machine,
> Oracle client installed on the NT machine with ODBC driver v 9.2.0.2
>
> We have set up an ODBC source on the NT machine. An application (app
> server) opens a connection pool with e.g. 4 read connections and 4 write
> connections.
>
> An application changes e.g. two rows in two different tables and commits.
>
> The changed data is visible in TOAD from another machine.
>
> After about 2 minutes, the data is reverted back to old values without
> any activity from the app server whatsoever.
>
> How is this possible? Note, this is definitely not a bug in the app
> server code because the app runs without problems on 3 other Oracle
> database installations and on many many other installations using
> PostgreSQL, SQL Server and DB2. The app doesn't even know what the old
> row values were!
>
> Has anyone ever see something like this happening and how to solve it?
>
> Thank you and best regards,
>
> David

And what makes you think Oracle is the culprit here when you yourself
say that the same code works OK in other Oracle installations.

I strongly suspect it's your App Server but to be sure... you can audit
the tables in question and see who actually is doing the changes.

Regards
/Rauf

2005-05-27, 8:23 pm

DA Morgan wrote:

>
> Never seen it before and it isn't possible: At least not as some
> magical Oracle capability.
>
> When the "old" value shows up again in the application what does
> SQL*Plus (or TOAD) show at that very same point-in-time? How many
> schemas are involved?
>
> What app server? Is it caching anything? What happens if you flush
> the cache?

Well it looks like magic only because we don't understand what is
happening ;-)

So let me explain it better. The app server involved, is a Smalltalk
server (proprietary code) with an O-R layer which is mapping objects to
the database.

So all the app server code is in Smalltalk (well that's ALL the code
there is apart from the Oracle and the Windows code). As you can see the
source code is under our control so we know for sure that it doesn't do
any inserts, updates or deletes after doing the transactional commit.
When I say for sure, this is based on a simple fact that there is only
one method in the ODBC API which we use (a method for executing an SQL
statement or query), so it is fairly easy to see what goes into the
database. There is no other chanel to get SQL trough from the app server
to the database.

Also let me point out that there is no manual written SQL code in the
app server. All the SQL code is generated by the O-R layer using meta
data to convert objects to SQL. Objects marked as dirty a processed in a
single point i.e. some kind of transactional manager which will flush
the dirty cached objects into SQL inserts, updates and deletes, execute
statements and upon success it will do COMMIT WORK at the end. After
that the cache is flushed and new data is read into on demand. The new
values also show up in the app server *and* in TOAD.

Regarding your question "what happens when you flush the cache" I must
also point out that with our O-R layer it is not possible to do a dirty
update. The thing is that all updates are done in the following fashion:

UPDATE <aTable> SET <colName>=<newValue> WHERE <pkCol>=<pkColValue> AND
<colName>=<oldValue>

As you can see, if the database row is changed, then UPDATE will return
"zero rows updated" result and afterwards our O-R layer will
automatically rollback the whole transaction.

Also, we checked that after doing COMMIT WORK the changes are visible
from other connections (and from TOAD too). Then after about 2 minutes
all the values are changed back to old values. There are no triggers on
the database and nothing that could otherwise change the data. There are
only a couple of read-only connections which are simultaneously reading
tables (using ODBC).

Let me also mention that we are successfully running the app server on
additional 3 Oracle installations and on at least 100 other SQL server,
DB2 and PostgreSQL installations. Also the O-R layer has been in
production for over 4 years now without any problems whatsoever.

Also, I am not pointing at anything just asking how one could find out
who is changing the data back to previous values.

Thank you and best regards,

David

2005-05-27, 8:23 pm

Rauf Sarwar wrote:

>
>
> And what makes you think Oracle is the culprit here when you yourself
> say that the same code works OK in other Oracle installations.
>
> I strongly suspect it's your App Server but to be sure... you can audit
> the tables in question and see who actually is doing the changes.
>
> Regards
> /Rauf
>

I am not looking for a culpret, just a solution to the problem we are
having.

Regarding auditing, this is also something I would like to do, but the
DB admin who is in charge is not really helping me much in this area. I
couldn't even get a server log of who is changing the tables (I guess
this is possible with Oracle). Where does one start with auditing? Can
this be done from TOAD?

Well, the customer is using Oracle for a long time but has never
installed ODBC before, so I thought there are some well known tricks one
must know...

I can not mathematically prove to you that the problem does not lie in
our app server, but I am also pretty sure that it is not the app server.
But thanks anyway for your answer.

Best regards,

David

DA Morgan

2005-05-27, 8:23 pm

DG wrote:
> DA Morgan wrote:
>
>
>
> Well it looks like magic only because we don't understand what is
> happening ;-)
>
> So let me explain it better. The app server involved, is a Smalltalk
> server (proprietary code) with an O-R layer which is mapping objects to
> the database.
>
> So all the app server code is in Smalltalk (well that's ALL the code
> there is apart from the Oracle and the Windows code). As you can see the
> source code is under our control so we know for sure that it doesn't do
> any inserts, updates or deletes after doing the transactional commit.
> When I say for sure, this is based on a simple fact that there is only
> one method in the ODBC API which we use (a method for executing an SQL
> statement or query), so it is fairly easy to see what goes into the
> database. There is no other chanel to get SQL trough from the app server
> to the database.
>
> Also let me point out that there is no manual written SQL code in the
> app server. All the SQL code is generated by the O-R layer using meta
> data to convert objects to SQL. Objects marked as dirty a processed in a
> single point i.e. some kind of transactional manager which will flush
> the dirty cached objects into SQL inserts, updates and deletes, execute
> statements and upon success it will do COMMIT WORK at the end. After
> that the cache is flushed and new data is read into on demand. The new
> values also show up in the app server *and* in TOAD.
>
> Regarding your question "what happens when you flush the cache" I must
> also point out that with our O-R layer it is not possible to do a dirty
> update. The thing is that all updates are done in the following fashion:
>
> UPDATE <aTable> SET <colName>=<newValue> WHERE <pkCol>=<pkColValue> AND
> <colName>=<oldValue>
>
> As you can see, if the database row is changed, then UPDATE will return
> "zero rows updated" result and afterwards our O-R layer will
> automatically rollback the whole transaction.
>
> Also, we checked that after doing COMMIT WORK the changes are visible
> from other connections (and from TOAD too). Then after about 2 minutes
> all the values are changed back to old values. There are no triggers on
> the database and nothing that could otherwise change the data. There are
> only a couple of read-only connections which are simultaneously reading
> tables (using ODBC).
>
> Let me also mention that we are successfully running the app server on
> additional 3 Oracle installations and on at least 100 other SQL server,
> DB2 and PostgreSQL installations. Also the O-R layer has been in
> production for over 4 years now without any problems whatsoever.
>
> Also, I am not pointing at anything just asking how one could find out
> who is changing the data back to previous values.
>
> Thank you and best regards,
>
> David

Not buying it. Here's how I'd test.

-- in SQL*Plus
SELECT * FROM t;

-- duplicate in application

-- SQL*Plus
UPDATE t SET some_column = some_value WHERE < some_condition_is_tr
ue>;

COMMIT;

SELECT * FROM t;

-- wait 2 minutes
SELECT * FROM t;

-- in application duplicate the query

If the results are different, which seems to be what you are indicating.
Flush the cache on the app server. Requery.

You still didn't address the question BTW of schemas. Are you absolutely
sure they are looking at the same table in the same schema on the same
server?
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

DA Morgan

2005-05-27, 8:23 pm

DG wrote:
> Rauf Sarwar wrote:
>
>
> I am not looking for a culpret, just a solution to the problem we are
> having.

If you don't have culprit you'll not have a solution.

If the DBA won't cooperate then find something else to do with your time.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

2005-05-27, 8:23 pm

DA Morgan wrote:

>
>
> If you don't have culprit you'll not have a solution.
>
> If the DBA won't cooperate then find something else to do with your time.

Yes, that's probably what I will do. I will install good old PostgreSQL
and forget about Oracle.

Thanks for your attention,

David

2005-05-27, 8:23 pm

DA Morgan wrote:

>
>
> So because someone volunteering their time for free won't do what your
> DBA is paid to do you are going to change RDBMS products. Do you bring
> the same level of professionalism to your other endeavours?
>
> The fact that your DBA doesn't consider it worth his or her time gives
> a good indication of the importance of what you are doing. The fact that
> you can't talk to your own management gives a pretty good indication of
> the importance of what you are doing. If they won't give you the time of
> day why should we? But do remember that PostgreSQL is not Sarbanes-Oxley
> compiant so use it with care. Otherwise the Feds may have a few questions.

Ok, can I first suggest that we cut the buying/selling/professionalism
stuff and stay on the technical side because you got the situation
totally wrong.

<situation>
First it's the customer's DBA who just went earlier home on Friday, but
his financial department wants to run our product on Monday. So, because
the customer's DBA wont support us on *his* DB, our options are limited
to either do his work or to install another DB for ourselves. So that's
the situation. And we are not in the U.S. so let's forget about the Feds
& Co. - I don't even know what Serbian-Ox is, and neither do I care -
the DB just has to run as expected, and the mentioned option has proven
itself as reliable numerous times.
</situation>

So back to the technical side. I though about transaction isolation
levels. Is it possible that the database is configured to "READ
UNCOMMITED" isolation level by default? This would mean that the TOAD is
really showing data which hasn't been committed yet. Later the session
disconnects and the data is converted back.

Thanks, David

Sybrand Bakker

2005-05-27, 8:23 pm

On Fri, 27 May 2005 23:44:09 +0200, DG <info@no-spam-e-resitve.com>
wrote:

>So back to the technical side. I though about transaction isolation
>levels. Is it possible that the database is configured to "READ
>UNCOMMITED" isolation level by default? This would mean that the TOAD is
>really showing data which hasn't been committed yet. Later the session
>disconnects and the data is converted back.

There is no such thing as read uncommitted isolation level.
Apart from that, an exit from an Oracle session issues an implicit
commit.
You would need logminer to establish what is going on the database
side, another option would be running statspack or even
utlbstat/utlestat. Both statspack and utlbstat/utlestat establish the
number of rollbacks issued. However, rollbacks are not being issued by
Oracle ever.
Good luck with your attempt hunting phantoms.

--
Sybrand Bakker, Senior Oracle DBA

DA Morgan

2005-05-27, 8:23 pm

DG wrote:
> DA Morgan wrote:
>
>
>
> Ok, can I first suggest that we cut the buying/selling/professionalism
> stuff and stay on the technical side because you got the situation
> totally wrong.
>
> <situation>
> First it's the customer's DBA who just went earlier home on Friday, but
> his financial department wants to run our product on Monday. So, because
> the customer's DBA wont support us on *his* DB, our options are limited
> to either do his work or to install another DB for ourselves. So that's
> the situation. And we are not in the U.S. so let's forget about the Feds
> & Co. - I don't even know what Serbian-Ox is, and neither do I care -
> the DB just has to run as expected, and the mentioned option has proven
> itself as reliable numerous times.
> </situation>
>
> So back to the technical side. I though about transaction isolation
> levels. Is it possible that the database is configured to "READ
> UNCOMMITED" isolation level by default? This would mean that the TOAD is
> really showing data which hasn't been committed yet. Later the session
> disconnects and the data is converted back.
>
> Thanks, David

There are truly only three possibilities.

1. There is no commit and you don't realize it.

2. The commit is failing and your understanding of the Oracle
transaction model is so weak you don't understand how to interpret
what you are seeing or your code doesn't trap for the failure.

3. The commit is succeeding and you have a problem with caching
on the application server or in your application.

I don't mean to be negative, irritating and/or rude but truly your
comment above indicates a complete lack of knowledge of the Oracle
transaction model so any one of the three is possible.

What is absolutely impossible is that a commit is succeeding and
the change is being rolled back by the database on its own.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Joel Garry

2005-05-27, 8:23 pm

DA Morgan wrote:
> DG wrote:
>
> There are truly only three possibilities.
>
> 1. There is no commit and you don't realize it.
>
> 2. The commit is failing and your understanding of the Oracle
> transaction model is so weak you don't understand how to interpret
> what you are seeing or your code doesn't trap for the failure.
>
> 3. The commit is succeeding and you have a problem with caching
> on the application server or in your application.

4. The TOAD is looking from another session that started a transaction
before the app server started it's update transaction, after looking
from a session that started after the update transaction committed.

But I think 2 is pretty likely.

>
> I don't mean to be negative, irritating and/or rude but truly your
> comment above indicates a complete lack of knowledge of the Oracle
> transaction model so any one of the three is possible.
>
> What is absolutely impossible is that a commit is succeeding and
> the change is being rolled back by the database on its own.

flashhhhbaaaaackkkkk
.

> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)

jg
--
@home.com is bogus.
"PLEASE DO NOT REPLY TO THIS E-MAIL. This e-mail address is not
monitored." - from replies@oracle-mail.com

DA Morgan

2005-05-27, 8:23 pm

Joel Garry wrote:

>
>
> flashhhhbaaaaackkkkk
.

I considered that. But to flashback you need a couple of things that
this user clearly doesn't have.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

2005-05-30, 8:23 pm

Joel Garry wrote:
>
> DA Morgan wrote:
>
>
>
> 4. The TOAD is looking from another session that started a transaction
> before the app server started it's update transaction, after looking
> from a session that started after the update transaction committed.
>
> But I think 2 is pretty likely.
>

Well, it's none of the above. I've solved the problem. There is some
obscure bug (or different behaviour at least) on the part of the Oracle
ODBC driver.

I've used the following ODBC lib function for committing transactions:

---
SQLRETURN SQLEndTran(
SQLSMALLINT HandleType,
SQLHANDLE Handle,
SQLSMALLINT CompletionType);

HandleType is SQL_HANDLE_DBC
CompletionType is SQL_COMMIT
---

Then, I've changed the Smalltalk commit method implementation to use:

---
SQLRETURN SQLExecDirect(
SQLHSTMT StatementHandle,
SQLCHAR * StatementText,
SQLINTEGER TextLength);

and execute SQL statement "COMMIT".
---

And, with this change it works as one would expect.

So, regarding the possibilities above:

1. It can't be. The change was visible in TOAD (another connection) for
some time. So it must have been committed and then reverted by the ODBC
driver code.

2. I can not comment on this. I have 10 years experience in the RDB and
OODB technology, even implemented an OODB using MVCC (like the Oracle is
using). I guess that should give me enough understanding of the existing
transactional models.

3. No chance. The app works without problems with all other databases.
But there could be a caching problem in the ODBC driver.

4. No, this is not the case here.

If someone else ever encounters similar problem with ODBC here is the
solution. Don't use SQLEndTran, execute a direct COMMIT instead.

Best regards,

David

DA Morgan

2005-05-30, 8:23 pm

DG wrote:

> Well, it's none of the above. I've solved the problem. There is some
> obscure bug (or different behaviour at least) on the part of the Oracle
> ODBC driver.
>
> I've used the following ODBC lib function for committing transactions:
>
> ---
> SQLRETURN SQLEndTran(
> SQLSMALLINT HandleType,
> SQLHANDLE Handle,
> SQLSMALLINT CompletionType);
>
> HandleType is SQL_HANDLE_DBC
> CompletionType is SQL_COMMIT
> ---
>
> Then, I've changed the Smalltalk commit method implementation to use:
>
> ---
> SQLRETURN SQLExecDirect(
> SQLHSTMT StatementHandle,
> SQLCHAR * StatementText,
> SQLINTEGER TextLength);
>
> and execute SQL statement "COMMIT".
> ---
>
> And, with this change it works as one would expect.
>
> So, regarding the possibilities above:
>
> 1. It can't be. The change was visible in TOAD (another connection) for
> some time. So it must have been committed and then reverted by the ODBC
> driver code.
>
> 2. I can not comment on this. I have 10 years experience in the RDB and
> OODB technology, even implemented an OODB using MVCC (like the Oracle is
> using). I guess that should give me enough understanding of the existing
> transactional models.
>
> 3. No chance. The app works without problems with all other databases.
> But there could be a caching problem in the ODBC driver.
>
> 4. No, this is not the case here.
>
> If someone else ever encounters similar problem with ODBC here is the
> solution. Don't use SQLEndTran, execute a direct COMMIT instead.
>
> Best regards,
>
> David

You are incorrect. ODBC drivers are irrelevant to whether the database
does or does not commit a transaction. If it does then it has done so.
If it doesn't then it hasn't. Your changing the driver may have caused
something that didn't commit to commit. But it did not change the
behavior of a committed transactions.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Randy Harris

2005-05-30, 8:23 pm

"DG" <info@no-spam-e-resitve.com> wrote in message
news:wmKme.12391$F6.2626011@news.siol.net...

[snip...]

is[color=darkred]
>
>
> Well, it's none of the above. I've solved the problem. There is some
> obscure bug (or different behaviour at least) on the part of the Oracle
> ODBC driver.
>
> I've used the following ODBC lib function for committing transactions:
>
> ---
> SQLRETURN SQLEndTran(
> SQLSMALLINT HandleType,
> SQLHANDLE Handle,
> SQLSMALLINT CompletionType);
>
> HandleType is SQL_HANDLE_DBC
> CompletionType is SQL_COMMIT
> ---
>
> Then, I've changed the Smalltalk commit method implementation to use:
>
> ---
> SQLRETURN SQLExecDirect(
> SQLHSTMT StatementHandle,
> SQLCHAR * StatementText,
> SQLINTEGER TextLength);
>
> and execute SQL statement "COMMIT".
> ---
>
> And, with this change it works as one would expect.
>
> So, regarding the possibilities above:
>
> 1. It can't be. The change was visible in TOAD (another connection) for
> some time. So it must have been committed and then reverted by the ODBC
> driver code.
>
> 2. I can not comment on this. I have 10 years experience in the RDB and
> OODB technology, even implemented an OODB using MVCC (like the Oracle is
> using). I guess that should give me enough understanding of the existing
> transactional models.
>
> 3. No chance. The app works without problems with all other databases.
> But there could be a caching problem in the ODBC driver.
>
> 4. No, this is not the case here.
>
> If someone else ever encounters similar problem with ODBC here is the
> solution. Don't use SQLEndTran, execute a direct COMMIT instead.
>
> Best regards,
>
> David

David, I've been reading this thread with interest. I read this NG to learn
and find there is a much value in learning from the experiences of others.
Thanks for posting the solution you've uncovered, it adds to the value for
learners, such as myself.

I do quite a lot of work with ODBC myself, mostly using ADO. From what I
know of it, I find it difficult to accept that the driver is somehow caching
that data, then triggering a revert. Rolling the transaction back after
some sort of timeout seems much more plausible, though this explanation is,
of course, contradicted by what you see with TOAD.

You've obviously found a "fix" for the problem and I have no more reasonable
explanation to offer. I still, however, find this quite puzzling.

2005-05-31, 7:23 am

Randy Harris wrote:
> "DG" <info@no-spam-e-resitve.com> wrote in message
> news:wmKme.12391$F6.2626011@news.siol.net...
>
> [snip...]
>
>
>
> is
>
>
>
> David, I've been reading this thread with interest. I read this NG to learn
> and find there is a much value in learning from the experiences of others.
> Thanks for posting the solution you've uncovered, it adds to the value for
> learners, such as myself.
>
> I do quite a lot of work with ODBC myself, mostly using ADO. From what I
> know of it, I find it difficult to accept that the driver is somehow caching
> that data, then triggering a revert. Rolling the transaction back after
> some sort of timeout seems much more plausible, though this explanation is,
> of course, contradicted by what you see with TOAD.
>
> You've obviously found a "fix" for the problem and I have no more reasonable
> explanation to offer. I still, however, find this quite puzzling.
>

Hello, sorry for bugging you all. After a joint session with the
customer's DBA we finally got to looking at the logs. And it turned out
that there was a third process which was replicating tables from another
view and which was periodically overwriting all the data the users
stored in our web app. So, it was only a coincidence that the
replication didn't happen when we installed our "fix".

Best regards,

David

Ed Prochak

2005-05-31, 1:23 pm

Thanks David for closing the loop and showing us what the real cause
was.

perhaps the lesson is
before accepting the improbable as true, make sure you have eliminated
ALL other possibilities!

ed

Joel Garry

2005-05-31, 8:23 pm

DG wrote:

>
>
> Hello, sorry for bugging you all. After a joint session with the
> customer's DBA we finally got to looking at the logs. And it turned out
> that there was a third process which was replicating tables from another
> view and which was periodically overwriting all the data the users
> stored in our web app. So, it was only a coincidence that the
> replication didn't happen when we installed our "fix".

LOL!

5. Something so stupid no one would think of it.

Thanks for the closure.

jg
--
@home.com is bogus.
"Think impure thoughts." - Carolyn Hax

Sponsored Links