what happen if we do not explicitly commit a transaction

If a transaction is started using BEGIN TRANSACTION and is not explicitly committed using COMMIT, SQL Server keeps the transaction open until one of the following occurs:

Ø  A COMMIT is issued.

Ø  A ROLLBACK is issued.

Ø  The session/connection is terminated.

Ø  SQL Server shuts down unexpectedly.

An open transaction continues to hold locks, prevents transaction log truncation, can cause blocking, increase log file growth, and may lead to performance degradation across the system. If the connection is closed before the transaction is committed, SQL Server automatically rolls back the uncommitted transaction during session cleanup.

When a transaction begins:

BEGIN TRANSACTION;

UPDATE Employee

SET Salary = Salary * 1.1

WHERE DepartmentID = 10;

SQL Server creates a transaction context and assigns a Transaction ID. At this point COMMIT TRANSACTION; has not yet been executed. The transaction enters an Active Transaction State.

And internally do the following

Ø  Generates a Transaction ID.

Ø  Writes transaction begin record to the transaction log.

Ø  Acquires necessary locks.

Ø  Stores before/after image information in the log.

Ø  Keeps transaction open.

Because the transaction is still active:

Ø  Locks remain held.

Ø  Changes are not permanently committed.

Ø  Other sessions may be blocked.

Ø  Log records cannot be truncated.