Re: Row values changing back to old values randomly???

From: DG <info_at_no-spam-e-resitve.com>
Date: Tue, 31 May 2005 14:11:51 +0200
Message-ID: <d7hka802eeg@enews4.newsguy.com>

Randy Harris wrote:

> "DG" <info_at_no-spam-e-resitve.com> wrote in message
> news:wmKme.12391$F6.2626011_at_news.siol.net...
> 
> [snip...]
> 
> 


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

>>>

>>>

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

> 
> 
> 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 Received on Tue May 31 2005 - 07:11:51 CDT