Deadlocks during synchronization

We use SQL Server 2000 SP4 with merge replication enabled. The master site
replicates all changes to at multiple subscribers, but we encounter some
problems due to deadlocking issues.
Some actions produce a lot of new data that must be replicated. The table,
in which the data is inserted, has a trigger defined that must be enabled for
replication. Inserting a record into this table takes 70-350ms during normal
operation. It goes through several complex calculations that have been
optimized pretty well.
When the merge agent starts replicating it often receives a deadlock when
inserting data in these tables. After this deadlock it becomes very slow. It
enlists all further records for retrying (Unable to synchronize row due to
unknown reason) and inserts that do get through have durations of over
35.000ms! Of course, this severly hurts replication performance.
I think the triggers for replication are the real probleme due to locking
issues. These calculations need to be performed even when data is replicated
and I don't know another method then using a trigger. Does anyone have a
suggestion?
Greetings,
Ramon de Klein
The replication triggers do cause increased latency of operations on
replicated tables.
Do you have real time requirements for the data that these triggers
calculate? You may want to evaluate having this calculation being performed
in a batch.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ramon de Klein" <RamondeKlein@.discussions.microsoft.com> wrote in message
news:2820F1E2-B4B2-4140-BABC-26DC753D69EB@.microsoft.com...
> We use SQL Server 2000 SP4 with merge replication enabled. The master site
> replicates all changes to at multiple subscribers, but we encounter some
> problems due to deadlocking issues.
> Some actions produce a lot of new data that must be replicated. The table,
> in which the data is inserted, has a trigger defined that must be enabled
> for
> replication. Inserting a record into this table takes 70-350ms during
> normal
> operation. It goes through several complex calculations that have been
> optimized pretty well.
> When the merge agent starts replicating it often receives a deadlock when
> inserting data in these tables. After this deadlock it becomes very slow.
> It
> enlists all further records for retrying (Unable to synchronize row due to
> unknown reason) and inserts that do get through have durations of over
> 35.000ms! Of course, this severly hurts replication performance.
> I think the triggers for replication are the real probleme due to locking
> issues. These calculations need to be performed even when data is
> replicated
> and I don't know another method then using a trigger. Does anyone have a
> suggestion?
> --
> Greetings,
> Ramon de Klein
sql