Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have an application database on Server A that is replicated to Server B using push transactional replication. Reporting is done on the replicated copy on Server B.

We are having issues where the replication process, which obtains a shared with intent exclusive (SIX) lock, deadlocks with SELECT statements, which obtain an Intent shared (IS) lock. The SELECT statement is chosen as the deadlock victim, the query is terminated, and the report developer isn't happy.

Where should I fix this? Should we add NOLOCK hints to the SELECT statement? Change some replication setting? Is there a bigger issue, like poor database design, at work here?

share|improve this question
2  
SIX and IS are compatible, so I must question your analysis. Can you post the actual deadlock graph? –  Remus Rusanu Jul 26 '12 at 17:52
    
Remus - I cannot post the actual graph, but I did look at the trace again. It now appears to me that the SELECT statements are S and the replication is IX. I am digging more to validate. –  JHFB Jul 26 '12 at 18:20
    
Consider letting the report developer use a lower isolation level, or simply not run reports during replication processing. –  Emmad Kareem Jul 26 '12 at 19:59

2 Answers 2

While I've never tested this, I would try turning on snapshot isolation on the reporting database. If all the existing queries run using READ COMMITTED (the default), turning on READ_COMMITTED_SNAPSHOT could fix the problem with a flick of the switch.

Using one of the snapshot isolation levels should eliminate the deadlocks completely as the single writer won't block the reader. As a nice byproduct it should also reduce the possibility of inconsistent data ending up in the reports due to concurrently-applied replication changes.

share|improve this answer
    
I really hate to tell you but there is a serious chance that the developer is not using read commited but the REAL default which is SERIALIZABLE. If this is done in any more modern environment and gets actually written up with and as a DTC or something this is sadly the default. I never have seen a single sql statement with Read COmmited actually deadlock.... not with only selects. –  TomTom Dec 16 '13 at 15:29
    
@TomTom SELECTs running under READ COMMITTED embrace in deadlocks with modifications all the time: sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/… –  AlexKuznetsov Dec 16 '13 at 17:32
    
@TomTom: This question deals with a single writer (Replication) and concurrent readers. I don't follow? –  Jon Seigel Dec 16 '13 at 18:15

The report developer should learn more SQL and use a READCOMMITED transaction so he is not actually leaving ANY locks in the database.

When reports are programmed smartly, then in likely 80% of the cases they do not need to maintain any locks as they neither want repeatable reads (useless if you read only once then end the transaction anyway) nore do any updates anyway.

I would like tell Mr. Not Happy Developer to fix his side of the game ;)

NOLOCK is evil - the problem with NOLOCK is that if updates happen in the data, and thus rtrigger things like index splits, you can get he same row MULTIPLE TIMES - i.e. you are not guaranteed consistent data. THis really will possibly piss off some users of the reports. They should pretty much NEVER be used ;)

share|improve this answer
    
Reading the same row multiple times (AKA inconsistent analysis) is also possible in READ COMMITTED. You have to move up to at least REPEATABLE READ or one of the snapshot isolation levels to eliminate that possibility. –  Jon Seigel Dec 16 '13 at 18:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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