DG wrote:
> 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
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_true>;
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_at_x.washington.edu
(replace x with u to respond)
Received on Fri May 27 2005 - 15:33:19 CDT
