mardi 3 mars 2015

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


How can SQL Server terminate one of its transactions after 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 <purchase order line item 1>
INSERT INTO PurchaseOrderLineItem <purchase order line item 2>
INSERT INTO PurchaseOrderLineItem <purchase order line item 3>
:
INSERT INTO PurchaseOrderLineItem <purchase order 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?


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 prevent this? (If application is unable to submit entire transaction at once.)


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





Aucun commentaire:

Enregistrer un commentaire