Orphaned Sessions and locks

Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
CraigCB
Look at WAITFOR command in the BOL.
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
was
> to update a row in the database and break the network connection before
the
> transaction could be commited. This locks the row in the database for
about
> 5 minutes before Sql Server realises that the communication has been
broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Hi
How did you break the conenction? Unplug the network cable?
What is your command timeout setting (not connection)?
Regards
Mike
"CB" wrote:

> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests wa
s
> to update a row in the database and break the network connection before th
e
> transaction could be commited. This locks the row in the database for abo
ut
> 5 minutes before Sql Server realises that the communication has been broke
n,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>
>|||The network tells sql when the connection is broken. I beleive there is a
TCP option called Keepalive. This controls how frequently TCP checks each
connection to see if it is alive - perhaps setting the keepalive to a
smaller value...
Also, you might check about connection pooling - and see if that is having
an effect ( although it should not.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi
> We are using the transaction capabilities of ADO .Net. One of our tests
> was
> to update a row in the database and break the network connection before
> the
> transaction could be commited. This locks the row in the database for
> about
> 5 minutes before Sql Server realises that the communication has been
> broken,
> times the transaction out and rolls it back. The question is, does anyone
> know how to configure this timeout value?
> Thanks
> Craig
>|||Yes, don't do this. This is old Client/Server technology and you should
migrate to n-Tier.
NEVER LET THE CLIENT CONTROL TRANSACTIONS. This is the number one
concurrency killer.
Batch up everything you want to do, including the BEGIN TRANS and COMMIT or
ROLLBACK logic and ship it to the SQL Server. Better yet, use nothing but
stored procedures for all of your CRUD operations.
Think through these two scenarios and tell me which one is more efficient
and faster.
Client-controlled transactions.
1. Client submits BEGIN TRAN request.
2. Request travels across network.
3. Server responds with success or failure.
4. Response travels across network.
5. Repeat process for each CRUD operation. Submit, traffic, response,
traffic.
6. Client does other processing.
7. Client submits COMMIT or ROLLBACK TRAN request.
8. Request travels across network.
9. Server responds.
10. Response travels across netowrk.
Server-controlled transactions.
1. Client submits parameters with stored procedure execution request.
2. Request travels across network.
3. Server executes transaction and all CRUD operations...very fast.
4. Response travels across network.
Hmmm? Which would you prefer? The point is about transaction processing is
that the DBMS must hold locks while the modifications are being made. You
DO NOT WANT those locks held simply because the client is having network
connections. If something should happen before the stored procedure and all
of the parameters reach the server, noting happens and the end user will
have to resubmit the request. If something should happen to the client
after the request was transmitted, the transaction still processes as if the
client were still connected. If someting happens to the server while
processing, then the ACID properties of the DBMS provide rollforward or
rollback functionality.
Sincerely,
Anthony Thomas
"CB" <craig.bryden@.derivco.com> wrote in message
news:enFjkvPOFHA.2748@.TK2MSFTNGP09.phx.gbl...
Hi
We are using the transaction capabilities of ADO .Net. One of our tests was
to update a row in the database and break the network connection before the
transaction could be commited. This locks the row in the database for about
5 minutes before Sql Server realises that the communication has been broken,
times the transaction out and rolls it back. The question is, does anyone
know how to configure this timeout value?
Thanks
Craig