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