What happens if a client connection is killed while holding a lock? What happens if a client establishes a transaction, locks a row or table, and the client process crashes before it has a chance to rollback or commit the transaction? What, if anything, does SQL Server do with the transaction?


Does SQL Server have a heartbeat mechanism to detect that the socket got killed?
Upon detecting that a socket got killed, does SQL Server rollback the transaction automatically? Or does the row/table lock persist (causing other clients to deadlock)?


Please provide authoritative sources to back up your answer.

UPDATE: The client process is crashing. I am not talking about someone invoking the KILL command.
The session along with the spid attached will be killed/rolled back.

https://stackoverflow.com/questions/3978227/how-to-kill-or-rollback-active-transaction

https://technet.microsoft.com/en-us/library/ms173730%28v=sql.110%29.aspx

EDIT:


I would check your logs to verify, but in essence, the socket would be forcibly closed.


It's not a database issue from what I can see...but do you have an error message you can post to clarify?

Also see Errors During Transaction Processing


  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