CODEWITHSUNDEEP
sqlsql-serverdisaster-recovery
billpg
billpg

Reputation: 3293

Run a SQL command in the event my connection is broken? (SQL Server)

Here's the sequence of events my hypothetical program makes...

  1. Open a connection to server.
  2. Run an UPDATE command.
  3. Go off and do something that might take a significant amount of time.
  4. Run another UPDATE that reverses the change in step 2.
  5. Close connection.

But oh-no! During step 3, the machine running this program literally exploded. Other machines querying the same database will now think that the exploded machine is still working and doing something.

What I'd like to do, is just as the connection is opened, but before any changes have been made, tell the server that should this connection close for whatever reason, to run some SQL. That way, I can be sure that if something goes wrong, the closing update will run.

(To pre-empt the answer, I'm not looking for table/record locks or transactions. I'm not doing resource claims here.)

Many thanks, billpg.

Upvotes: 4

Views: 1504

Answers (3)

Thomas
Thomas

Reputation: 64655

I'll expand on my comment. In general, I think you should reconsider your approach. All database access code should open a connection, execute a query then close the connection where you rely on connection pooling to mitigate the expense of opening lots of database connections.

If it is the case that we are talking about a single SQL command whose rows on which it operates should not change, that is a problem that should be handled by the transaction isolation level. For that you might investigate the Snapshot isolation level in SQL Server 2005+.

If we are talking about a series of queries that are part of a long running transaction, that is more complicated and can be handled via storage of a transaction state which other connections read in order to determine whether they can proceed. Going down this road, you need to provide users with tools where they can cancel a long running transaction that might no longer be applicable.

Upvotes: 1

Mike Baranczak
Mike Baranczak

Reputation: 8374

Assuming it's even possible... this will only help you if the client machine explodes during the transaction. Also, there's a risk of false positives - the connection might get dropped for a few seconds due to network noise.

The approach that I'd take is to start a process on another machine that periodically pings the first one to check if it's still on-line, then takes action if it becomes unreachable.

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41569

I'm not sure there's anything built in, so I think you'll have to do some bespoke stuff...

This is totally hypothetical and straight off the top of my head, but:

  1. Take the SPID of the connection you opened and store it in some temp
    table, with the text of the reversal update.
  2. Use an a background process (either SSIS or something else) to monitor the temp table and check that the SPID is still present as an open connection.
  3. If the connection dies then the background process can execute the stored revert command
  4. If the connection completes properly then the SPID can be removed from the temp table so that the background process no longer reverts it when the connection closes.

Comments or improvements welcome!

Upvotes: 2

Related Questions