We are using SQL 2K with sp4. We are using Push method and both publisher
and distribution db are on the same server.
Recently we have quite a few deadlocks involving a replication's sp such as
sp_MSupd_xxxxxxx and some select statement from our application. We have
deadlocks from time to time but I haven't seen a deadlock caused by sp_MSupd
until now. Is this an indication of something that I need to look into?
Any help is very much appreciated.
Wingman
Sounds like a transaction on the publisher is coming over as a transaction
on the subscriber which you're deadlocking with. Some ideas: you could look
at the order of the transaction on the publisher and check that your
application accesses tables in the same order. You could allow your
application to do dirty reads (not generally recommended but sometimes
useful). You could use SQL Server 2005 and the snapshot isolation level
where this problem goes away. Finally you could control when the
distribution agent runs to avoid the lcash.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment