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?