Skip to main content

Enable READ_COMMITTED_SNAPSHOT in SQL Server

By default, a SQL Server database uses lock-based isolation to maintain transactional consistency. In this model, readers (use SELECT) acquire shared locks, while writers (use INSERT, UPDATE, DELETE) acquire exclusive locks. Under concurrent access, this behavior can result in blocking (LCK_M_X waits, LCK_M_S waits), transaction timeouts, and hung threads when multiple transactions attempt to access the same data simultaneously. For example, multiple readers operations may block to a single writer (LCK_M_S waits) access.

To improve the performance of the SQL Server database under high load—where many concurrent transactions are opened—the READ_COMMITTED_SNAPSHOT option must be enabled in the database that transition the isolation from lock-based to row versioning-based isolation.

When the row versioning–based isolation is enabled via enabling the READ_COMMITTED_SNAPSHOT option, the following benefits are achieved:

  • The SQL Server database stores row versions in tempdb (system database) whenever data is modified.

  • Readers can access snapshot versions of data without waiting for locks.

  • Readers access never block writers, and writers access never block readers.

The row versioning–based isolation also preserves data consistency while significantly improving concurrency and performance under high load.

The following commands must be executed on the target SQL Server database prior to its deployment, to enable the READ_COMMITTED_SNAPSHOT option:

USE [YourDatabaseName];
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [YourDatabaseName] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
note

Failure to enable the READ_COMMITTED_SNAPSHOT option in the database may result into severe performance degradation under high concurrent load.

Modern ORM frameworks such as Spring and Hibernate often open multiple concurrent transactions; without this option enabled, lock contention can lead to timeouts or hung threads. Enabling this option removes such contention issues and significantly increases application throughput. Microsoft also recommends enabling the READ_COMMITTED_SNAPSHOT option for most OLTP databases to ensure stability and responsiveness under high load.