1

I have a Winforms application that calls a webservice method

The webservice method starts a long running transaction (unfortunately there are lots of little commands as opposed to one long running one.)

As far as I can see if the webservice call times out the transaction in the the webservice method will continue to run and complete. What I would like to happen would be for the transaction to rollback when the webservice timesout. Is this possible?

AJM
  • 32,054
  • 48
  • 155
  • 243
  • When you invoke the webservice you could pass a timeout value. When you execute the DB command, just use the specified timeout. This way the client is specifying how long they are willing to wait. When the command times out, your exception handler can rollback the transaction. Another possibility would be to use async webservice calls, so that the client can wait much longer. – Tomas McGuinness Oct 11 '12 at 10:18
  • Thanks, unfortunately there are lots of little commands as opposed to one long running one. – AJM Oct 11 '12 at 10:19
  • Sorry! I really should learn to read the questions in detail. One crude option would be to check elapsed time each time a command completes. If the elapsed time is too long, abort and roll the transaction back. – Tomas McGuinness Oct 11 '12 at 10:22
  • If the "little commands" you mention are database calls for a single instance of a database then rollback is trivial. If not you will have to figure out how to rollback the commands. Note that the timeout is controlled by client, I don't know if the server actually gets a notification when the timeout occurs on client and you may have to devise some hacks (such has timeout value included in request); as others have suggested allowing the server to complete the execution and providing async response to client is a more accepted approach. – Miserable Variable Sep 24 '13 at 01:51

1 Answers1

0

Reviving an old thread, but I have had the same problem, so thought I would update this for anyone else that comes across it. The best solution I have found is to use XACT_ABORT ie insert

SET XACT_ABORT ON

at the start of your transaction. This will force a rollback for any type of client error (so your transaction will not continue to run and complete, it will just rollback). There is a really good discussion on it in the dba stackexchange:

https://dba.stackexchange.com/questions/10912/sql-server-transaction-timeout

Community
  • 1
  • 1
SeeMoreGain
  • 1,263
  • 16
  • 36