Transactions isn't rolled back, when using connecction pooling

[Lars Berg]

I have a asp.net (1.1) Web Application.

Connection pooling is on

From that webapplication I execute this SQL with a commandtimeout of
10 seconds
"BEGIN TRAN
UPDATE XYZ set column1 = '3PU' where iid = 1234
WAITFOR DELAY '00:00:15
COMMIT TRAN"

The SQL will timeout during the WAITFOR DELAY - so COMMIT is never
called.

But the transaction is still open and locks are helt on ressources.

Even thou I call Close() and Dispose() on the connection - the
transaction isn't rolled back (because of the pooling the connection
is never really closed).

Only if I clear the pool - or when I reuse this connection again are
the transaction rolled back?

1) Is this a behavior I should be worried about in a production
environment? Do I risk that resources are blocked for longer periods
of time even thou the SQL has timed out (or failed) ?

2) Does it rollback the transaction again next time a random
connection is taken from the pool - or is it only when its this exact
connection that is being taken?

If connection pooling is off everything works as expected.

Any insight is appriciated..

Sample code:
SqlConnection connection = null;
SqlCommand command = null;


try
{
//create connection
connection = new
SqlConnection(Daisy.SqlDatabase.GetConnectionString(null));
connection.Open();


//create command
command = connection.CreateCommand();

command.CommandType =
CommandType.Text;
command.CommandTimeout = 10;



string sql = @"
BEGIN TRAN
UPDATE d_invoice_line set charge_code_id = '3PU' where invoice_id =
181685
WAITFOR DELAY '00:00:15
COMMIT TRAN'";

command.CommandText = sql ;


command.ExecuteNonQuery();

}
catch
{

}
finally
{

if (connection != null)
{
if (connection.State ==
ConnectionState.Open)
{
connection.Close();
}

connection.Dispose();
connection = null;
}
}

 

[Miha Markic [MVP C#]]

Hi Lars,

You should use ADO.NET's SqlTransaction class - you get an instance if you
call conn.BeginTransaction.
Don't forget to invoke either Commit() or Rollaback() methods.
Otherwise ado.net doesn't know that there is a transaction involved.

 

[Lars Berg]

Hi Miha.

Thanks for your answer.

Yes, I know that must be the best solution.
But this is a production system, and would take quite a bit of
recoding, since many of the transactions are handled inside stored
procedures.

So I'm just thinking loud, if this is a important issue in our setup -
or just a minor issue.

If the open transactions are rolled back, on each request for a
connection from the pool I guess the problem isn't that big (as that
happens each second).

I'm just worried that a "closed" connection could be on this locking
state for a long period of time.

/Lars

 

[Guest]

Lars, I'm having the exact same problem in the production environment and you
should be worried. Neither Sql Server nor ADO.Net close the connection and
rollback the transaction so this can be a problem.

I'll need some recoding to the .Net SqlTransaction object but I've also
heard of SET XACT_ABORT ON. Any comments?

- Ricardo