What happens if a client connection is killed while holding a lock?

Question

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?

1. Does SQL Server have a heartbeat mechanism to detect that the socket got killed?
2. 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.

Answer 1

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:

1. 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

Answer 2

You could test this, like so:

• Open SSMS, Connect to the Server/Database, Open New query
• begin tran , insert into sometable select somevalue
• Open new SSMS window, and run select * from sometable - it should be blocked.
• Open Task Manager and Kill first SSMS process
• Check the results of select in new SSMS window whether the table contains somevalue (it won't, because the transaction got rolled back)

That being said, I have seen cases with SSRS reports where the user closes their browser, but the select query keeps running on the datasource for hours.