1

I have a software operating via WLAN mounted on a moving device. At the moment transactions are opened and closed in code. Between the businesslogic is happening. Now i'm suffering of lost connection and staying open transactions on the database. (MSSQL 2012)

My solution was to move all transactions/logic to a sp.

So the client only calls the sp and transations are handled inside.

My question here is: What happens to a sp wenn the connection is lost? Does it run to the end?

Christian Edel
  • 132
  • 5
  • 1
    Probably you would get better answers at https://dba.stackexchange.com/ – Cleptus Apr 16 '19 at 15:11
  • 2
    Possible duplicate of [What happens to an uncommitted transaction when the connection is closed?](https://stackoverflow.com/questions/1539564/what-happens-to-an-uncommitted-transaction-when-the-connection-is-closed) – p.campbell Apr 16 '19 at 15:12

1 Answers1

1

This is covered in the documentation Controlling Transactions (Database Engine), specifically in the Errors During Transaction Processing section:

If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

I've emphasised the relevant section.

So, moving the transactions to the SP won't stop the transaction being rolled back if your connection drops. I would suggest finding out why your connection is unstable and fixing that. Otherwise you'll need to work out a way to run the query locally on the instance (perhaps, using SQL Agent).

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • The part you emphasised do i know. Its not the problem that it isn't rolled back, but the server detects the drop of client connection very slow, in the maintime the transaction stays open. That causes massive locks on some tables (if updated in transaction and run by multiple clients). – Christian Edel Apr 17 '19 at 05:01