12

Possible Duplicate:
What happens to an uncommitted transaction when the connection is closed?

What would happen if lose the connection in the middle of a transaction?

I guess that if the transaction is never committed and could execute again, but... is there a way I can connect and roll it back to a saved point and continue with the things I already have?

Kind regards.

Tom
  • 16,842
  • 17
  • 45
  • 54
vtortola
  • 34,709
  • 29
  • 161
  • 263

2 Answers2

14

If you lose the connection in the middle of a transaction you can no longer reenter this transaction from a different connection. If the connection is dropped, SQL Server will rollback any uncommitted transactions automatically. So if this happens, simply reconnect and you will find your database at the same state it was before starting the transaction (assuming of course there aren't other users that might have changed it in between.

Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
2

Generally, any break in the transaction will be rolled back. Whether the client disconnects, CommandTimeout or the server restarts itself.

This is Atomic in ACID

The ability to "pick up" a transaction where it left off does not make sense

  • other transactions that happen/the transaction LSN will be out of order
  • internal structures are changed as part of a write (indexes etc)/the database is inconsistent
gbn
  • 422,506
  • 82
  • 585
  • 676
  • There is no *fundamental* problem with long lives TXNs. A DBMS could implement them just like a long running "normal" TXN. I.e. it would have to keep all the locks/row-versions/... that the TXN holds/requires. The only thing is: it's very much impractical. Crashed applications would require manual cleanup, it would tie up server resources, and in most situations where SQL databases are used, the chances of committing a TXN that's hours or even days old would be slim to non-existant. – Paul Groke Feb 03 '15 at 19:29