4

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
billpg
  • 3,195
  • 3
  • 30
  • 57
  • Which programming language? May be just do second update in finally section of exeption handling? – Alexey Sviridov Mar 10 '11 at 18:38
  • This should not be done in program code since this leads to inconsistent states if the connection to the db has been interrupted between step 3 and 4. – ntziolis Mar 10 '11 at 18:42
  • 5
    I would suggest that the overall approach is misguided. Each command should open a connection, do something, then close the connection and rely on connection pooling to handle the expense of opening connections. Thus, you never need care about whether your connection was closed or not. You simply always open a connection when you need to do an operation. – Thomas Mar 10 '11 at 18:54
  • @Alexey - No programming language can ensure the finally block runs when the machine is exploding. – billpg Mar 11 '11 at 08:07
  • For the record, my title to this question was originally "Run an SQL...", not it's current "Run a SQL..." as edited by @marc_s. I pronounce it "Ess-Queue-Ell", and I don't care if that's wrong. – billpg Mar 17 '11 at 18:58
  • @billpg: okay - that's why. I was wondering.... I would pronounce it "A sequel command ...." - therefore the "an" would be wrong... – marc_s Mar 17 '11 at 19:19
  • @marc_s - Annoyingly, "sequel" is the correct pronounciation. In this case, however, I reject correctness. :) – billpg Mar 18 '11 at 09:08

3 Answers3

2

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!

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
  • I like this approach. If there's a way to "SELECT * FROM OpenCon WHERE SPID NOT IN sys_OpenConnections" then other machines will know the connection was stopped abrubtly. – billpg Mar 11 '11 at 08:00
  • In SMSS have a look at the definition of sp_who2. Its in the master database under programmability>>stored procedures>>system stored procedures. Right click and select modify. I don't suggest you change this, but it should contain everything you need. I think sys.sysprocesses is the table you need though – Jon Egerton Mar 11 '11 at 09:26
1

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.

Thomas
  • 63,911
  • 12
  • 95
  • 141
  • Even relying on connection pooling this slows things WAYYY down. For example, I have a console app that does data migration on something like 500,000 records. If I open/close the connection on each record it takes an hour to run. If I use the same connection for the whole thing it runs in 3 minutes. – Ryan Mann Nov 14 '15 at 21:38
  • @Ryios - First, if you are _migrating_ 500K rows, then doing it row-by-row in the middle tier is not the right solution. Instead, I'd be inclined to bulk insert that data into a staging table and process it using SQL statements. I reiterate my point that having connections and especially transactions open for long periods of time is usually an indication of a design problem especially if you are at the point of asking if there is a way to know if your connection is still open. – Thomas Nov 17 '15 at 17:34
  • True, I wasn't suggesting that the OP do that, merely stating that even with connection pooling you take a performance hit when you open/close the connection for every transaction. It's best practice to open/close it for sure, but in some rare scenarios it makes since to reuse one if you need to do something often, and as fast as possible. – Ryan Mann Nov 17 '15 at 17:52
0

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.

Mike Baranczak
  • 8,291
  • 8
  • 47
  • 71
  • That's an option. The only issue is that if there are lots of machines that need to make use of the system then this can become quite onerous. – Jon Egerton Mar 10 '11 at 18:58
  • If the connection dropped from network noise, the client would know about it. – billpg Mar 11 '11 at 07:57