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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability