AnsweredAssumed Answered

Crazy transaction behaviour in SQL Server

Question asked by Dan Keeley on Dec 17, 2018
Latest reply on Dec 17, 2018 by Dan Keeley

Hi,

 

so after discovering this quite frankly bizarre behaviour in SQL Server:

 

SQLServer use of NOLOCK

 

I've now discovered something else weird.

 

If you have a transaction, and the client connection is dropped, the transaction is NOT rolled back, nor is it closed.

 

Hence at that point, ALL your queries will block on that table.

 

TBH I really can't believe this. I can't understand how people are living with this? So any war stories out there?

 

There does seem to be a workaround - a way to tell the database to operate in a sane way, but I generally dislike these types of solutions, has anyone tried it, it's documented here:

 

locking - SQL Server Transaction Timeout - Database Administrators Stack Exchange

Outcomes