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

"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. Received on Mon May 30 2005 - 19:03:59 CDT