How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?

Question

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 drops¹ 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.

__

¹) 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...

score 3 · Accepted Answer · answered Mar 03 '15 at 20:37

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.

How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive?

1 Answers1