Deadlock Solution:SnapShot Isolation Level

Question

Deadlock Solution:SnapShot Isolation Level

Alkesh Khedle

Right there with Babe

Points: 780
More actions
March 27, 2008 at 12:18 am

#183647

Hi we are constantly getting deadlocks on some part of database.Can we apply Snapshot Isolation level at table level or we need to defined it on database level.Is that a good solution or we have other option available to prevent Deadlocks?
Is there any way to resolve the Deadlock Issue in DATABASE. Is Snapshot Isolation Level is the Good Way to minimize Deadlocks Occurrence?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Snapshot isolation probably will fix the deadlocks, but you do need to check and make sure it will not break any code. You also have to take the impact on TempDB into account and, if you're using full snapshot as opposed to read-committed snapshot, you need to consider update conflicts and to change all your code to set the isolation level.

Your other option for deadlocks is to find the cause (use traceflag 1204 or 1222 to write the deadlock graph into the error log) and change the code or the indexes to prevent deadlocking.

If you need help with interpreting the dealdlock, or changing the code, post here. Tehre are several people here who can help.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

I haven't found any situation where it will break code, but it won't do anything if you aren't in transactions.

ALTER DATABASE SomeDB

SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE SomeDB

SET ALLOW_SNAPSHOT_ISOLATION ON;

SELECT

sd.is_read_committed_snapshot_on,

sd.snapshot_isolation_state,

sd.snapshot_isolation_state_desc

FROM sys.databases AS sd

WHERE sd.[name] = 'SomeDB';

Bob Fazio SSChampion Points: 10794 More actions · Answer 3

Also, I have seen a performance hit of roughtly 5% OVER the normal hit that being in a transaction will give you.

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

Alkesh Khedle (3/27/2008)
Hi we are constantly getting deadlocks on some part of database.Can we apply Snapshot Isolation level at table level or we need to defined it on database level.Is that a good solution or we have other option available to prevent Deadlocks?

At DB level only. Can't do it by table.

Is there any way to resolve the Deadlock Issue in DATABASE. Is Snapshot Isolation Level is the Good Way to minimize Deadlocks Occurrence?

It has helped us significantly in some of our databases. No issues at all really.

jswong05 Hall of Fame Points: 3553 More actions · Answer 5

Once you use trace flag and find out the deadlock transactions, you probably click right-away. (provided you understand SQLServer concurrency and locking). You can send that transaction to snapshot isolation level.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

(provided you already did ALTER DATABSE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION on;)

or if this is under your control, like we had in our case,

reschedule two competeing jobs not on the same timing.

Jason
http://dbace.us
😛

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please note, two-year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass