Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

SQL Server runs every data change inside a transaction. The transaction mode decides who starts it and who ends it: SQL Server, your code, or the connection settings. Most surprises with “missing” changes or locks held for hours come from not knowing which mode a session is in.

Autocommit (the default)

Each statement is its own transaction. If it succeeds it is committed straight away; if it fails it is rolled back on its own. Earlier statements in the same batch stay committed.

CREATE TABLE dbo.Demo (ID int PRIMARY KEY, Note varchar(50));

INSERT dbo.Demo VALUES (1, 'first');    -- committed
INSERT dbo.Demo VALUES (1, 'duplicate'); -- fails with error 2627, rolled back on its own
INSERT dbo.Demo VALUES (2, 'second');   -- still runs and commits

SELECT * FROM dbo.Demo;                  -- rows 1 and 2

Explicit transactions

BEGIN TRANSACTION starts a transaction that lasts until COMMIT or ROLLBACK. Use one when several changes must succeed or fail together.

BEGIN TRANSACTION;
    UPDATE dbo.Demo SET Note = 'changed' WHERE ID = 1;
    DELETE dbo.Demo WHERE ID = 2;
COMMIT TRANSACTION;

Nesting is an illusion. A second BEGIN TRANSACTION only adds one to @@TRANCOUNT; an inner COMMIT only takes one away, and nothing is really committed until @@TRANCOUNT goes back to 0. A ROLLBACK anywhere rolls back everything and sets @@TRANCOUNT to 0.

BEGIN TRAN;                 -- @@TRANCOUNT = 1
    BEGIN TRAN;             -- @@TRANCOUNT = 2
        INSERT dbo.Demo VALUES (3, 'inner');
    COMMIT;                 -- @@TRANCOUNT = 1, nothing committed yet
ROLLBACK;                   -- @@TRANCOUNT = 0, row 3 is gone

SELECT COUNT(*) FROM dbo.Demo WHERE ID = 3;   -- 0

If you need to undo part of a transaction, use a savepoint (SAVE TRANSACTION name, then ROLLBACK TRANSACTION name), not a nested transaction.

Implicit transactions

With SET IMPLICIT_TRANSACTIONS ON, the first statement that reads or changes data quietly starts a transaction, and it stays open until you COMMIT or ROLLBACK. Even a plain SELECT starts one.

SET IMPLICIT_TRANSACTIONS ON;

SELECT COUNT(*) FROM dbo.Demo;  -- this starts a transaction
SELECT @@TRANCOUNT;              -- 1
UPDATE dbo.Demo SET Note = 'implicit' WHERE ID = 1;
-- Nothing is committed until:
COMMIT;

SET IMPLICIT_TRANSACTIONS OFF;

This is the mode that catches people out. Some drivers and tools switch it on (JDBC with autoCommit = false, some ORMs and Oracle-minded code), and a session that “only ran a SELECT” can hold locks and stop the transaction log being truncated until it disconnects.

Finding sessions in the wrong mode

Sessions with a transaction open, oldest first. A session with status sleeping and an open transaction is the classic sign of implicit transactions, or of an application that forgot to commit:

SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.status,
       s.open_transaction_count,
       at.transaction_begin_time,
       DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()) AS open_seconds
FROM sys.dm_exec_sessions s
JOIN sys.dm_tran_session_transactions st ON st.session_id = s.session_id
JOIN sys.dm_tran_active_transactions at ON at.transaction_id = st.transaction_id
WHERE s.is_user_process = 1
ORDER BY at.transaction_begin_time;

To check your own connection, bit 2 of @@OPTIONS is the implicit transactions setting:

SELECT CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END AS implicit_transactions;

Good practice

• Keep the default autocommit mode, and use explicit transactions only around changes that must succeed or fail together.
• Keep transactions short. Never wait for user input, a web call or a file inside one.
• Always pair BEGIN TRANSACTION with error handling that rolls back, with SET XACT_ABORT ON (the TRY/CATCH pattern).
• If an application needs implicit transactions, make sure it commits after reads as well as writes.

From my SQL Server DBA handbook.