Where should I fix the deadlock?

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?

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.

StackExchange Database Administrators Q#21570, answer score: 6