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 AccountsSET Balance = Balance - 500WHERE AccountID = 100;UPDATE AccountsSET Balance = Balance + 500WHERE 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.
| Isolation Level | Dirty Reads | Nonrepeatable Reads | Phantom Reads |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Snapshot | No | No | No |
| Serializable | No | No | No |
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 ProductsSET 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 OrdersWHERE 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 SalesDBSET 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 SalesDBSET 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 | Purpose |
|---|---|
| Shared (S) | Reading data |
| Exclusive (X) | Updating data |
| Update (U) | Preparing to modify data |
| Intent (IS, IX) | Indicates lower-level locks |
| Schema (Sch-S, Sch-M) | Protect schema changes |
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 ProductsSET 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 ProductsSET Price = 100WHERE ProductID = 1AND 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
| Scenario | Recommended Isolation |
|---|---|
| Financial transfers | Serializable |
| General OLTP | Read Committed |
| Reporting | Snapshot |
| Azure SQL workloads | RCSI |
| Large analytical queries | Snapshot |
| High-contention inventory systems | Serializable or carefully designed Repeatable Read |
| Temporary diagnostic queries | Read Uncommitted (use cautiously) |
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
rowversioncolumn 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
The Data Community 