Transactions group multiple operations into atomic units — all commit or all roll back. Locking and isolation levels control how concurrent sessions interact. Misconfigured concurrency causes blocking, deadlocks, and incorrect reads.

Basic Transactions 

  BEGIN TRANSACTION;

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

-- Verify business rules before commit
IF (SELECT Balance FROM dbo.Accounts WHERE AccountID = 1) < 0
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, N'Insufficient funds', 1;
END

COMMIT TRANSACTION;

@@TRANCOUNT shows nested transaction depth — only outermost COMMIT persists changes.

ACID in SQL Server

Property SQL Server Mechanism
Atomicity Transaction log — rollback on failure
Consistency Constraints, triggers, application rules
Isolation Locks + row versioning
Durability Log flush to disk (depends on settings)

Isolation Levels 

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT Balance FROM dbo.Accounts WHERE AccountID = 1;
-- Other sessions may change data before you re-read
COMMIT TRANSACTION;
Level Dirty Read Non-Repeatable Read Phantom Read
READ UNCOMMITTED Yes Yes Yes
READ COMMITTED (default) No Yes Yes
REPEATABLE READ No No Yes
SNAPSHOT No No No
SERIALIZABLE No No No

Default READ COMMITTED uses locking unless READ_COMMITTED_SNAPSHOT is enabled.

Read Committed Snapshot (RCSI) 

  ALTER DATABASE MyApp SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-- Readers don't block writers; writers don't block readers (row versions in tempdb)

Popular for OLTP — reduces blocking without app code changes. Monitor tempdb version store size.

Snapshot Isolation 

  ALTER DATABASE MyApp SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM dbo.Products WHERE CategoryID = 5;
-- Consistent snapshot for transaction duration
COMMIT TRANSACTION;

Must handle error 3960 — update conflict when snapshot transaction conflicts with concurrent writer.

Explicit Lock Hints 

  BEGIN TRANSACTION;

SELECT ProductID, Stock
FROM dbo.Products WITH (UPDLOCK, ROWLOCK)
WHERE ProductID = 5;

UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductID = 5;

COMMIT TRANSACTION;
Hint Behavior
UPDLOCK Update lock — prevents concurrent updates
HOLDLOCK Serializable range — prevents phantoms
NOLOCK READ UNCOMMITTED — dirty reads, avoid in production
TABLOCKX Exclusive table lock — maintenance only

Use hints sparingly — fix schema and isolation first.

Lock Escalation

SQL Server may escalate row/page locks to table lock when threshold exceeded (~5000 locks):

  -- Disable escalation for specific table (use carefully)
ALTER TABLE dbo.OrderItems SET (LOCK_ESCALATION = DISABLE);
-- Or AUTO (partition escalation in Enterprise)

Batch large updates to avoid escalation blocking entire table.

Monitoring Locks and Blocking 

  -- Current locks
SELECT
    request_session_id,
    resource_type,
    resource_database_id,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

-- Blocking chain
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.status,
    t.text AS batch_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0 OR r.session_id IN (
    SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
);

sp_WhoIsActive (community) provides richer blocking analysis than raw DMVs alone.

Deadlocks

When two sessions wait on each other’s locks, SQL Server kills one victim:

  -- Trace flags — log deadlock graph to error log
DBCC TRACEON(1222, -1);  -- XML deadlock report
DBCC TRACEON(1204, -1);  -- Verbose (dev only)

Enable system_health session Extended Events for deadlock capture in modern versions:

  SELECT CAST(target_data AS XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE s.name = 'system_health';

Deadlock Prevention

  • Access tables in consistent order across procedures
  • Keep transactions short — no user waits inside transactions
  • Index foreign keys — reduce lock duration on scans
  • Avoid default lock escalation on hot tables

Application Locks 

  BEGIN TRANSACTION;

EXEC @result = sp_getapplock
    @Resource = N'nightly_inventory_sync',
    @LockMode = N'Exclusive',
    @LockOwner = N'Transaction',
    @LockTimeout = 5000;

IF @result < 0
    THROW 50002, N'Could not acquire app lock', 1;

-- Critical section work here

COMMIT TRANSACTION;  -- Releases applock

Coordinate multi-instance jobs without external Redis when appropriate.

Optimistic Concurrency 

  -- Rowversion column for app-level concurrency check
ALTER TABLE dbo.Products ADD RowVer ROWVERSION;

UPDATE dbo.Products
SET Price = @new_price
WHERE ProductID = @id AND RowVer = @expected_rowver;

IF @@ROWCOUNT = 0
    THROW 50003, N'Concurrency conflict — row was modified', 1;

Common Mistakes

Mistake Symptom
Long open transactions Blocking, log growth
NOLOCK on financial reads Dirty reads, wrong balances
Missing index on FK Table scans + prolonged locks
User prompts inside transaction Hours of blocking
Serializable everywhere Unnecessary deadlocks

Performance Tips

  • Enable RCSI for read-heavy OLTP
  • Use SET XACT_ABORT ON in procedures — auto-rollback on errors
  • Partition large tables — partition-level lock escalation
  • Monitor LCK_M_* waits in sys.dm_os_wait_stats
  SELECT TOP 10 wait_type, wait_time_ms / 1000.0 AS wait_sec
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE N'LCK%'
ORDER BY wait_time_ms DESC;

Production Scenario: Inventory Deduction 

  CREATE OR ALTER PROCEDURE dbo.ReserveStock
    @ProductID INT,
    @Qty INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRANSACTION;

    UPDATE dbo.Products WITH (ROWLOCK)
    SET Stock = Stock - @Qty
    WHERE ProductID = @ProductID AND Stock >= @Qty;

    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50010, N'Insufficient stock', 1;
    END

    COMMIT TRANSACTION;
END;

Short transaction, row lock, predicate prevents oversell.

Troubleshooting Checklist

  1. Identify blocking head blocker session ID
  2. DBCC INPUTBUFFER(session_id) — what is it running?
  3. Check open transaction: sys.dm_tran_active_transactions
  4. Kill only as last resort: KILL session_id
  5. Root-cause fix — index, query rewrite, RCSI

Choose isolation based on consistency needs vs concurrency — default READ COMMITTED + RCSI suits many enterprise OLTP apps.