Sql – Subsonic: Select on a View, locks the table update

asp.netlockingsql serversubsonic

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.

Best Answer

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.

Related Solutions

Sql-server – Hidden Features of SQL Server

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff.

SQL update from one Table to another based on a ID match

I believe an UPDATE FROM with a JOIN will help:

MS SQL

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

MySQL and MariaDB

UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;