Joel Garry wrote:
>
> DA Morgan wrote:
>
>>DG wrote:
>>
>>>DA Morgan wrote:
>>>
>>>
>>>>>Yes, that's probably what I will do. I will install good old
>>>>>PostgreSQL and forget about Oracle.
>>>>>
>>>>>Thanks for your attention,
>>>>>
>>>>>David
>>>>
>>>>
>>>>
>>>>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
>>
>>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
Received on Mon May 30 2005 - 15:05:51 CDT
