SQL Server Interview Question #145

What is an implicit transaction?

Transactions, Locking, Blocking & Deadlocks Senior Advanced

Detailed Explanation

With IMPLICIT_TRANSACTIONS enabled, certain statements automatically start a transaction when no transaction is already active. The transaction remains open until the application explicitly issues COMMIT or ROLLBACK.

This differs from SQL Server's common autocommit behavior, where each individual statement is automatically committed when it succeeds. Accidentally leaving an implicit transaction open can cause long-running locks and blocking.

Code Example

SET IMPLICIT_TRANSACTIONS ON;

UPDATE dbo.Products
SET Price = 100
WHERE Id = 10;

COMMIT;

SET IMPLICIT_TRANSACTIONS OFF;