Skip to main content
Matrix42 Self-Service Help Center

Configuring Transaction Isolation Level Snapshot

  1. Last updated
  2. Save as PDF
How to activate SQL Server feature that helps reduce blocking scenarios in case of long-running queries and long-running transactions.

Snapshot Isolation Level

You can configure the Enterprise Service Management to use the SQL transaction isolation level snapshot when you browse your data with the UUX. Snapshot isolation is an SQL server feature that helps reduce blocking scenarios in case of long-running queries and long-running transactions.

Reduce UI locks

We know that in some environments long-running search requests or background processing can keep data in SQL tables blocked for a longer time. In very bad cases this kind of locks may be escalated to other tables and your whole application becomes unresponsive.

Snapshot isolation level has 2 positive effects on querying data.

  • Update locks that normally block any request no longer block, the request is simply answered with the version of your data before the update statement locks the data. In nearly all cases this is totally ok, as the web application anyway shows you only a snapshot of the data when you run your request.
  • Locks are not escalated by simply querying the data as queries running with snapshot isolation create no locks.

Snapshot isolation can't help in case you want to update data that is locked by another process in another transaction that also updates the same data. This will block the UI till the other process has finished its update transaction.

Possible effects on your SQL server and your databases

SQL server implements this feature by using a sort of version store. This is simply a space in your temp DB where SQL server will store the original version of your data as soon as an update changes any data. The engine has to add a pointer to your data that references this version store entries. This means your database has to be prepared to use snapshot isolation. This is done by configuring a property of your database. This will influence all update/insert/delete operations; the server has to use the version store even if no snapshot transaction is running, as it has to be prepared to expect such transactions.

You should expect increased usage of your Temp DB and also some increase in the size of your database as the pointers to the version store must be stored in the database.

You should also keep your SQL server load checked. Without snapshot isolation, bad running queries would result in blocks and the issue becomes obvious pretty fast. With snapshot isolation, these queries still run for a long time and consume resources on your SQL server. This can also influence the overall system performance as the base issue is not fixed.

In case you are executing stored procedures using a SQL Linked server the transaction level needs to be set directly at the beginning of the procedure by using this command

set transaction isolation level read committed

How to activate

Start the Configuration Wizard → Change Configuration → Configure Databases → check the Snapshot Isolation Level Enabled checkbox and Apply the change.

See also, Configuration Wizard: Configure Databases.

The system now configures your production and your archive database to allow snapshot isolation level and also changes the application configuration to use snapshot isolation level for UI queries.