Replication Locks and ASP | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Replication Locks and ASP

Apologies if I’m posting to the wrong place – I’m a newbie to this! I have an asp classic application which allows users to update records in a database. The database is constantly being replicated to using transactional replication from another database – generally this works fine however at busy times of the day users occasionally find that the asp pages gives them a timeout error – this persists for a few minutes and is then ok again. I am almost certain that this is due to locks by replication. I am uncertain how replication locks records and whether we can specify that it only locks to record level as opposed to page/table etc. It is unlikely that the record being updated is one thats currently being updated by replication so I’m guessing that replication locks pages? I could extend the timeout period on the asp application however as the record appears to be locked for a several minutes I dont want the application hanging around that long. Any advice would be gratefully received.
I assume your ASP app is just reading from this table and it’s the reads timing out? Transactional replication goes through the normal lock escalation model. It would never intellionally come and hold a table lock to just update a few rows in any table of noticable size. If your source table (one the publisher) is receiving large update statements then this could well be the source of the problem as replication will open a transcation as it updates the rows – depending on the number of rows this could take a while. Individual row updates shouldn’t be noticeable at all. A work around in your ASP app (or stored proc) would be to use (nolock) after the table name. This will ignore locked records which should be ok if you think the records changing are not records your users are looking at. If you prefer not to use this option you need to look at improving your replication performance, maybe at disk level. If you want to monitor the blocking use EXEC sp_who2 active to get a list of active SPID, find the replication SPID. Then run EXEC sp_lock <SPID Number> To see what locks replication is taking. Hope this helps.