What Happens When a Connection Drops in the Middle of a Transaction

 When a connection drops in the middle of an active transaction, SQL Server automatically treats the transaction as aborted and begins a rollback operation. Since the transaction was never committed, SQL Server uses the transaction log to undo all modifications made by that transaction. The rollback process may take significant time if a large number of rows were modified, and locks acquired by the transaction remain in place until rollback completes. If SQL Server itself crashes during the rollback, crash recovery continues the undo operation during startup using the transaction log. If the connection drops after COMMIT has already completed, the transaction remains committed because durability has already been established. This behavior ensures ACID compliance, particularly the Atomicity property, where a transaction either completes entirely or is fully rolled back. Understanding this mechanism is critical for diagnosing blocking issues, long-running rollbacks, application failures, and transaction recovery scenarios in production SQL Server environments.

Let’s see the demo

Before updating the record see the account balance

Now updating the account balance

BEGIN TRANSACTION;  UPDATE Account SET Balance = Balance + 10000 WHERE AccountNumber = 'SB100001';

Run successfully. Here we have not yet committed the transaction.

We can see the uncommitted value in the other session using no lock table hint.

 

See the updated value which is not yet committed.

Meanwhile , before commit

Ø  Network cable unplugged

Ø  SSMS closed

Ø  Application crashed

Ø  VPN disconnected

Ten we will lost the connection.

Here we are closing the SSIS and stopping the SSMS service.

Our connection got lost.

We are restarting the service and running the query and see the record either it was rollbacked or not.

See it was rollbacked.