2.4 — Database Configuration, Isolation Levels and Concurrency
DP-800 Domain 2 · Skill 2.4
What You Need to Know
Performance and correctness both depend on how the database is configured and how transactions see each other's changes. Questions ask for the setting that fixes a symptom, such as blocking, inconsistent reads or slow parallel queries.
Recommending configurations
| Area | Options and guidance |
|---|---|
| Azure SQL Database tier | General Purpose (balanced, remote storage), Business Critical (local SSD, built-in readable replica, in-memory OLTP), Hyperscale (up to 128 TB, fast scaling, named replicas) |
| Compute model | Provisioned for steady load; serverless (auto-pause, auto-scale) for intermittent load |
| Read scale-out | Send reporting to a read-only replica (ApplicationIntent=ReadOnly) |
| Compatibility level | Turns on newer optimizer behavior and features, such as level 170 for the latest T-SQL. Test before raising it in production |
| Database scoped configurations | MAXDOP, parameter sniffing, OPTIMIZE_FOR_AD_HOC_WORKLOADS, PREVIEW_FEATURES (SQL Server 2025 preview features) |
| Automatic tuning (Azure SQL) | FORCE_LAST_GOOD_PLAN on by default (reverts plan regressions); CREATE_INDEX and DROP_INDEX are optional |
Isolation levels
| Level | Dirty reads | Non-repeatable reads | Phantoms | Mechanism |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | No shared locks (NOLOCK) |
| READ COMMITTED (default) | No | Possible | Possible | Short shared locks, or row versions with RCSI |
| REPEATABLE READ | No | No | Possible | Holds shared locks until commit |
| SERIALIZABLE | No | No | No | Range locks (the most blocking) |
| SNAPSHOT | No | No | No | Row versions; a transaction-consistent view; update conflicts cause errors |
- Read Committed Snapshot Isolation (RCSI) is on by default in Azure SQL Database. Readers don't block writers and writers don't block readers, using row versions.
- SNAPSHOT gives a consistent view for the whole transaction, which suits long reports. Writers that touch the same row after the snapshot started fail with an update conflict (3960) and must retry.
- Optimized locking (Azure SQL) holds transaction ID locks and qualifies rows after acquiring locks, which reduces lock memory and blocking.
- Avoid
NOLOCKfor correctness-sensitive work: it can return dirty, duplicated or missing rows.
Concurrency controls
- Pessimistic: locks,
UPDLOCK/HOLDLOCKhints, orsp_getapplockfor app-level mutexes. - Optimistic: a
rowversioncolumn checked in theUPDATE ... WHERE RowVer = @old. If 0 rows are affected, someone else changed the row first. This is common in APIs. - Keep transactions short, access tables in a consistent order, and index the predicates so that locks stay narrow.
Key Concept: Azure SQL Database uses RCSI by default, so reads don't block writes. Use SNAPSHOT for consistent multi-statement reads (and handle update conflicts), and SERIALIZABLE only when phantoms must be impossible. Pick the tier and compute model that fit the workload.
Exam Traps
- Adding
NOLOCKeverywhere to "fix" blocking - Choosing SERIALIZABLE for reporting queries (heavy blocking) when SNAPSHOT gives consistency without locks
- Expecting SNAPSHOT writers never to fail (update conflicts must be retried)
- Choosing serverless for a database that is busy around the clock
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- SET TRANSACTION ISOLATION LEVEL, Microsoft Learn
- Transaction locking and row versioning guide, Microsoft Learn
- Optimized locking, Microsoft Learn
- vCore purchasing model: service tiers, Microsoft Learn
- Automatic tuning, Microsoft Learn