4

How can SQL Server terminate one of its transactions if it exceeds preset timeout?

Imagine the following transaction performed by traveling salesman's SQL client application, which is submitted command-by-command (i.e. not as single batch) and normally takes 2 seconds:

BEGIN TRANSACTION
    INSERT INTO PurchaseOrder <purchase order header>
    INSERT INTO PurchaseOrderLineItem <line item 1>
    INSERT INTO PurchaseOrderLineItem <line item 2>
    INSERT INTO PurchaseOrderLineItem <line item 3>
          :
    INSERT INTO PurchaseOrderLineItem <line item 99>
COMMIT TRANSACTION

Now, if mobile connection permanently drops1 during sending line item 51 (thus dropping the link to SQL server etc.), ABORT command will never arrive from client side. How can SQL Server time out and rollback the transaction when client which opened the transaction is permanently lost? (Condition example: no new command arrived for 30 seconds in that transaction.)

The problem I'm trying to avoid is that all other SQL commands accessing PurchaseOrderLineItem table will wait infinitely for the one described above. How to clean way for other queued commands? (If the client application is unable to submit entire transaction batch at once.)

I can see advices to set client timeout but they are of no help if client is permanently lost.

__

1) other possible accidents leading to problem: application crashes and exits without cleanup, PC goes to blue screen of death, laptop runs out-of-battery, network device stops responding etc...

  • Unless I'm missing something... the keep alive checks SQL Server does on connections. If it no longer sees the client it'll terminate the session which would take care of your issue. You could also check for long lock times and make up some custom logic that ends up killing valid connections at some point at 2 am as well... – Sean Gallardy Mar 3 '15 at 17:35
  • @SeanGallardy – TCP/IP keep alive mechanism can solve problems when client machine disappears from network – good. But client application can crash sometimes (and restart under new connection) and TCP/IP keep-alive mechanism won't react here. So I'm searching for mechanism how SQL Server can detect and terminate transactions where no new command was seen for example for 30 seconds (question updated on this). I think I understand the theory and I'm searching for practical advices on implementing such a thing. – miroxlav Mar 3 '15 at 18:12
  • If the application crashes, the connection will be terminated (even if it lingers it will eventually be cleaned up by the keep alive mechanism). You're literally asking to not have idle connection for more than 30 seconds, which is the keep alive mechanism and I WOULD NOT set it that low. – Sean Gallardy Mar 3 '15 at 19:37
3

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this.

Of course, one could ask why not

  • use a reliable queueing communication channel (eg. MSMQ)
  • submit the entire order in one call rather than 100 calls
  • commit the order line by line in 'pending' state

Any of the above would work. 30 second transactions would not work.

|improve this answer|||||
  • Testing shows that ABORT-ed transaction cannot be detected: waiting queries cannot continue if I press STOP on long running query in SSMS (which does UPDATE+WAITFOR DELAY). Do you have explanation for this? But they can continue immediately if I kill that SSMS instance instead of stopping – looks good. +1 for bullet point suggestions. – miroxlav Mar 4 '15 at 6:45
  • Aborted batch is not a disconnect, so it should not and does not abort the transaction (pressing STOP in SSMS is aborting the current batch, not the transaction). Look also at SET XACT_ABORT ON. – Remus Rusanu Mar 4 '15 at 8:38
  • OK, I'm getting the point. With SET XACT_ABORT OFF the other transactions wait for my COMMIT TRAN or ROLLBACK TRAN and with SET XACT_ABORT ON aborting the batch by pressing STOP issues ROLLBACK TRAN. I understand this option and will consider it. – miroxlav Mar 4 '15 at 9:16
  • Note that pressing STOP in SSMS is not equivalent to any 'hung' or crash in you client app. STOP is equivalent to your application hitting the SqlCommand.CommandTimeout and initiating a query abort. The equivalent of app crash would be to kill SSMS or Disconnect the query window (but SSMS would refuse to disconnect 'abruptly' while running a batch, so will try to abort it first, different from an app crash behavior) – Remus Rusanu Mar 4 '15 at 10:10
  • Yes, I assumed pressing STOP is equivalent to expired timeout on which client sends ABORT to server. I was dealing with it because I was trying to check and understand all scenarios. Thanks for the followup anyway, it may be helpful for some readers. – miroxlav Mar 4 '15 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.