Oracle Transactions doesn't work as expected in some client machines in a .net windows application

Question

Hi.

I have an windows application in VB.NET (2.0) and Oracle Database, the connections are handled by System.Data.OracleClient. My oracle client is 10g. In the server side, the AutoCommit is off. The application uses transtactions (isolationLevel: ReadCommitted) for certain operations.

The problem is that in some machines (and only in some of them) transactions don´t work as expected. The problems I have detected are the following:

1. Rollback doesn´t work (they are used inside the catch of the exception, as typically). All changes made before are commited.

2. From my app, I call a stored procedure that uses a select the clause "for update no wait". The excepted behavior was locking the selected rows, but it doesn´t happen.

Apparently, client-side configuration is the same in all the machines, but in some of them transactions have this behavior. Obviously I´m missing something...

Any help?

Thanks in advance

Gus.

Answer 1

Not sure what you're expecting from us here. You say on the one hand your application works for some client machines but not for others. But on the other hand "client-side configuration is the same in all the machines".

Clearly one of those statements is wrong: either your application can fail for any client machine but just haven't yet, or else there is something different about the problematic clients. Remember that it may not be something to do with the client machines themselves but in their network connections. For instance, do they use a different firewall?

Either way it's unlikely we can solve your problem for you. You need to switch on (or put in) some tracing to understand when and where in your app these failures are occurring. Then you have a chance of understanding the why part of the problem (or at least giving us enough info to explain it to you).

Answer 2

More details on the configuration are needed.

The 'symptoms' suggest commits are happening when you don't expect them (ie rollback not working and locks being released).

Is there any form of connection pooling in place ? I'd look at V$SESSION. If every end user has its own session there, probably not. Can end users fire up multiple versions of the application on their PC, and does each of those get its own session ? Can they spawn off separate Windows within the application, and do these get their own session ?

It may be that all the clients are configured the same way, but people are using them differently (eg Fred runs reports in the background, while Wilma is reassigning jobs at the same time as creating new ones).

I'd also look for commits in the code. For this, I'd probably run traces of the operation. Keep an eye on v$transaction to see when the transactions start.