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?