Transactions: Implicit vs. Explicit

Implicit Transactions (Autocommit Mode)

Default Behavior

-- SQL Server default: AUTOCOMMIT (implicit transactions)
-- Each statement is its own transaction

INSERT INTO dbo.Users (Name, Email) VALUES ('John', 'john@example.com');
-- ✓ Automatically committed

UPDATE dbo.Users SET Email = 'newemail@example.com' WHERE UserID = 1;
-- ✓ Automatically committed

DELETE FROM dbo.Users WHERE UserID = 2;
-- ✓ Automatically committed

-- Each statement COMMITS automatically if no error
-- Each statement ROLLS BACK if an error occurs

The Problem with Implicit Transactions

-- Scenario: Transfer money from Account A to Account B
-- In implicit autocommit mode:

UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
-- ✓ Committed immediately

<-- PowerOutage here! -->

UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
-- Never executes

-- Result: $100 disappears (deducted from A but never added to B)
-- No rollback because each statement was independent

Explicit Transactions (BEGIN...COMMIT/ROLLBACK)

The Safe Approach

-- Explicit transaction: all-or-nothing guarantee
BEGIN TRANSACTION;

UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

COMMIT TRANSACTION;  -- Both succeed, or neither succeeds

With Error Handling

BEGIN TRANSACTION;

BEGIN TRY
  UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
  UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

  COMMIT TRANSACTION;
  PRINT 'Transfer successful';

END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
  PRINT 'Transfer failed: ' + ERROR_MESSAGE();

END CATCH;

Key insight: With explicit transactions, if ANY statement fails (constraint violation, permission error, power loss mid-statement), ALL statements in the transaction roll back automatically. This is the "all-or-nothing" guarantee.

The Nested Transaction Trap

Scenario: Calling Stored Procedures

-- Procedure A (calls Procedure B)
CREATE PROCEDURE usp_ProcessOrder
  @OrderID INT
AS
BEGIN
  BEGIN TRANSACTION;  -- Starts transaction

  EXEC usp_UpdateInventory @OrderID;  -- Calls Procedure B

  COMMIT TRANSACTION;  -- Commits?
END;

-- Procedure B (has its own transaction?)
CREATE PROCEDURE usp_UpdateInventory
  @OrderID INT
AS
BEGIN
  BEGIN TRANSACTION;  -- Nested? Or reuses outer?
  UPDATE dbo.Inventory SET Qty = Qty - 1 WHERE ...
  COMMIT TRANSACTION;
END;

The Truth About Nested Transactions

-- SQL Server does NOT support true nested transactions
-- BEGIN TRANSACTION in a nested call increments @@TRANCOUNT

SELECT @@TRANCOUNT;  -- 0 (no active transaction)

BEGIN TRANSACTION;
SELECT @@TRANCOUNT;  -- 1

  BEGIN TRANSACTION;  -- "Nested" but really just increments counter
  SELECT @@TRANCOUNT;  -- 2

  COMMIT TRANSACTION;  -- Decrements to 1 (NOT committed yet!)
  SELECT @@TRANCOUNT;  -- 1

COMMIT TRANSACTION;  -- NOW it commits
SELECT @@TRANCOUNT;  -- 0

-- All changes committed only when @@TRANCOUNT reaches 0

⚠ Gotcha: Only the final COMMIT (when @@TRANCOUNT reaches 0) actually commits. All intermediate COMMITs are just decrements. A single ROLLBACK at any level rolls back the ENTIRE transaction.

SET IMPLICIT_TRANSACTIONS

Enabling Implicit Transactions Explicitly

-- Default (AUTOCOMMIT):
SET IMPLICIT_TRANSACTIONS OFF;
INSERT INTO dbo.Foo VALUES (1);  -- Commits immediately

-- Implicit transactions mode:
SET IMPLICIT_TRANSACTIONS ON;
INSERT INTO dbo.Foo VALUES (2);  -- Does NOT commit
-- ⚠ Locks the table until COMMIT or ROLLBACK!

SELECT @@TRANCOUNT;  -- 1 (active transaction)

COMMIT TRANSACTION;

The Problem with IMPLICIT_TRANSACTIONS ON

Many legacy applications and some ORMs set this. It sounds good ("everything is transactional!") but it's dangerous:

-- Application behavior with IMPLICIT_TRANSACTIONS ON:

SELECT * FROM dbo.Users;  -- Starts transaction (lock acquired)
-- App processes data
-- ... network latency, user thinks, etc ...
-- (locks still held)

-- 5 minutes later, user clicks another button
UPDATE dbo.Users SET Status = 'Active' WHERE UserID = 1;
-- ✓ Update succeeds (still in same transaction)

-- Another user tries to read Users table:
SELECT * FROM dbo.Users;  -- BLOCKS (waiting for first transaction to commit)
-- Timeout → Application hangs

-- DBA checks: "Why are locks held?"
-- Answer: First application never committed its transaction

Isolation Levels and Implicit Transactions

Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Lock Duration
READ UNCOMMITTED	Yes ✗	Yes	Yes	Minimal
READ COMMITTED	No ✓	Yes	Yes	Short
REPEATABLE READ	No ✓	No ✓	Yes	Long
SERIALIZABLE	No ✓	No ✓	No ✓	Very Long

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
SELECT * FROM dbo.Users;
-- Locks released as soon as SELECT completes
COMMIT TRANSACTION;

-- vs. SERIALIZABLE:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
SELECT * FROM dbo.Users;
-- Locks held until COMMIT
COMMIT TRANSACTION;

Common Transaction Mistakes

Mistake 1: Long-Running Transactions

-- ✗ Bad: Opens transaction, does network I/O
BEGIN TRANSACTION;
SELECT @Data = SomeColumn FROM dbo.Table WHERE ID = 1;

-- (Call external API, takes 30 seconds)
EXEC sp_CallExternalService @Data;

UPDATE dbo.Table SET Status = 'Processed' WHERE ID = 1;
COMMIT TRANSACTION;

-- Locks held for 30+ seconds! Other users blocked.

-- ✓ Good: Keep transaction short
SELECT @Data = SomeColumn FROM dbo.Table WHERE ID = 1;

-- Do network I/O outside transaction
EXEC sp_CallExternalService @Data;

-- Only transaction for actual database change
BEGIN TRANSACTION;
UPDATE dbo.Table SET Status = 'Processed' WHERE ID = 1;
COMMIT TRANSACTION;

Mistake 2: Not Checking @@TRANCOUNT After Error

-- ✗ Bad:
BEGIN TRANSACTION;
UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

IF @@ERROR <> 0
  ROLLBACK TRANSACTION;
ELSE
  COMMIT TRANSACTION;

-- Problem: If one UPDATE succeeds but second fails,
-- first is already committed (implicit autocommit within transaction)

-- ✓ Better: Use explicit error handling
BEGIN TRY
  BEGIN TRANSACTION;
  UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
  UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  -- Handle error
END CATCH;

Mistake 3: Forgetting to Commit/Rollback

-- ✗ Connection left open with uncommitted transaction:
BEGIN TRANSACTION;
INSERT INTO dbo.Foo VALUES (1);
-- Connection closes without COMMIT or ROLLBACK
-- Automatic ROLLBACK occurs (but locks were held until timeout)

Best Practices

☐ Use explicit BEGIN TRANSACTION for multi-statement operations that must be atomic
☐ Keep transactions short (commit/rollback quickly)
☐ Do network I/O and external work OUTSIDE transactions
☐ Always use TRY/CATCH for error handling in transactions
☐ Check @@TRANCOUNT in CATCH block (may be > 0 if error occurred inside transaction)
☐ Avoid IMPLICIT_TRANSACTIONS ON (sets trap for lock duration issues)
☐ Use appropriate isolation level (not all READ COMMITTED by default)
☐ Document transaction boundaries in stored procedures
☐ Test failover and power-loss scenarios (not just happy path)
☐ Monitor long-running transactions (blocking other users)

The Bottom Line

SQL Server's default autocommit (implicit transactions) works great for single statements. But for operations that must succeed or fail together—transfers, multi-table updates, complex workflows—explicit BEGIN TRANSACTION is your safety net.

The key is understanding that implicit doesn't mean "no transaction." It means each statement is its own all-or-nothing unit. If you need multiple statements to be all-or-nothing, you must explicitly group them. Ignore this, and you get data inconsistency, mysterious locks, and 2 AM support calls.