8

Possible Duplicate:
SQL Server and connection loss in the middle of a transaction

I have a .NET 3.5 client app that kicks off a long-running (5-10 m) stored proc on the MS SQL Server 2005. The stored proc starts with BEGIN TRAN and ends with COMMIT TRAN.

If I pull the plug on the workstation, what happens to the stored procedure, does it finish running? Does it finish running under all the circumstances? Or will the loss of connectivity with the workstation cause the database to abort the stored proc?

EDIT: The workstation and the SQL Server are on different boxes.

Community
  • 1
  • 1
AngryHacker
  • 59,598
  • 102
  • 325
  • 594
  • To the folks who closed the question as a dupe...please read both questions and you'll see that they are functionally different. Other question is about losing connection to DB, reconnecting and manipulating the transaction in progress. This question focuses on what happens on the server when the client connection is lost. – AngryHacker Jun 19 '11 at 02:00

3 Answers3

4

Since the stored procedure runs on the server, if the BEGIN/END TRANSACTION are part of that stored procedure the procedure should run to completion (barring any errors). The client will never receive any results, of course, since the connection was lost.

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • That's interesting; I was under the impression that the loss of a client connection would cause a transaction to roll back... not force it to complete, client or no. – Andrew Barber Jun 17 '11 at 23:11
  • Actually, I should have mentioned what you did in your answer. Typically, the server doesn't know in time that the connection was lost; if, of course, the SP runs a long enough time that the connection loss is noticed, there may be a rollback. I've never seen this, but it makes sense. I'm upvoting your answer as well, because you caught the edge case I missed mentioning. – Ken White Jun 17 '11 at 23:47
4

The loss of the workstation's power won't necessarily cause the SP to abort, but it could very well cause the transaction to roll back.

I say "could" because it does depend on exactly when the client loses its power. If a network connection is lost into a 'black hole' like this, the server won't be immediately notified that any disconnect happened at all; it has to rely on TCP eventually telling it that the connection is dead simply because the other side has not responded to anything in X time.

This is different from disconnecting the client application explicitly and 'normally'; in such a case, the client explicitly closes the connection, if applicable, and so SQL will know right away that the client is gone.

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
1

Somewhat similar; SQL Server and connection loss in the middle of a transaction

Be aware that connections aren't always shut down immediately, so unexpected behaviour must be anticipated.

Community
  • 1
  • 1
k_b
  • 2,470
  • 18
  • 9