Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

Enterprise Library 4.1 DAB leaking oracle connections that participate in DTC transactions

Enterprise Library 4.1 DAB leaking oracle connections that participate in DTC transactions

  • Comments 2

We had a case recently where connection pooling was not happening properly when using Enterprise Library 4.1 Data Access Block with Oracle provider (ODP.Net). It was working properly from EL 3.1.

Network trace and ODP.Net Connection Pooling trace revealed that a new physical connection was getting created for every open connection call even though previous connections were closed hence enough idle connections should be present in the pool.

ODP.Net Connection Pooling trace:

EL 4.1

TIME:2009/12/10-22:15:09:964 TID: f18 (POOL) New connection pool created for: "Data Source=orcl;User ID=****;" (id: -561933825)

TIME:2009/12/10-22:15:09:964 TID: f18 (POOL) Total number of connection pools: 1

TIME:2009/12/10-22:15:09:964 TID: f18 (POOL) Total number of connections for pool (id: -561933825) : 1

TIME:2009/12/10-22:15:15:292 TID: ef4 (POOL) Total number of connections for pool (id: -561933825) : 2

TIME:2009/12/10-22:15:19:839 TID: ef4 (POOL) Total number of connections for pool (id: -561933825) : 3

TIME:2009/12/10-22:15:24:293 TID: ef4 (POOL) Total number of connections for pool (id: -561933825) : 4

TIME:2009/12/10-22:15:28:730 TID: ef4 (POOL) Total number of connections for pool (id: -561933825) : 5

EL 3.1

TIME:2009/12/10-22:15:13:680 TID: 1b4 (MTS) Enlistment - txnid: (663035d9-4880f0a4-d8193c9a-9db7e470); result: 0

TIME:2009/12/10-22:15:15:352 TID: 1b4 (MTS) Enlistment - txnid: (2580b517-44746eab-4448249a-805bb5d8); result: 0

TIME:2009/12/10-22:15:16:977 TID: 1b4 (MTS) Enlistment - txnid: (796d2c59-4e10eef2-4f66b19d-0931af83); result: 0

TIME:2009/12/10-22:15:18:602 TID: 1b4 (MTS) Enlistment - txnid: (89a11b44-4cade6c4-5f8810b5-a9d23d6f); result: 0

TIME:2009/12/10-22:15:22:056 TID: 1b4 (MTS) Enlistment - txnid: (cbf1c92f-44018fd2-508c059d-1fec67dc); result: 0

Above trace shows that in when EL 4.1 is used a new connection is added to the pool every time while in EL 3.1 it is reusing the same connection.

After digging into the DAB code for a while I found that the problem is with GetConnection method of Microsoft.Practices.EnterpriseLibrary.Data.TransactionScopeConnections class.

public static DbConnection GetConnection(Database db)

{

Transaction currentTransaction = Transaction.Current;

if (currentTransaction == null)

return null;

Dictionary<string, DbConnection> connectionList;

DbConnection connection;

lock (transactionConnections)

{

if (!transactionConnections.TryGetValue(currentTransaction, out connectionList))

{

connectionList = new Dictionary<string, DbConnection>();

transactionConnections.Add(currentTransaction, connectionList);

currentTransaction.TransactionCompleted += OnTransactionCompleted;

}

}

lock (connectionList)

{

if (!connectionList.TryGetValue(db.ConnectionString, out connection))

{

connection = db.GetNewOpenConnection();

connectionList.Add(db.ConnectionString, connection);

}

}

return connection;

}

The issue is related to when the TransactionCompleted event is being assigned value. In EL 4.1 build this assignment happens in the block where dictionary is created while in EL 3.1 build it happens in the block where connection gets created. The handler for this event does some cleanup work.

If the assignment is done before connection creation (i.e. call to db.GetNewOpenConnection) as done in EL 4.1 then proper cleanup won't happen. One thing to notice when proper cleanup does not happen is that the value of currentTransaction.TransactionInformation.DistributedIdentifer is null (all 0s) until GetNewOpenConnection is called.

To resolve the issue move that line after the GetNewOpenConnection() call. Here is the modified method

public static DbConnection GetConnection(Database db)

{

Transaction currentTransaction = Transaction.Current;

if (currentTransaction == null)

return null;

Dictionary<string, DbConnection> connectionList;

DbConnection connection;

lock (transactionConnections)

{

if (!transactionConnections.TryGetValue(currentTransaction, out connectionList))

{

connectionList = new Dictionary<string, DbConnection>();

transactionConnections.Add(currentTransaction, connectionList);

}

}

lock (connectionList)

{

if (!connectionList.TryGetValue(db.ConnectionString, out connection))

{

connection = db.GetNewOpenConnection();

currentTransaction.TransactionCompleted += OnTransactionCompleted;

connectionList.Add(db.ConnectionString, connection);

}

}

return connection;

}

Recompile the modified assembly and copy it to the location from where your project is referencing the EntLib assemblies.

 

Author : Naresh(MSFT), SQL Developer Technical Lead, Microsoft

Reviewed by : Enamul(MSFT), SQL Developer Technical Lead, Microsoft

Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
  • Man i love you ! i've been struggling for months about this bug! and i could not even identify it!

    This problem is still present in Enterprise library 5. Thanks you so much :D

  • i think this issue also present in enterprise Library 5.0. can you please confirm?

Page 1 of 1 (2 items)