Pentaho

 View Only

 Crazy transaction behaviour in SQL Server

  • Pentaho
  • Pentaho
Dan Keeley's profile image
Dan Keeley posted 12-17-2018 08:58

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


#Pentaho
Joao Figueiredo's profile image
Joao Figueiredo posted 12-17-2018 09:37

Hi,

when you say that the client connection is dropped, you are talking about an abnormal termination? If yes, depending on the cause of that termination you will get "zombie" connections on the server, which means that any uncommitted transactions will stay that way, until the connection if closed on the server (in this case a KILL statement would probably be the best approach) .

However most of the times (in that odd scenario - like force closing the SQL client) I've seen the server also closing the connection on it's side.

If, however, you do a regular disconnect from the server, it is expected to have a rollback on all uncommitted transactions. Be also aware that depending on the volume of rollback transactions that the server needs to process, it may take a while to actually rollback, and maybe that is giving you the impression that it is not working?

Dan Keeley's profile image
Dan Keeley posted 12-17-2018 09:41

Thats the horrible thing. this was indeed a zombie that was blocking everything else. The process itself was idle. As soon as we killed it everything freed up.

we only had about 8 users, so it's pretty concerning.

I'm assuming it's an unclean close that left the zombie, but so far i've only been able to identify one terminated connection in the log and i'm fairly sure thats the one we killed, rather than the one which was zombied.

Dan Keeley's profile image
Dan Keeley posted 12-17-2018 12:10

I've just noticed you can change this setting at a server level too:

tsql - Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally? - Stack Overflow

Seems like a wise idea.

Joao Figueiredo's profile image
Joao Figueiredo posted 12-17-2018 13:46

I'm not sure that option is supposed to work on the scenario that you described.

It seems it is intended to work along with errors raised within T-SQL (like stored procedures) much like an EJB3 @ApplicationException(rollback=true) exception works when is thrown inside a managed transaction.

In you're scenario you're having a connection being closed/dropped or something in those lines, so I would not assume that specific option to be relevant in that case, at least without testing it.

Dan Keeley's profile image
Dan Keeley posted 12-17-2018 15:42

Absolutely - definitely won't assume that.  We are using some stored procs, and I had a review and they're not using transactions.  So indeed, this could be a red herring.

Nevertheless the symptom remains - I had an idle process on the database that was blocking a whole bunch of other processes from running.

I think next step will have to be to try and reproduce this.

Also this one does talk about processes that dont query all rows - e.g. PDI:

https://logicalread.com/understanding-sql-server-locks-and-blocks-dr01/#.XBfGNVX7S70

Related Content