Subsonic: Select on a View, locks the table update?

Question

Hi, I have a Web site live and running now. I am using the Subsonic to handle the database connections etc. I am getting time out expired error while updating a table (say Employee). When I check sp_who2, I see the suspended connection for the PID which is updating with a block by anothor pid, so I run the profiler and found out when ever this suspended connection occur, the blocked pid is a select statement on the view (say ActiveEmployees, which is the same as the table but with some where conditions).

Anyone know why a Select statement on the view could cause failure in update. If it is other (like select fails due to update) may be reasonable. Is there any way for me to make select on a view without locking the table?

PS: I am using the Sql server 2005 and subsonic 2.2.

Thanks

Answer 1

You might add with(nolock) hint to the select statement in the view if you don't care about accuracy of the returned data (it will return uncommited rows possibly). We encountered timeouts also when the select statements where scanning a table that other thread was inserting into. I resolved the issue by adding appropriate index that is used by our select.

Answer 2

How to add the nolock in subsonic select statement?