question

Leo avatar image
Leo asked

Setting Isolation Level ON

Hi, ALLOW_SNAPSHOT_ISOLATION READ_COMMITTED_SNAPSHOT Are they save to use both on Production Server to reduce the Deadlock? Do I need to run both query below or just Read_committed_Snapshot will be good enough for reducing Deadlock? ALTER DATABASE [DatabaseName] SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE [DatabaseName] SET READ_COMMITTED_SNAPSHOT ON Also do I need to change my code in stored procedures? Thanks
sql-server-2008deadlock
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
I have used SET ALLOW_SNAPSHOT_ISOLATION ON to mitigate deadlocking, but I tend to use it on a procedure-by-procedure basis, using `SET TRANSACTION ISOLATION LEVEL SNAPSHOT` within the code rather than setting it as the database default. Because row versions are stored in tempdb, I didn't want to just throw everything in there; only specific procedures that were having issues. If you set `READ_COMMITTED_SNAPSHOT` as the database default, you may still have to change stored procedure code. For example, I knew a DBA who coded each and every stored procedure he wrote to use the SQL Server default isolation level: `SET TRANSACTION ISOLATION LEVEL READ COMMITTED;` In this case, setting the database default will not help as the procedure code would override the `READ_COMMITTED_SNAPSHOT` default.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Ken, Thanks for your answer. Which stored procedure should I use for 'SET TRANSACTION ISOLATION LEVEL SNAPSHOT' ? In SELECT stored procedure or INSERT stored procedure? Thanks
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
To be able to use the SNAPSHOT isolation level you have to alter the database and `SET ALLOW_SNAPSHOT_ISOLATION ON`. Until this is enabled on database level, you are not able to use the SNAPSHOT isolation and doesn't matter whether you want to set it globally on database level or locally in queries or stored procedures. As @KenJ noted if you enable the SNAPSHOT on database level, then you will not need to modify the stored procedures only if there isn't set some other isolation level locally. If the stored proc as using a locally set isolation level, then you have to alter your stored procedures as @KenJ noted.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
It is my belief, and experience, that 99% of deadlocks are avoided with coding and indexing modifications, NOT changing the isolation level. You can minimize deadlocks with some isolation levels, but at the expense of concurrency and performance.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Excellent point. I neglected to mention how rare of an event this is - I've only tweaked the isolation level about 4 times in the same number of years.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.