MS Oracle provider - transaction bug

Mon, 01 Sep 2008 15:01:15 Z

Hello!

We're using the MS provider for Oracle to access the database in our app (System.Data.OracleClient.dll file version: 2.0.50727.3053 (netfxsp.050727-3000)), and since the recent Framework Service Pack that we installed a week or two ago, we've started to experience some strange behavior regarding transactions. Namely, we start a Serializable isolation level transaction, yet anything we do inside it is immediately commited into the DB (we can see it through the Oracle SQL Developer while stepping through our code), and in case of an exception, even though the transaction is neatly rolled back in the code, evrything that got in still stays in the DB. In short, transactions do not work.

After a lengthy struggle trying to discover who to blame, I'm sorry to report, we've determined it's the MS provider for Oracle. For some strange reason the connection's internal "TransactionState" flag changes from "LocalStarted" to "AutoCommit". Curiously, this does not happen the first time you open a transaction - if you wish to recreate this, you have to start and commit a dummy transaction, inside of which you need not do anything to the data in the DB, and then open a new connection and start a second transaction on it - using the debugger you can see that in the second pass the flag state has changed and the erroneous behavior appears.

We've also discovered that adding this line after starting a transaction makes everything work just fine:

connection.GetType().GetProperty("TransactionState", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic).SetValue(connection, 1, null);

(my apologies, I didn't succeed in marking the code block)

So, just manually forcing the flag back to LocalStarted is enough to fix it.

I hope this post will find it's way to the right place, and the people involved will have enough to go on to fix this as soon as possible, since, IMO, this is beyond critical.

Josip Bakić

MS Oracle provider - transaction bug

Wed, 10 Sep 2008 10:15:39 Z

*bump*

nobody finds this interesting?

MS Oracle provider - transaction bug

Thu, 18 Sep 2008 16:34:52 Z

Exactly what we ran into today! Thanks a lot: we'll implement your fix.

Anything from M$ on this?

Found this bugreport that seems very similar:

AutoCommit behavior change in Oracle Transactions in .Net Framework 2.0 SP2 Beta (~.Net Framework 3. SP1 Beta )

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=351746&wa=wsignin1.0

MS Oracle provider - transaction bug

Mon, 22 Sep 2008 10:23:11 Z

Same problem here!!

We are going to try the fix.

Strange that there's no response from MS, seems very critical to me!!

MS Oracle provider - transaction bug

Tue, 30 Sep 2008 13:47:53 Z

Josip Bakić wrote:

*bump*

nobody finds this interesting?

The same problem.

Thanx a lot!

MS Oracle provider - transaction bug

Tue, 02 Dec 2008 15:12:30 Z

We ran into this problem too but in a different way was manefested. We save a Oracle LOB to the database and the provider requires you to go out to oracle and get a temp LOB to update. This was failing saying you have to do this inside of a transaction (which we were) and digging in we found this AutoCommit problem. This is even issued in the Readme on the SP1 notes. It boggles my mind that this was let out into the wild! Any word on the fix???

MS Oracle provider - transaction bug

Thu, 04 Dec 2008 20:54:52 Z

I am assuming everybody in this thread is running Oracle 10g RC2 and above because that is the version that uses System.Transaction code without MSDTC. That said check the first link below to see if the Oracle Services for MTS and related issues is relevant to you.

@navodot

Yes the Microsoft provider runs LOB through transaction but you don't have to use that because Oracle’s ODP.NET uses a location based method that lets you use 2gig of LOB so you need to change your data provider to ODP.NET to mitigate the current issue.

http://www.oracle.com/technology/tech/windows/ora_mts/index.html

http://www.oracle.com/technology/oramag/oracle/05-nov/o65odpnet.html

MS Oracle provider - transaction bug

Fri, 05 Dec 2008 09:42:24 Z

I don't know about the other people here, but we are using Oracle 11.

I don't quite understand what we're supposed to do with the Oracle Services for MTS that you suggested. The problem ocurrs in the simplest applications, which do not use the System.Transactions namespace, and has never happened prior to the last .NET Service Pack. And also, the hack I wrote in the first post fixes it well enough.

The reason I posted this here, aside from helping others with the same problem, was to report the issue to MS in the hope of a patch of some kind being released. Is that going to happen any time soon?

MS Oracle provider - transaction bug

Sat, 06 Dec 2008 15:44:15 Z

My bad if you are not using System.Transaction in your code then that is a bug because you are using atomic unit of work transactions. The Oracle Services for MTS is used by the Oracle client to resolve Microsoft application layer transaction, I remember back in 2005 the Oracle 10g RC2 client can let you skip some options but MTS is required because of the System.Transaction TransactionScope none atomic transactions.

I have read at Oracle .NET forums that 11g new .NET tools includes fixes for this and other bugs if you are not using Vista and Win2008 because both comes with new data access which is not compatible with Oracle Services for MTS.

http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

MS Oracle provider - transaction bug

Mon, 08 Dec 2008 09:16:18 Z

I have no idea what you're talking about, or even what question you're trying to answer here.

MS Oracle provider - transaction bug

Tue, 09 Dec 2008 04:16:34 Z

What part you did not understand? And I am not trying to answer any questions with my previous post but I was covering issues relevant using transaction with Oracle because the .NET transactions and the transactions in the database are not the same.

MS Oracle provider - transaction bug

Tue, 09 Dec 2008 08:43:39 Z

Well, it seems like a simple issue, after updating the .NET Framework with the Service Pack 1 the MS data provider for Oracle stops working properly. It sets the AutoCommit flag on for no apparent reason. It did not do so before the update. We did not change the installed DB version during this time (we've been using the same version since the project started). We tested a lot, and the bug pops up in the simplest "Hello world"-equivalent DB accessing application.

You used the expression "I was covering issues" - you did not try to recreate the bug described here?

And finally, it's easily fixed with the line of code in the first post, which makes it all the more difficult for me to understand why your suggestions are relevant. I can see you have some expertise in the area, and your help is appreciated, but everything indicates we're dealing with a very simple bug, not an issue caused by improper use.

MS Oracle provider - transaction bug

Tue, 09 Dec 2008 12:56:17 Z

Please Microsoft, just FIX this problem.

As stated before: it's even in the Release Notes (http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/dotNet35SP1Readme.htm) under 'General Issues'.

<QUOTE>

2.3.1.4 Changes made in an OracleTransaction are committed even if the transaction is rolled back

Commands executed within the context of an OracleTransaction will not be rolled back when the transaction is rolled back or aborted. This can occur in applications using the managed Oracle provider System.Data.OracleClient.

To resolve this issue:
Disable connection pooling and create a new OracleConnection object for every OracleTransaction.

</QUOTE>

Disabling connection pooling is not an option due to the huge performance degradation that would cause in our apps.

The problem is too big to ignore any longer, the solution is known (thanks Josip!), so please fix this problem.

MS Oracle provider - transaction bug

Tue, 09 Dec 2008 14:38:47 Z

I'm amazed that they have that as the solution to the problem. Luckily we've avoided the problem in production but it affects all our development machines because sp1 fixes a bunch of VS 2008 issues which is why we installed it. Thankfully we detected this unit testing and didn't get nailed. They need to get this fixed ASAP.

MS Oracle provider - transaction bug

Fri, 19 Dec 2008 08:20:11 Z

A fix for the problem is now included in:

Microsoft .NET Framework 3.5 Family Update for Windows XP x86, and Windows Server 2003 x86
http://www.microsoft.com/downloads/details.aspx?FamilyID=6c095bba-6100-4ec9-9c54-6450b0212565&DisplayLang=en

x64 and IA64 versions of the update are also available.