Tell me more ×
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.
up vote 2 down vote favorite

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

1 Answer

up vote 3 down vote

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

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.