hi gurus.
here is how I simulate an orphaned transaction from a query analyzer window:
once the transaction is open, pull the network cord from the client machine, plug it back in.
result -- orphaned transaction which is blocking other reads writes.
SQL Server does not roll it back, nor does it commit it.
This orphaned connection (transaction) hangs around amd SQL Server continues to keep locks owned by the client active until they are killed, or until the connection is terminated or closed by Windows NT (registry setting by default cleanup occurs every 2 hours).
The question is: How do you identify this just happened... It seems like a likely scenario, that an application starts a transaction, and a network hickup disconnects the app from the database... How can this application go back in and commit or rollback + restart the transaction??? Does SQL Server assign an ID to a transaction? I tried naming the transaction, but that name is valid only in the session where transaction began. So if you reconnect and try to commit (or rollback) transaction abc, it would not work.
HELP!! thanks!
here is how I simulate an orphaned transaction from a query analyzer window:
Code:
--create table
create table xxx (i int)
Code:
--initiate a transaction (do not commit or rollback)
begin tran
insert xxx (i) select 1
once the transaction is open, pull the network cord from the client machine, plug it back in.
result -- orphaned transaction which is blocking other reads writes.
SQL Server does not roll it back, nor does it commit it.
This orphaned connection (transaction) hangs around amd SQL Server continues to keep locks owned by the client active until they are killed, or until the connection is terminated or closed by Windows NT (registry setting by default cleanup occurs every 2 hours).
The question is: How do you identify this just happened... It seems like a likely scenario, that an application starts a transaction, and a network hickup disconnects the app from the database... How can this application go back in and commit or rollback + restart the transaction??? Does SQL Server assign an ID to a transaction? I tried naming the transaction, but that name is valid only in the session where transaction began. So if you reconnect and try to commit (or rollback) transaction abc, it would not work.
HELP!! thanks!