System.Transactions: TransactionScope and CommittableTransactions leave open
transactions on server.

I'm having problems with commited or rolled back transactions leaving open
transactions on the database server.
The problem is on our web site using .Net 2.0, Windows 2003 server, SQL
Server 2005 and occurs when we
are making updates in more than one database (on the same server).

Basic flow is as follows:
1) Create a CommittableTransaction. (I've tried using TransactionScope with
same result)
2) Open connection to db1 on server X.
3) Update table in db1
4) Open connection to db2 on server X.
5) Update table in db2
6) Commit or rollback transaction in try catch
7) Close connections.
8) Dispose transaction

When looking in the Activity Monitor (Sql Server Management Studio) the last
connection
has 1 in Open Transactions. This is causing the connection pool to run out
of connections after a while
since these connections aren't returned to the pool (it seems).


To reproduce:
Create a dummy table on 3 DBs (same server: DB1, DB2, DB3):
--------------------------------------
create table dbo.AnyTable
(
anyColumn varchar(10)
)
--------------------------------------


Paste this in to a test app (run when hitting a button):
--------------------------------------
private void ExecuteWithCommittableTransaction(bool doFail)
{
try
{
using (CommittableTransaction tx = new CommittableTransaction())
using (SqlConnection cn1 = GetConnection("DB1"))
using (SqlConnection cn2 = GetConnection("DB2"))
using (SqlConnection cn3 = GetConnection("DB3"))
{
try
{
ExecuteOnDB(tx, cn1);
ExecuteOnDB(tx, cn2);
ExecuteOnDB(tx, cn3);

if (doFail) throw new ApplicationException("FAILED");
tx.Commit();
MessageBox.Show("Databases updated!");
}
catch (Exception ex)
{
tx.Rollback();
throw ex;
}
}

}
catch (Exception ex)
{
MessageBox.Show(string.Format("ERROR: {0}", ex));
}
}

private SqlConnection GetConnection(string db)
{
string server = "YOURDBSERVER";
return new SqlConnection(string.Format("Data Source={0};Initial
Catalog={1};Integrated Security=SSPI;Connect Timeout=30", server,db));
}

private void ExecuteOnDB(CommittableTransaction tx, SqlConnection cn)
{
cn.Open();
cn.EnlistTransaction(tx);
ExecuteCmd(cn);
}

private void ExecuteCmd(SqlConnection cn)
{
SqlCommand cmd = new SqlCommand("insert into anytable values('data')",
cn);
cmd.ExecuteNonQuery();
}
-------------------------------------

When running this code the last 2 connections will remain in an open
transaction. (Activity Monitor: 1 in Open Transactions).
All but the first will remain in this state for 5-10 minutes.
How can I force the transaction to close and return connections to the
normal connection pool?
Transactions will be closed when application ends or after 5-10 minutes, or
in the real case, when we reboot the webservers.


Thanks
/patrik rosquist

Hi all,
I've tried using DTS transactions (EnterpriseServices.ServicedComponent),
which we used in framework 1.1 with no problem at all. Same "problem" but
all 3 connections are now marked with 1 in Open Transactions.
I'm not sure if this is a problem since our old web, running 1.1 did not
have any problem with too many open connections. This makes me think that I
have a general "open, but forget to close connection problem" some where
hidden in my code. I'll look it over...
Anyway I would be greatful if somebody could explain why there are
connections with Open Transactions in the Activity Monitor when using
conenctions to more than one database...

Thanks,
Patrik Rosquist

"Patrik Rosquist" wrote:

I am having the same problem here. Can someone tell me if this is a valid problem or not. I have two tables in two different databases and am using TransactionScope. Though the transaction is committed, the Activity Monitor log shows 1 open transaction for this thread! Weird. Help!

From http://www.developmentnow.com/g/7_2006_8_0_0_812986/CommittableTransaction-leaves-open-transactions-on-server.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/