orphaned transactions ..... 1

[vadimg]

hi gurus.

here is how I simulate an orphaned transaction from a query analyzer window:

--create table
create table xxx (i int)

--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!

 

[vongrunt]

Actually you don't have to pull the cord out. In one QA window run this:

begin transaction
insert into mytable( somefield ) values ('blah')

... and in another:

select * from mytable

Because default isolation level (1) prohibites uncommited reads, blocking happens.

 

[vadimg]

you didn't understand the question.
i know why blocking happens.

what i'm trying to simulate is a network hickup on the connection that started the transaction, which renders the transaction and the session orphaned.

 

[donutman]

Shouldn't the scope of a transaction just be within the SP itself? I'd try to avoid writing an app that allows a user to hold it open. Maybe your business requirements force it, but on the other hand maybe there's a work around.
This is beyond my experience so I may be a bit naive.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[baddos]

I was going to say the same thing... I don't trust the application to do the transactions if I don't HAVE to.

 

[mrdenny]

I agree, put the begin transactions within a stored proc when ever possible.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[vadimg]

thanks for the answers guys. I got a hold of ms support guy, and he basically told me the same thing...

unfortunately, if the network connection get's lost there is not much you can do aside from tracking down the spid and killing it...
;(

 

[baddos]

One thing you can do to minize the locking is to optimize your queries in the transactions... This way less will be blocked if the transaction's connection is lost.

 

[vadimg]

Karl -- you are absolutely correct. The scope of the transaction should always be in the stored procedure.

However, in this case the transaction is a distributed transaction.... So it 2phase commits a sp to 3 different servers.


in any case -- i did some research on this...

the case i described was using TCP/IP. TCPIP is a connectionless protocol. There is no way for sql server to know weather the connection is dead or the calling service is just thinking about something because TCPIP does not use any kind of probing or keepalives...

This problem is solved by using NamedPipes for the connection. Named Pipes do use keepalives, and if a network hickup happens, the transaction will rollback or roll forward depending on timing within the distributed transaction (in theory... the documentation on this is a bit hazy - and i can't think of a way where the transaction would roll forward. but the rollback definitely works. )

 

[donutman]

Thanks for reposting. That is good to know.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]