liveBook · Manning

Optimistic isolation level

As explained in 100 SQL Server Mistakes and How to Avoid Them

An optimistic isolation level uses row versions or similar mechanisms to reduce reader-writer locking rather than enforcing extensive blocking locks.

Mistake: treating optimistic isolation as a magic bullet. SQL Server uses row versions in TempDB so readers and writers can avoid much of the locking and blocking found with pessimistic isolation, but the tradeoff is additional I/O and disk usage. section 10.12

Use these checks before choosing an optimistic isolation level:

  • Consider Read Committed Snapshot for Read Committed behavior and Snapshot for Serializable-equivalent protection against data anomalies. section 10.12
  • Check whether TempDB has spare I/O capacity and free disk space; busy systems may incur substantial version-store overhead. section 10.12 section 10.14
  • Prefer diagnosis over adding hardware, and consider optimistic isolation when lock contention causes performance problems. section 10.14

MarketingArchive configuration

Enable database-level behavior with ALTER DATABASE MarketingArchive SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT, or permit Snapshot isolation with SET ALLOW_SNAPSHOT_ISOLATION ON. Inspect stored row versions through sys.dm_tran_version_store. section 10.12

Listing 10.30 Enabling Read Committed Snapshot
ALTER DATABASE MarketingArchive 
   SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT ;

Shows the T-SQL command for enabling Read Committed Snapshot on a database. section 10.12

Listing 10.31 Enabling Snapshot isolation
ALTER DATABASE MarketingArchive 
   SET ALLOW_SNAPSHOT_ISOLATION ON ;

Shows the T-SQL command for allowing Snapshot isolation on a database. section 10.12

Listing 10.33 Examining the Version Store
    SELECT *
   FROM sys.dm_tran_version_store ;

COMMIT

Shows how to examine row versions in the Version Store. section 10.12

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains row-version storage, isolation variants, and resource tradeoffs, but stops short of detailed concurrency internals.
Examples
Demonstrates database configuration and Version Store inspection using MarketingArchive and ImpressionsArchive T-SQL listings.
What it assumes
Assumes basic SQL Server transaction knowledge while distinguishing pessimistic and optimistic isolation from their operational effects.
What you can do after
Operate/configure Read Committed Snapshot or Snapshot isolation and inspect Version Store usage.
How it connects
Connects optimistic isolation with pessimistic isolation, Read Committed, Serializable, TempDB, locking, blocking, and transaction isolation.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.