SQL Server Q&A Topic 9: Isolation & Concurrency

SQL Server Interview Questions & Answers

Topic 9: Isolation & Concurrency

Questions 83–90 | Intermediate to Advanced

This section covers SQL Server transaction isolation levels, row-versioning-based concurrency, RCSI, SNAPSHOT isolation, dirty reads, non-repeatable reads and phantom reads.

Isolation level is an important database design decision because it determines the balance between transaction consistency and concurrency. Stronger isolation can provide greater consistency but may introduce additional blocking or resource usage.

83. What isolation levels does SQL Server support?

Answer:
SQL Server supports five primary transaction isolation levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SNAPSHOT
• SERIALIZABLE

SQL Server also supports a database option called READ COMMITTED SNAPSHOT, commonly called RCSI, which changes the implementation of READ COMMITTED to use row versioning for consistent reads instead of relying on shared locks for normal data reads.

A simplified comparison is:

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Interview Tip:
Do not describe RCSI as a sixth transaction isolation level. It is a database option that changes how READ COMMITTED provides statement-level read consistency.

84. What is READ UNCOMMITTED?

Answer:
READ UNCOMMITTED is the least restrictive SQL Server isolation level. It allows a transaction to read data that another transaction has modified but not yet committed.

It therefore allows dirty reads.

Example:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT
    AccountID,
    Balance
FROM dbo.Accounts;

The table hint:

SELECT *
FROM dbo.Accounts WITH (NOLOCK);

provides READ UNCOMMITTED semantics for that table reference.

Possible consequences include:

• Dirty reads
• Reading values that are later rolled back
• Missing rows
• Reading rows more than once
• Internally inconsistent results

DBA Tip:
READ UNCOMMITTED should not be selected simply because an application experiences blocking. First determine whether inconsistent reads are acceptable for the business requirement.

85. What is READ COMMITTED?

Answer:
READ COMMITTED is the default transaction isolation level for the SQL Server Database Engine.

Its primary guarantee is that a statement does not read uncommitted data.

Under traditional locking-based READ COMMITTED, SQL Server generally uses shared locks while reading data.

These shared locks are normally released as the statement processes the data rather than being retained until the end of the transaction.

Consequently:

• Dirty reads are prevented.
• Non-repeatable reads are still possible.
• Phantom reads are still possible.

Example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT
    AccountID,
    Balance
FROM dbo.Accounts;

Interview Tip:
READ COMMITTED prevents dirty reads, but two SELECT statements inside the same transaction are not guaranteed to return identical values if another transaction commits changes between those statements.

86. What is RCSI?

Answer:
RCSI stands for Read Committed Snapshot Isolation.

When READ_COMMITTED_SNAPSHOT is enabled, transactions running at READ COMMITTED can use row versions for normal reads rather than relying on shared locks to provide read consistency.

This can significantly reduce reader-writer blocking.

Enable RCSI using:

ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;

Under RCSI:

• Readers generally do not block writers because of shared data locks.
• Writers generally do not block readers for normal row-versioned reads.
• Writer vs writer blocking can still occur.
• Reads see a committed version appropriate to the statement.

Row versioning also introduces additional resource usage because SQL Server must maintain previous versions of modified rows.

Important:
RCSI reduces many reader-writer blocking scenarios, but it does not mean that SQL Server stops using locks entirely.

87. RCSI vs SNAPSHOT isolation?

Answer:
Both RCSI and SNAPSHOT use row versioning, but they provide consistency at different scopes and are enabled differently.

RCSI

• Enabled using READ_COMMITTED_SNAPSHOT ON.
• Existing READ COMMITTED workloads can use row versioning without explicitly changing every transaction to SNAPSHOT.
• Provides statement-level consistency.

Example:

ALTER DATABASE SalesDB
SET READ_COMMITTED_SNAPSHOT ON;

SNAPSHOT

• Requires snapshot isolation to be enabled for the database.
• The transaction explicitly requests SNAPSHOT isolation.
• Provides transaction-level consistency based on the transaction's snapshot.
• Update conflicts can occur when concurrent transactions modify data that has changed since the snapshot transaction began.

Example:

ALTER DATABASE SalesDB
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT *
FROM dbo.Orders;

COMMIT TRANSACTION;

Easy Interview Answer:

RCSI = statement-level versioned reads under READ COMMITTED.

SNAPSHOT = transaction-level consistent snapshot explicitly requested by the transaction.

88. What is a dirty read?

Answer:
A dirty read occurs when one transaction reads data modified by another transaction before the modifying transaction has committed.

Consider this example:

Session 1

BEGIN TRANSACTION;

UPDATE dbo.Accounts
SET Balance = 0
WHERE AccountID = 1001;

-- No COMMIT yet

Session 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT Balance
FROM dbo.Accounts
WHERE AccountID = 1001;

Session 2 may see:

Balance = 0

But Session 1 could subsequently execute:

ROLLBACK TRANSACTION;

The value read by Session 2 was therefore never a committed business state.

Interview Tip:
This is why NOLOCK can be dangerous for financial, inventory, billing and other workloads where correctness matters.

89. What is a non-repeatable read?

Answer:
A non-repeatable read occurs when a transaction reads the same row more than once and receives different committed values because another transaction modified and committed that row between the reads.

Example:

Session 1

BEGIN TRANSACTION;

SELECT Balance
FROM dbo.Accounts
WHERE AccountID = 1001;

-- Returns 5000

Session 2

UPDATE dbo.Accounts
SET Balance = 4000
WHERE AccountID = 1001;

Session 1 reads the row again:

SELECT Balance
FROM dbo.Accounts
WHERE AccountID = 1001;

-- May now return 4000

The same row returned two different committed values within Session 1's transaction.

Interview Tip:
Dirty read means reading data that has not committed.
Non-repeatable read means reading committed data twice and seeing a changed value.

90. What is a phantom read?

Answer:
A phantom read occurs when the same transaction executes the same predicate-based query more than once and receives a different set of rows because another transaction inserted or deleted qualifying rows and committed the change.

Example:

Session 1

BEGIN TRANSACTION;

SELECT *
FROM dbo.Orders
WHERE OrderAmount > 10000;

-- Returns 10 rows

Session 2

INSERT INTO dbo.Orders
(
    CustomerID,
    OrderAmount
)
VALUES
(
    1001,
    15000
);

Session 1 executes the same query again:

SELECT *
FROM dbo.Orders
WHERE OrderAmount > 10000;

-- May now return 11 rows

The newly appearing qualifying row is called a phantom.

Under locking-based SERIALIZABLE isolation, SQL Server can use key-range locking where appropriate to prevent other transactions from inserting rows into the protected key range.

Easy Interview Answer:

Dirty Read = Uncommitted value appears.
Non-Repeatable Read = Existing row changes.
Phantom Read = Qualifying row set changes.

Useful DBA Script: Check Row Versioning Configuration

SELECT
    name AS DatabaseName,
    snapshot_isolation_state_desc AS SnapshotIsolation,
    is_read_committed_snapshot_on AS RCSIEnabled
FROM sys.databases
ORDER BY name;

Useful DBA Script: Check Active Session Isolation Levels

SELECT
    session_id,
    login_name,
    status,
    transaction_isolation_level,
    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 is_user_process = 1
ORDER BY session_id;

Choosing an Isolation Strategy

Define Business Consistency Requirement
   ↓
Understand Current Blocking Pattern
   ↓
Review Transaction Scope
   ↓
Review Current Isolation Level
   ↓
Determine Required Read Consistency
   ↓
Evaluate Locking vs Row Versioning
   ↓
Consider RCSI / SNAPSHOT if Appropriate
   ↓
Evaluate Version Store Impact
   ↓
Test Application Behavior
   ↓
Monitor Blocking and Performance

Interview Quick Recap

• SQL Server supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT and SERIALIZABLE isolation levels.
• READ UNCOMMITTED allows dirty reads.
• READ COMMITTED prevents dirty reads but can allow non-repeatable and phantom reads.
• REPEATABLE READ prevents dirty and non-repeatable reads but can still allow phantom rows.
• SERIALIZABLE provides the strongest traditional locking-based isolation but may reduce concurrency.
• RCSI changes READ COMMITTED to use row-versioned reads.
• RCSI generally provides statement-level read consistency.
• SNAPSHOT provides transaction-level consistency based on row versions.
• RCSI can reduce reader-writer blocking, but writer-writer blocking can still occur.
• Dirty reads involve uncommitted data.
• Non-repeatable reads involve a previously read row changing.
• Phantom reads involve the qualifying row set changing.

← Previous Topic
# Topic 8: Locks, Blocking & Deadlocks

Next Topic →

# Topic 10: Security

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning