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

StackExchange Database Administrators Q#104920, answer score: 13