Transactions isn't rolled back, when using connecction pooling

  • Thread starter Thread starter Lars Berg
  • Start date Start date
L

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;
}
}
 
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.
 
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
 
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
 
Back
Top