Launch Deal · 50% off 1st month ·

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 NOLOCK for correctness-sensitive work: it can return dirty, duplicated or missing rows.

Concurrency controls

  • Pessimistic: locks, UPDLOCK / HOLDLOCK hints, or sp_getapplock for app-level mutexes.
  • Optimistic: a rowversion column checked in the UPDATE ... 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 NOLOCK everywhere 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.

See plans

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