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

Published Jan 15 2019 12:52 PM 107 Views
First published on MSDN on Apr 07, 2010

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

Version history
Last update:
‎Jan 15 2019 12:52 PM
Updated by: