This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Preserve data integrity and consistency by using transaction isolation levels and concurrency controls

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

A SQL AI Developer must understand how SQL Server and Azure SQL Database maintain data consistency while allowing many users and applications to access the database simultaneously. Proper use of transactions, isolation levels, row versioning, locking, and concurrency controls is critical for building scalable, high-performance, and reliable database applications.

The DP-800 exam expects candidates to understand:

• Transaction ACID properties
• SQL Server transaction isolation levels
• Locking behavior
• Row versioning
• Optimistic vs. pessimistic concurrency
• Deadlocks and blocking
• Snapshot isolation
• Read Committed Snapshot Isolation (RCSI)
• Best practices for balancing performance with consistency

Why Transaction Isolation Matters

Modern applications rarely have only one user connected to a database.

Examples include:

• Thousands of customers placing online orders
• Banking applications processing transfers
• Hospital systems updating patient records
• AI applications reading operational data while transactions occur

Without concurrency controls, users could:

• Read incomplete data
• Overwrite each other’s changes
• Produce incorrect calculations
• Corrupt business data

SQL Server solves these problems through:

• Transactions
• Locking
• Isolation levels
• Versioning

Understanding Transactions

A transaction is a sequence of one or more SQL statements treated as a single unit of work.

Example:

BEGIN TRANSACTION;
UPDATE Accounts
SET Balance = Balance - 500
WHERE AccountID = 100;
UPDATE Accounts
SET Balance = Balance + 500
WHERE AccountID = 200;
COMMIT;

If either statement fails:

ROLLBACK;

ensures neither account is changed.

ACID Properties

Every SQL transaction follows the ACID principles.

Atomicity

Everything succeeds or everything rolls back.

Example:

Money should never disappear because only one UPDATE executed.

Consistency

Database rules remain valid before and after the transaction.

Examples include:

• Foreign keys
• Check constraints
• Unique keys
• Business rules

Isolation

Concurrent transactions should not interfere improperly with one another.

Isolation levels determine exactly how much interaction is allowed.

Durability

Once committed:

• data survives crashes
• power failures
• server restarts

SQL Server accomplishes this through the transaction log.

What Is Transaction Isolation?

Isolation controls how much one transaction can “see” changes made by another transaction.

Higher isolation:

• Better consistency
• More locking
• Less concurrency

Lower isolation:

• Higher concurrency
• Better performance
• Greater risk of inconsistent reads

Choosing the correct isolation level is an important design decision.

SQL Server Isolation Levels

SQL Server supports five primary isolation levels.

Read Uncommitted

Lowest isolation level.

Allows reading data that has not yet been committed.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Advantages:

• Minimal locking
• Highest concurrency

Disadvantages:

• Dirty reads
• Incorrect results
• Inconsistent reporting

Equivalent to:

SELECT *
FROM Orders WITH (NOLOCK);

The DP-800 exam often tests that NOLOCK allows dirty reads and should not be used when data accuracy is required.

Dirty Reads

A dirty read occurs when Transaction B reads data modified by Transaction A before Transaction A commits.

Example:

Transaction A:

UPDATE Products
SET Price = 200;

Before commit:

Transaction B reads:

Price = 200

Transaction A rolls back.

Actual value:

Price = 100

Transaction B used data that never officially existed.

Read Committed (Default)

Default SQL Server isolation level.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Characteristics:

• Prevents dirty reads
• Allows nonrepeatable reads
• Allows phantom rows

Most OLTP applications use this level.

Nonrepeatable Reads

Occurs when:

A transaction reads the same row twice.

Another transaction updates the row between reads.

Example:

First query:

Salary = 80,000

Another transaction updates:

Salary = 90,000

Second query:

Salary = 90,000

The same row produced different values.

Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Prevents:

• Dirty reads
• Nonrepeatable reads

Still allows:

• Phantom rows

Rows read remain locked until the transaction completes.

Phantom Reads

A phantom read occurs when:

The same query returns additional rows.

Example:

First query:

SELECT *
FROM Orders
WHERE Status='Pending';

Returns:

20 rows

Another transaction inserts a pending order.

Running the same query again returns:

21 rows

The extra row is called a phantom row.

Serializable

Highest isolation level.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Prevents:

• Dirty reads
• Nonrepeatable reads
• Phantom reads

SQL Server places range locks.

Advantages:

• Maximum consistency

Disadvantages:

• Significant blocking
• Lower throughput
• Reduced scalability

Often used for:

• Financial systems
• Inventory management
• Reservation systems

Snapshot Isolation

Snapshot Isolation uses row versioning instead of shared locks for reads.

Enable:

ALTER DATABASE SalesDB
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Benefits:

• Readers never block writers
• Writers never block readers
• Consistent transaction snapshot

Ideal for:

• Reporting
• Analytics
• AI workloads

Read Committed Snapshot Isolation (RCSI)

RCSI changes the default Read Committed behavior to use row versioning.

Enable:

ALTER DATABASE SalesDB
SET READ_COMMITTED_SNAPSHOT ON;

Benefits:

• Greatly reduces blocking
• Maintains Read Committed semantics
• No application code changes required

Azure SQL Database enables RCSI by default for many workloads because it improves concurrency.

Locking

SQL Server uses locks to maintain consistency.

Common lock types include:

Lock Granularity

Locks may occur at different levels:

• Row
• Page
• Table
• Partition
• Database

SQL Server automatically chooses appropriate granularity.

Large operations may trigger lock escalation, converting many row locks into a table lock to reduce memory overhead.

Blocking

Blocking occurs when:

One transaction waits for another transaction to release its locks.

Example:

Transaction A:

UPDATE Products
SET Price = 50;

Transaction B:

SELECT *
FROM Products;

Transaction B waits until Transaction A commits.

Blocking is normal and protects consistency, but excessive blocking can reduce throughput.

Deadlocks

A deadlock occurs when:

Transaction A waits for Transaction B.

Transaction B waits for Transaction A.

Neither transaction can continue.

SQL Server automatically selects one transaction as the deadlock victim and rolls it back.

Example:

Transaction A:

Locks Table A

Needs Table B

Transaction B:

Locks Table B

Needs Table A

Result:

Deadlock.

Minimizing Deadlocks

Best practices include:

• Keep transactions short.
• Access tables in a consistent order.
• Create proper indexes.
• Avoid unnecessary user interaction inside transactions.
• Commit as soon as possible.
• Reduce lock duration.

Optimistic Concurrency

Optimistic concurrency assumes conflicts are uncommon.

Instead of locking rows, applications detect changes before updating.

Common implementation:

rowversion

or timestamp columns.

Example:

UPDATE Products
SET Price = 100
WHERE ProductID = 1
AND RowVersion = @OriginalVersion;

If zero rows are updated:

Another user modified the row first.

Pessimistic Concurrency

Assumes conflicts are likely.

Locks data immediately.

Advantages:

• Prevents conflicts

Disadvantages:

• More blocking
• Reduced concurrency

Used in:

• Banking
• Airline reservations
• Inventory systems

Row Versioning

Snapshot Isolation and RCSI maintain previous row versions inside tempdb (or the persisted version store in databases that support Accelerated Database Recovery).

Readers access previous committed versions without blocking writers.

Benefits include:

• Improved concurrency
• Reduced blocking
• Better reporting performance

Transaction Best Practices

Keep Transactions Short

Avoid:

• User prompts
• Long loops
• Waiting for external APIs

Commit Promptly

Release locks quickly.

Use Appropriate Isolation Levels

Do not always choose Serializable.

Choose the lowest level that still satisfies business requirements.

Index Frequently Queried Columns

Better indexes reduce:

• Scan duration
• Lock duration
• Blocking

Retry Deadlock Victims

Applications should retry transactions after deadlock errors because SQL Server automatically rolls back the victim transaction.

Avoid NOLOCK for Critical Data

Dirty reads can lead to:

• Incorrect reports
• AI model training errors
• Financial inaccuracies

Isolation Level Selection Guide

DP-800 Exam Tips

Remember these frequently tested points:

• Read Committed is SQL Server’s default isolation level.
• Dirty reads occur only under Read Uncommitted (or NOLOCK).
• Snapshot Isolation uses row versioning instead of shared locks.
• RCSI reduces reader/writer blocking while preserving Read Committed semantics.
• Serializable provides the highest consistency but can significantly reduce concurrency.
• Deadlocks occur when two or more transactions wait on each other, and SQL Server automatically selects a deadlock victim.
• Optimistic concurrency commonly uses a rowversion column to detect conflicts rather than locking data.

Practice Exam Questions

Question 1

A banking application must guarantee that account balances remain accurate even when multiple users transfer funds simultaneously. Which transaction isolation level provides the highest level of protection against concurrency anomalies?

A. Read Committed
B. Snapshot
C. Serializable
D. Read Uncommitted

Correct Answer: C

Explanation: Serializable prevents dirty reads, nonrepeatable reads, and phantom reads by using range locks. It offers the highest level of transaction isolation and is well suited for critical financial operations.

Question 2

A developer executes the following statement:

SELECT * FROM Sales WITH (NOLOCK);

What behavior should the developer expect?

A. The query will prevent all concurrent updates.
B. The query may read uncommitted data.
C. The query automatically enables Snapshot Isolation.
D. The query uses Repeatable Read isolation.

Correct Answer: B

Explanation: The NOLOCK hint is equivalent to Read Uncommitted isolation and allows dirty reads, meaning rows may be read before transactions commit.

Question 3

A reporting application experiences blocking because long-running SELECT queries interfere with update operations. Which feature is most appropriate?

A. Repeatable Read
B. Serializable
C. Snapshot Isolation
D. Exclusive locking

Correct Answer: C

Explanation: Snapshot Isolation uses row versioning so readers do not block writers and writers do not block readers, making it ideal for reporting workloads.

Question 4

Which concurrency problem occurs when a transaction reads the same row twice and receives different values because another transaction updated the row?

A. Nonrepeatable read
B. Phantom read
C. Lock escalation
D. Dirty read

Correct Answer: A

Explanation: A nonrepeatable read occurs when the same row returns different values within the same transaction due to another committed update.

Question 5

What is the primary purpose of a rowversion column in optimistic concurrency control?

A. Encrypt row data
B. Compress large tables
C. Detect whether a row has changed since it was read
D. Prevent index fragmentation

Correct Answer: C

Explanation: Applications compare the original rowversion value during updates. If it has changed, another transaction modified the row, allowing the application to detect concurrency conflicts.

Question 6

Which SQL Server feature reduces reader and writer blocking while maintaining Read Committed behavior?

A. Read Committed Snapshot Isolation (RCSI)
B. Table hints
C. Lock escalation
D. Read Uncommitted

Correct Answer: A

Explanation: RCSI uses row versioning for Read Committed transactions, significantly reducing blocking without requiring application code changes.

Question 7

Two transactions each hold a lock that the other requires, causing both to wait indefinitely. What is this situation called?

A. Blocking
B. Lock escalation
C. Phantom read
D. Deadlock

Correct Answer: D

Explanation: A deadlock occurs when transactions wait on each other’s resources. SQL Server automatically selects one transaction as the deadlock victim and rolls it back.

Question 8

Which ACID property ensures that either all statements in a transaction succeed or none of them are applied?

A. Consistency
B. Isolation
C. Atomicity
D. Durability

Correct Answer: C

Explanation: Atomicity guarantees that a transaction is treated as a single unit of work. If any part fails, the entire transaction is rolled back.

Question 9

A database administrator wants to reduce the likelihood of deadlocks. Which practice is recommended?

A. Keep transactions open for longer periods.
B. Access tables in a consistent order across transactions.
C. Use Serializable isolation for every workload.
D. Disable indexes on frequently accessed tables.

Correct Answer: B

Explanation: Accessing resources in a consistent order reduces circular dependencies between transactions, decreasing the likelihood of deadlocks.

Question 10

Which statement best describes Snapshot Isolation?

A. It allows dirty reads to improve performance.
B. It relies exclusively on shared locks for readers.
C. It prevents writers from modifying data during reads.
D. It provides each transaction with a consistent version of committed data by using row versioning.

Correct Answer: D

Explanation: Snapshot Isolation stores previous committed versions of rows, allowing transactions to view a consistent snapshot of the database without blocking concurrent updates.

Go to the DP-800 Exam Prep Hub main page