How does ADO.NET handle transactions when the connection is broken

31 Mar 2005 9:36 AM

Rob Thompson

Hi,

We are thinking of using ADO.NET to implement transactions on our
SqlConnections, but would like to know how ADO.NET and SqlServer handle the
situation when the connection gets broken.

We have a client application in one location and a SqlServer DB in another
location. We have tested the commit and rollback actions on a good
connection and everything works great! When the connection gets dropped
after the BeginTransaction method gets called however SqlServer seems to
take a while before realising the connection has been dropped and rolling
back the transaction (5 minutes). This means that the records updated inside
the transaction before the connection was dropped remain locked for 5
minutes before being rolled back.

Is there any way we can configure or control the timeout period?

Here is a basic code snippet:

try
{
db.OpenConnectionAndBeginTransaction();
db.ExecuteSomeSQL();
db.CommitTransaction();
}
catch(Exception)
{
db.RollbackTransaction();
throw;
}

Any help would be greatly appreciated.

Thanks,
Rob

31 Mar 2005 1:29 PM

Cowboy (Gregory A. Beamer) - MVP

If the entire transaction can be represented in T-SQL, I would move all of
the logic to the server. There are certainly instances where this is
impossible; in many instances, however, it is quite easy to switch to this
model.

If you want to control timeout, on the server, in .NET, you will have to
employ a method other than direct connect, so you can monitor the network
connection and facilitate a quick rollback.

Another possible option is adding locking hints to your SQL statements to
use the minimum type of lock necessary. NOTE, however, that SQL will
sometimes escalate locks despite hints, so hints alone are not a complete
solution. For clients that need to view but not alter data, the NOLOCK hint
can work. Realize that this can yield dirty reads, however, so you have to
determine the likelihood of pulling from the rows affected by the transaction.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"Rob Thompson" wrote:

> Hi,
>
> We are thinking of using ADO.NET to implement transactions on our
> SqlConnections, but would like to know how ADO.NET and SqlServer handle the
> situation when the connection gets broken.
>
> We have a client application in one location and a SqlServer DB in another
> location. We have tested the commit and rollback actions on a good
> connection and everything works great! When the connection gets dropped
> after the BeginTransaction method gets called however SqlServer seems to
> take a while before realising the connection has been dropped and rolling
> back the transaction (5 minutes). This means that the records updated inside
> the transaction before the connection was dropped remain locked for 5
> minutes before being rolled back.
>
> Is there any way we can configure or control the timeout period?
>
> Here is a basic code snippet:
>
> try
> {
>   db.OpenConnectionAndBeginTransaction();
>   db.ExecuteSomeSQL();
>   db.CommitTransaction();
> }
> catch(Exception)
> {
>   db.RollbackTransaction();
>   throw;
> }
>
> Any help would be greatly appreciated.
>
> Thanks,
> Rob
>
>
>
>
>