Best Practice for using SNAPSHOT Isolation Level

Question

Right now our company databases are set to default "READ COMMITTED".

When I run a T-SQL update script, I'd like to tighten up transaction updates. I'm interested in the SNAPSHOT isolation level, but this is a database-level change.

How will it impact others users and their work? I only want to protect my script and my updates, but not necessarily other sessions with read/update/write.

I'm new at this. Special thanks to Erland for helping me think through this!

Answer 1

Although the SNAPSHOT isolation level is controlled at the session level, it is permitted only when the ALLOW_SNAPSHOT_ISOLATION database option is turned on. Once the database option is turned on, additional overhead is incurred of additional storage space (14 bytes per row) plus increased space/usage in tempdb for the row version store. The overhead may be offset by the concurrency benefits of reduced locking/blocking/deadlocking, depending on your workload.

How will it impact others users and their work? I only want to protect my script and my updates, but not necessarily other sessions with read/update/write.

As I understand your other question, you run a SELECT query and then iterate over the results to update rows. Assuming your script is running in a SNAPSHSOT isolation level transaction and the other users are using default READ_COMMITTED (without RCSI), your SELECT query will return rows as they existed at the time the SELECT query started. The query will 1) not return rows inserted during SELECT query execution, 2) return the before value of rows updated during query execution, and return rows deleted during query execution.

If you attempt to UPDATE a row which has been modified or deleted by another session, you'll get the dreaded update conflict error below and you can simply rerun your script.

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.YourTable' directly or indirectly in database 'Example' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Other READ_COMMITTED sessions that attempt to access a row modified by your transaction will block until your transaction commits because normal locking behavior applies. The impact to these other sessions will be insignificant if the blocking duration is short (i.e. your transactional script runs quickly). If it runs a long time and you've already optimized the query/indexes, you can mitigate impact to other sessions by processing in multiple smaller batches (e.g. a TOP clause in the select query) instead of one large transaction.

Answer 2

Thanks for your question.

If my understanding of your question is correct, it seems like you're interested in implementing SNAPSHOT isolation level for your query. If I've misunderstood your question, please forgive me.

If you want to use the SNAPSHOT isolation level for a specific transaction in T-SQL, you can use the SET TRANSACTION ISOLATION LEVEL statement to set the isolation level for the current transaction.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Please find the syntax in the document below.

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16#syntax

Please refer below document as well which has explanation with an example

https://www.sqlshack.com/snapshot-isolation-in-sql-server/

Let us know if this helps!

Answer 3

Hi @Coreysan ,

Here are some potential impacts of changing the isolation level to SNAPSHOT:

• Increased disk space usage: When SNAPSHOT isolation is enabled, SQL Server creates and maintains versioned copies of data for each transaction. This can result in increased disk space usage and potentially impact the performance of other transactions.
• Increased memory usage: SQL Server must maintain the versioned copies of data in memory, which can result in increased memory usage and potentially impact the performance of other transactions.
• Changes in query results: SNAPSHOT isolation can cause changes in query results because transactions may see data as it existed at the start of the transaction, rather than the current state of the database.
• Blocking and deadlocks: SNAPSHOT isolation can result in blocking and deadlocks because transactions may be holding locks on versioned data that other transactions need to access.

Understand the impact: SNAPSHOT isolation level can impact performance, disk space, and memory usage, as well as potentially impact query results and cause blocking and deadlocks.

• Test in a non-production environment: Before implementing SNAPSHOT isolation level in a production environment, it is important to test it in a non-production environment to understand how it will impact performance and functionality.
• Use at the transaction level: To avoid impacting other users, it is recommended to use SNAPSHOT isolation level at the transaction level rather than at the database level. This can be done using the SET TRANSACTION ISOLATION LEVEL statement.
• Monitor performance: When using SNAPSHOT isolation level, it is important to monitor performance and disk space usage to ensure that it is not impacting other users or causing performance issue.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".