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
