Tuesday, October 26, 2010

System.Transactions with ODP.NET. System.AccessViolationException: “Attempted to read or write protected memory. Data provider internal error(-3000).

Use Case

Oracle 10g
Oracle Data Provider for .NET 2.0 11.2.0.1.2
VS 2008, C#

When trying to open connection to Oracle database inside Distributed Transactions, weird exception appears:

System.AccessViolationException: “Attempted to read or write protected memory. This is often an indication that other memory is corrupt.”

another people reported following exception:

Data provider internal error(-3000) [System.String]

Steps to reproduce.

1. Use explicit CommittableTransaction

            var factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
            string constr = 'my connection string';

            using (var transaction = new CommittableTransaction())
            {
                using (var conn = factory.CreateConnection())
                {
                    conn.ConnectionString = constr;
                    conn.Open();
                    conn.EnlistTransaction(transaction); <- Exception thrown here
                }
            }

2. Use implicit TransactionScope

            var factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
            string constr = 'my connection string';
            using (var scope = new TransactionScope())
            {
                using (var conn = factory.CreateConnection())
                {
                    conn.ConnectionString = constr;
                    conn.Open(); <-- Exception thrown here
                }
            }

Solution

After some time spent to reading documentation and googling, following things became apparent.

First of all, distributed transactions are handled by Distributed Transaction Coordinator service, so, MS DTC service must be running (in major configuration it's start mode is Manual)

Second, behaviour of odp.net depends on exact version of Oracle server and client versions and .net framework version. Recipe shown here wasn't checked on all possible combinations.

Third, there are some special connection string parameters needs to be considered.

As follows from http://download.oracle.com/docs/cd/E11882_01/win.112/e12249/featADO20.htm#CJAEBFEB, when first connection is opened to Oracle Database 10g (or higher), a local transaction is created. When a second connection is opened, the transaction is automatically promoted to a distributed transaction.

Connections, created from transaction context, must have "Promotable Transaction" setting set to "promotable".

Also, if application use System.Transactions, it is required that the "enlist" connection string attribute is set to either "true" (default) or "dynamic".

So, in my case, all what I had to do is just make sure that correct Promotable Transaction and Enlist settings are set.

1 comment: