Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

dead connection detection Expand / Collapse
Author
Message
Posted Saturday, August 25, 2012 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 5:18 AM
Points: 5, Visits: 8
In Oracle, one can set

SQLNET.EXPIRE_TIME= <# of minutes>

to have the server detect connections where the client has died (e.g. when the client machine was abruptly powered down or the network connection lost without any signal to this effect to the server).

This allows the database server to automatically close out such connections in a timely manner, releasing any locks or other resources they hold.

Do any version(s) of SQL Server provide such functionality? If so, how?

So far everything I find suggests that there is no such functionality in SQL Server -- which would be a major gap in functionality as far as I see it.
Post #1350041
Posted Saturday, August 25, 2012 12:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:29 PM
Points: 32,834, Visits: 14,975
There are timeouts for clients, and when a client has a query running, there is some communication back to the client. I'm not sure that the timeouts are, but if a client dies, the server will detect it.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350062
Posted Saturday, August 25, 2012 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 5:18 AM
Points: 5, Visits: 8
What if a client grabs a lock, goes "out to lunch" holding the lock but not making further queries for a while -- and during this time the client abruptly dies or the network connection is severed? [In the case in question this scenario is somewhat common and critically important, not something that can just be shrugged off.]

Some admittedly brief googling suggests that SQL Server won't notice this. Microsoft's closest article is one on finding and fixing this issue manually as an administrator and there is an old article explicitly stating this as a gap in SQL Server as compared to Oracle.
Post #1350068
Posted Saturday, August 25, 2012 1:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:29 PM
Points: 32,834, Visits: 14,975
How does the client grab a lock? If your application is built to hold locks on tables, even for seconds, you are killing concurrency.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1350070
Posted Saturday, August 25, 2012 2:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 5:18 AM
Points: 5, Visits: 8
The entire purpose of the locks in question is to prevent concurrency of some critical operations in clients -- and to *quickly* notice when a lock is released. These are not "normal" data rows.

Given this usage it is paramount that the database be able to quickly notice when a client is no longer present -- including when the client has gone away quite silently, e.g. when a network cable is yanked.

Oracle can be configured to handle this. I am not seeing any evidence that SQL Server can be, which may force an entirely different approach since I need to support both databases.
Post #1350071
Posted Saturday, August 25, 2012 3:58 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
If the connection drops (and if the client goes away it will drop) any open transactions get rolled back. As a result of that rollback any locks are released.
Test it, it's easy enough to do.

If however the client starts a transaction, the transaction waits for user input and the user goes away for the week, that will not be dropped. Hence why "No user input in transactions" is a golden rule.

The entire purpose of the locks in question is to prevent concurrency of some critical operations in clients -- and to *quickly* notice when a lock is released. These are not "normal" data rows.


What kind of 'locks' are you talking about here? In SQL Server locks protect data against concurrent changes, row page or table.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350078
Posted Saturday, August 25, 2012 7:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 5:18 AM
Points: 5, Visits: 8
I'm talking about row locks.

As for row locks protecting against changes to data, yes, of course they do that.

They can also be a basis for distributed negotiation, of course. Unfortunately, SQL Server seems ill suited to such usage because there's no mechanism for it to recognize dead connections in any timely manner.

Of course that's a broader problem. In your client, lock a row, do some computation [not sending/receiving data from the database] -- then suffer a power outage or network cable cut during this computation. Now SQL Server has no idea to drop the connection or undo the lock. Now another client comes along and wants to lock the row and can't. That's a rather nasty situation even when using locks purely to prevent concurrent access to a row of data. There's a Microsoft article on loads of manual ways for an administrator to track down and resolve such an issue, but that's bunk -- the situation should automatically be recognized by periodically checking the connections.
Post #1350086
Posted Sunday, August 26, 2012 4:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
jessh (8/25/2012)
They can also be a basis for distributed negotiation, of course. Unfortunately, SQL Server seems ill suited to such usage because there's no mechanism for it to recognize dead connections in any timely manner.


As I already explained, if the connection drops, any transaction that connection held will be rolled back, any locks it held will be released.

It's easy to test, but if you prefer I do it, no problem. SQL 2008.

I'm going to use SQLCMD as a test.

One session open from SQLCMD with an open transaction

Now I can't pull the network connection (both clients and DB server are the same machine), so I'll just close the window (X in the top-right)
Session has gone from the SSMS query


If I go and look in Testing DB, that transaction's gone and so's the X row lock that it held.

If you want to try the same test with network connections, please do. I just don't feel like hauling my laptop out or booting VMs today.

One major difference between Oracle and SQL server. Many of the things where in Oracle there's settings to change don't have comparable settings in SQL Server because it's taken care of automatically. Doesn't mean SQL doesn't have the feature, just that the settings aren't tweakable.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass



  Post Attachments 
Start.jpg (74 views, 128.03 KB)
Gone.jpg (74 views, 80.27 KB)
Post #1350100
Posted Sunday, August 26, 2012 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 5:18 AM
Points: 5, Visits: 8
Thanks for the testing efforts.

Similar testing efforts worked great with Oracle as well -- up until we actually abruptly powered down a machine or dropped it off the network.

You make good points, though -- I need to verify that SQL Server doesn't handle this automatically before jumping to conclusions. There is at least one article out there citing this as a gap in SQL Server vs. Oracle, but the article is rather old, so I really need to test this myself.
Post #1350106
Posted Sunday, August 26, 2012 6:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
There's a problem sometimes when DTC is involved (distributed transaction coordinator). The actual client goes away but SQL doesn't notice because DTS (which sits between the client and the server) is involved or the client incorrectly terminates the connection and DTC doesn't abort. There are articles on how to fix that and manually kill sessions. That's often due to buggy drivers or data access layers though (Java being a known culprit)

I have never seen a problem (other than the DTC mentioned above) where a client machine went away for any reason and the connections were left open, transactions open and locks held.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1350107
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse