we have three MSSQL 2005 servers (database is set to compatibility level 80 for legacy reasons) which we have just set up Transactional Replication w/ Updateable Subscriptions on. Two which are located in the US, and 1 located in Asia. Two of the 3 servers are busy servers and 1 is not. The one which is not is the Distributor and Publisher.
What we are seeing is that the table which is part of the Replication finds itself being locked by the sp_MSupd_dbo stored procedure. We notice this because other very simple select statements (just a lookup of the PK) are blocked during the execution time. This stored procedure is doing a simple UPDATE statement. This table has approx. 120,000 records in it. When running the sp_MSupd_dbo stored procedure manually, it seems to run very fast without issue. Additionally, the select statements which seem to get blocked are also very quick. We are seeing some locks as much as 20-30 seconds long.
Also, the majority of the traffic are these quick select statements which do a lookup straight from the primary key.
Has anyone else seen anything like this before?