Transactions and ACID Properties

Introduction

Transactions are fundamental to database integrity, ensuring that a series of operations either all succeed or all fail together. Understanding ACID properties and transaction management is essential for building reliable data-driven applications.

Table of Contents

• ACID Properties
• Transaction Basics
• Isolation Levels
• Concurrency Problems
• Locking Mechanisms
• Deadlocks
• Transactions in EF Core
• Distributed Transactions
• Best Practices
• Interview Questions

ACID Properties

Overview

Atomicity

All operations in a transaction complete successfully or none do.

-- Bank transfer: Must debit AND credit, or neither
BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
-- If this fails, the debit above must be rolled back
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;

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

Consistency

Database moves from one valid state to another, respecting all constraints.

-- Constraints ensure consistency
ALTER TABLE Accounts ADD CONSTRAINT CK_Balance CHECK (Balance >= 0);
ALTER TABLE Orders ADD CONSTRAINT FK_Customer
    FOREIGN KEY (CustomerId) REFERENCES Customers(Id);

-- Transaction maintains consistency
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountId = 1;
    -- If balance goes negative, constraint violation → rollback
COMMIT TRANSACTION;

Isolation

Concurrent transactions don’t see each other’s uncommitted changes.

-- Transaction 1: Reads balance
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Returns 100
-- Meanwhile, Transaction 2 updates balance to 50
-- With proper isolation, Transaction 1 still sees 100
COMMIT TRANSACTION;

Durability

Once committed, changes survive system failures.

BEGIN TRANSACTION;
    INSERT INTO Orders (CustomerId, Total) VALUES (1, 250.00);
COMMIT TRANSACTION;
-- Power failure here
-- After restart, the order is still in the database

Transaction Basics

SQL Server Transaction Syntax

-- Explicit transaction
BEGIN TRANSACTION;
    -- Operations
    INSERT INTO Orders (...) VALUES (...);
    UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 1;

    -- Check for errors
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN;
    END

COMMIT TRANSACTION;

-- Named transactions
BEGIN TRANSACTION OrderProcessing;
    -- Operations
COMMIT TRANSACTION OrderProcessing;

-- Save points (partial rollback)
BEGIN TRANSACTION;
    INSERT INTO Orders (...) VALUES (...);

    SAVE TRANSACTION BeforeInventory;

    UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductId = 1;

    IF @@ROWCOUNT = 0
    BEGIN
        ROLLBACK TRANSACTION BeforeInventory;  -- Only rolls back to savepoint
        -- Handle out of stock
    END

COMMIT TRANSACTION;

TRY-CATCH with Transactions

BEGIN TRY
    BEGIN TRANSACTION;

    -- Operation 1
    INSERT INTO Orders (CustomerId, OrderDate, Total)
    VALUES (@CustomerId, GETDATE(), @Total);

    DECLARE @OrderId INT = SCOPE_IDENTITY();

    -- Operation 2
    INSERT INTO OrderItems (OrderId, ProductId, Quantity, Price)
    VALUES (@OrderId, @ProductId, @Quantity, @Price);

    -- Operation 3
    UPDATE Products
    SET StockQuantity = StockQuantity - @Quantity
    WHERE ProductId = @ProductId;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Log error
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Isolation Levels

Isolation Levels Comparison

Setting Isolation Level

-- Set for session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set for specific transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    SELECT * FROM Accounts WHERE Balance > 1000;
    -- No phantom reads possible
COMMIT TRANSACTION;

-- Check current isolation level
SELECT
    CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable Read'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS IsolationLevel
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

Read Uncommitted

-- Can read uncommitted (dirty) data
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Or use table hint
SELECT * FROM Orders WITH (NOLOCK);

-- Use case: Reporting queries where slight inaccuracy is acceptable
-- Risk: May read data that gets rolled back

Read Committed (Default)

-- Only reads committed data
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 100
    -- Another transaction commits Balance = 50
    SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 50 (changed!)
COMMIT TRANSACTION;
-- Risk: Non-repeatable reads

Repeatable Read

-- Locks read data to prevent changes
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 100
    -- Another transaction cannot change this row
    SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Still 100
COMMIT TRANSACTION;
-- Risk: Phantom reads (new rows can appear)

Serializable

-- Prevents phantom reads with range locks
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    SELECT * FROM Accounts WHERE Balance > 1000;  -- Returns 5 rows
    -- Another transaction cannot insert rows with Balance > 1000
    SELECT * FROM Accounts WHERE Balance > 1000;  -- Still 5 rows
COMMIT TRANSACTION;
-- Risk: Lower concurrency, more blocking/deadlocks

Snapshot Isolation

-- Enable at database level
ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON;

-- Uses row versioning instead of locks
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Sees 100
    -- Another transaction commits Balance = 50
    SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Still sees 100 (snapshot)
COMMIT TRANSACTION;
-- Benefits: High concurrency, no blocking
-- Cost: TempDB overhead for version store

Concurrency Problems

Dirty Read

Reading uncommitted data that may be rolled back.

-- Transaction 1
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = 500 WHERE AccountId = 1;
-- Not committed yet

-- Transaction 2 (READ UNCOMMITTED)
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Returns 500

-- Transaction 1
ROLLBACK TRANSACTION;  -- Balance back to original
-- Transaction 2 read data that never really existed

Non-Repeatable Read

Same query returns different results within a transaction.

-- Transaction 1 (READ COMMITTED)
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Returns 100

-- Transaction 2
UPDATE Accounts SET Balance = 50 WHERE AccountId = 1;
COMMIT;

-- Transaction 1 (continued)
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- Returns 50 (changed!)
COMMIT TRANSACTION;

Phantom Read

New rows appear between reads.

-- Transaction 1 (REPEATABLE READ)
BEGIN TRANSACTION;
SELECT COUNT(*) FROM Orders WHERE Total > 100;  -- Returns 5

-- Transaction 2
INSERT INTO Orders (Total) VALUES (150);
COMMIT;

-- Transaction 1 (continued)
SELECT COUNT(*) FROM Orders WHERE Total > 100;  -- Returns 6 (phantom row!)
COMMIT TRANSACTION;

Lost Update

Two transactions update the same row, one overwrites the other.

-- Transaction 1
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 100
-- Calculates new balance: 100 + 50 = 150

-- Transaction 2
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE AccountId = 1;  -- 100
UPDATE Accounts SET Balance = 120 WHERE AccountId = 1;  -- 100 + 20
COMMIT TRANSACTION;

-- Transaction 1 (continued)
UPDATE Accounts SET Balance = 150 WHERE AccountId = 1;  -- Overwrites 120!
COMMIT TRANSACTION;
-- Lost Transaction 2's update

Locking Mechanisms

Lock Types

Lock Hierarchy

Database Lock
└── Table Lock (Intent locks)
    └── Page Lock
        └── Row Lock (Key lock for indexes)

Lock Hints

-- Read with no locks (dirty reads allowed)
SELECT * FROM Orders WITH (NOLOCK);

-- Read with shared lock held until end of transaction
SELECT * FROM Orders WITH (HOLDLOCK);

-- Force row-level locking
SELECT * FROM Orders WITH (ROWLOCK);

-- Force table lock (avoid lock escalation)
SELECT * FROM Orders WITH (TABLOCK);

-- Exclusive lock for update
SELECT * FROM Orders WITH (UPDLOCK) WHERE OrderId = 1;

Viewing Locks

-- Current locks
SELECT
    resource_type,
    resource_database_id,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE request_session_id > 50;

-- Blocked sessions
SELECT
    blocking_session_id,
    session_id,
    wait_type,
    wait_time,
    wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

Deadlocks

Deadlock Example

-- Transaction 1
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;  -- Locks row 1
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;  -- Waits for row 2

-- Transaction 2 (simultaneously)
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 50 WHERE AccountId = 2;   -- Locks row 2
WAITFOR DELAY '00:00:05';
UPDATE Accounts SET Balance = Balance + 50 WHERE AccountId = 1;   -- Waits for row 1

-- Deadlock! SQL Server kills one transaction as victim

Preventing Deadlocks

-- 1. Access objects in consistent order
-- Always lock AccountId 1 before AccountId 2

-- 2. Keep transactions short
BEGIN TRANSACTION;
    -- Do database work only
    -- No user input, external calls
COMMIT TRANSACTION;

-- 3. Use appropriate isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 4. Use UPDLOCK hint for read-then-update patterns
SELECT * FROM Accounts WITH (UPDLOCK) WHERE AccountId = 1;
UPDATE Accounts SET Balance = @NewBalance WHERE AccountId = 1;

Deadlock Detection

-- Enable deadlock tracing
DBCC TRACEON(1222, -1);  -- Writes to error log

-- Extended Events for deadlocks
CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename = 'Deadlocks.xel')
WITH (STARTUP_STATE = ON);

Transactions in EF Core

Basic Transaction

// Using SaveChanges (implicit transaction)
using var context = new AppDbContext();
context.Orders.Add(new Order { ... });
context.OrderItems.AddRange(items);
await context.SaveChangesAsync();  // Single transaction

// Explicit transaction
using var transaction = await context.Database.BeginTransactionAsync();
try
{
    context.Orders.Add(order);
    await context.SaveChangesAsync();

    context.Inventory.Update(inventory);
    await context.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Transaction with Isolation Level

using var transaction = await context.Database
    .BeginTransactionAsync(IsolationLevel.Serializable);

try
{
    var balance = await context.Accounts
        .Where(a => a.Id == accountId)
        .Select(a => a.Balance)
        .FirstAsync();

    // No other transaction can modify this until commit
    context.Accounts.Update(new Account
    {
        Id = accountId,
        Balance = balance - amount
    });

    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

TransactionScope (Ambient Transaction)

using var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadCommitted,
        Timeout = TimeSpan.FromMinutes(2)
    },
    TransactionScopeAsyncFlowOption.Enabled);

try
{
    // Multiple DbContexts share the transaction
    using var context1 = new OrdersDbContext();
    using var context2 = new InventoryDbContext();

    context1.Orders.Add(order);
    await context1.SaveChangesAsync();

    context2.Stock.Update(stock);
    await context2.SaveChangesAsync();

    scope.Complete();  // Commit
}
// If Complete() not called, transaction rolls back

Optimistic Concurrency

public class Order
{
    public int Id { get; set; }
    public decimal Total { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; }  // Concurrency token
}

// Update with concurrency check
var order = await context.Orders.FindAsync(orderId);
order.Total = newTotal;

try
{
    await context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Another user modified the row
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();

    // Handle conflict: client wins, database wins, or merge
    entry.OriginalValues.SetValues(databaseValues);
    await context.SaveChangesAsync();  // Retry
}

Distributed Transactions

Two-Phase Commit (2PC)

Phase 1: Prepare
├── Coordinator → Participant 1: "Prepare to commit"
├── Coordinator → Participant 2: "Prepare to commit"
├── Participant 1 → Coordinator: "Ready"
└── Participant 2 → Coordinator: "Ready"

Phase 2: Commit
├── Coordinator → Participant 1: "Commit"
├── Coordinator → Participant 2: "Commit"
└── All acknowledge completion

Saga Pattern (Alternative)

// Instead of distributed transaction, use compensating transactions
public class OrderSaga
{
    public async Task ProcessOrderAsync(Order order)
    {
        try
        {
            // Step 1: Reserve inventory
            await _inventoryService.ReserveAsync(order.Items);

            // Step 2: Process payment
            await _paymentService.ChargeAsync(order.CustomerId, order.Total);

            // Step 3: Create order
            await _orderService.CreateAsync(order);
        }
        catch (PaymentException)
        {
            // Compensate: Release inventory
            await _inventoryService.ReleaseAsync(order.Items);
            throw;
        }
        catch (OrderException)
        {
            // Compensate: Refund and release inventory
            await _paymentService.RefundAsync(order.CustomerId, order.Total);
            await _inventoryService.ReleaseAsync(order.Items);
            throw;
        }
    }
}

Best Practices

DO

-- ✅ Keep transactions short
BEGIN TRANSACTION;
    -- Only database operations
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;

-- ✅ Use appropriate isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- Default is usually fine

-- ✅ Handle errors properly
BEGIN TRY
    BEGIN TRANSACTION;
    -- Operations
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH

-- ✅ Access objects in consistent order to prevent deadlocks

DON’T

-- ❌ Long-running transactions
BEGIN TRANSACTION;
    SELECT * FROM LargeTable;  -- Holds locks
    WAITFOR DELAY '00:05:00';  -- User interaction
    UPDATE ...
COMMIT TRANSACTION;

-- ❌ Unnecessary high isolation levels
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- When READ COMMITTED works

-- ❌ Forget to handle @@TRANCOUNT
BEGIN TRANSACTION;
    -- If nested transaction, @@TRANCOUNT > 1
    -- ROLLBACK affects all levels!

Interview Questions

1. What are the ACID properties?

Answer:

• Atomicity: All operations succeed or none do (all-or-nothing)
• Consistency: Database moves from valid state to valid state
• Isolation: Concurrent transactions don’t interfere with each other
• Durability: Committed changes survive system failures

2. What isolation level would you use to prevent phantom reads?

Answer: Use SERIALIZABLE or SNAPSHOT isolation levels.

• SERIALIZABLE uses range locks to prevent inserts matching your query criteria
• SNAPSHOT uses row versioning to show consistent point-in-time view

SERIALIZABLE can cause more blocking; SNAPSHOT has TempDB overhead.

3. How do you handle deadlocks?

Answer: Prevention:

• Access objects in consistent order across transactions
• Keep transactions short
• Use appropriate isolation levels
• Use UPDLOCK for read-then-update patterns

Detection:

• SQL Server automatically detects and kills a victim
• Use Extended Events to capture deadlock graphs

Recovery:

• Implement retry logic in application code
• Log deadlock occurrences for analysis

4. What’s the difference between optimistic and pessimistic locking?

Answer:

• Pessimistic: Lock data when reading to prevent others from modifying. Uses database locks. Good for high-contention scenarios.
• Optimistic: Allow concurrent reads, check for conflicts at write time using version/timestamp. No locks held during read. Good for low-contention scenarios.

EF Core uses optimistic by default with [Timestamp] or [ConcurrencyCheck] attributes.

5. When would you use TransactionScope vs DbContext.Database.BeginTransaction?

Answer:

• DbContext.Database.BeginTransaction: Single database, single DbContext. Lightweight.
• TransactionScope: Multiple DbContexts, multiple databases, or need ambient transaction. Supports distributed transactions (MSDTC).

Use TransactionScope with TransactionScopeAsyncFlowOption.Enabled for async operations.

Sources

• Microsoft Docs: Transaction Processing
• Microsoft Docs: SET TRANSACTION ISOLATION LEVEL
• EF Core: Transactions
• SQL Server Concurrency