2

Last few days I had major problems with my production site, turns out deadlock occurred, every half an hour maybe 50-60 of them. To do a quick fix in order to keep the site running I set : ALLOW_SNAPSHOT_ISOLATION ON

I believe my log file will grow faster than before now?

Are there different types of isolation and is it possible to set the isolation to just 2 tables which causes the deadlock?

::Update::

enter image description here

::2013-03-21 15:09::

xml graph image

Can I query the process ID's involved in the deadlock? select object_name() returns null always

7
  • That snapshot isolation setting doesn't turn on snapshot isolation by default; it has to be specifically requested using SET TRANSACTION ISOLATION LEVEL SNAPSHOT... Please post the deadlock graph XML and then we can help you solve the root problem.
    – Jon Seigel
    Mar 20, 2014 at 13:39
  • 1
    Monitor carefully your tempdb, as snapshot will use versionning which is stored in this system database. Have a careful read of brentozar.com/archive/2013/01/… if you didn't already. Mar 20, 2014 at 16:27
  • I couldn't get anything when setting sql profiler to filter "locks" so I enabled all sub sections ... Ina a whole list of Lock:Timeout events I found also Lock:Deadlock Chain...I will post the result in the question
    – ArxXx
    Mar 21, 2014 at 8:21
  • @JonSeigel Can I set the transaction level individually for certain tables ?
    – ArxXx
    Mar 21, 2014 at 8:36
  • No, the transaction isolation level is set in each session. Sorry, we need to see the XML behind the deadlock graph as it contains more information.
    – Jon Seigel
    Mar 21, 2014 at 13:44

2 Answers 2

1

Enabling snapshot isolation on a production server without testing the application first is rather a high risk strategy, but if you seem to have got away with it, congratulations.

The side effects of snapshot isolation tend to be to do with tempdb rather than the transaction log, see for instance http://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx.

The best thing to do is to look at the code that is accessing those two tables, and see if you can correct the problem there.

3
  • I have a separate database and application for testing purposes and I checked on them first. Regarding the code, it's update with linq
    – ArxXx
    Mar 20, 2014 at 10:11
  • Doesn't seem like it did much difference. Site is still very very slow. No deadlocks are being logged now, what could it be this time? Database server RAM is at max (default) and CPU is 1% but site isn't responding. Same situation on web server regarding the CPU and RAM
    – ArxXx
    Mar 20, 2014 at 11:29
  • Check your storage system to ensure you don't have a failed disk that is being rebuilt or worse yet a failed disk with no hot spare
    – Aaron
    Aug 4, 2015 at 22:50
0

I think your best friend in this situation is sp_whoisactive developed by Adam Machanic; you may get the same from Brent Ozar's website. First Responder Kit

I was having similar condition wherein I used to get frequent blocking and my application used to go in non-responding state. I didn't change isolation level directly rather I enabled only Read Committed Snapshot. Below is command to do that:

   --Query to check current state of database
SELECT name, is_read_committed_snapshot_on FROM sys.databases where name = ‘DB_NAME’

--Query to modify the database:

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE DB_NAME SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO

Please keep in mind that this change requires downtime at production server. Read committed snapshot Isolation enabling requires code change at times and could put you in trouble. If its working in your case then, its perfectly fine. you may read more about the same on Little Kendra's Website Isloation level by Kendra

I hope above info would be of help or you. In case you are using any database monitoring tool like Solarwinds DPA or Whitesands Proactive DBA; this could be of great help in terms of alerting.

As far as your second question goes - yes, you can change isolation level at session level however I am not very sure about table level.

You may refer my question on this forum at Database Blocking

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.