Database locking issues?

I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type work (Adding record, modifying, viewing etc) via an ASP.Net website.

Each insert and update is done in it's own Transaction, handled by the data access layer. The database isolation, I believe, is set to Read_Commited.

All is working fine.

However, a new module has been added, which polls a separate database for information. If there is new info, a process starts a new transaction, and the uses the same data accessor code to read from our database, as well as reading from another separate database for the new info. It then does loads of checks to see what it must do with the new data... And the starts doing loads of updates or inserts into our database. This is all within a large transaction. All inserts and updates from both the UI application, and the polling service, go through the same CRUD procedures. Because an incoming message to be processed can hold a lot of entitys that need updating, the time for a transaction to complete can be between a split second, and a minute.

What we're finding though, is that when a larger message is processed, the UI locks up, and can lock for a user for 3 minutes.

So, we thought that maybe adding 'NOLOCK' hints to the selects may assist. It didn't. Well, it may have helped a bit, but the lockups are still happening.

I thought that the cause may be that the message arrives, and a transaction is started, which is locking other transactions from working (Even SELECT statements, which I don't understand). Profiling the database shows that even simple selects are taking ages to complete on the UI (Simple, such as SELECT fields FROM SingleTable WHERE PrimaryKey = Value

Our indexes seem OK... We do have Triggers on all tables which simply copy updates and inserts into an AUDIT database table. Don't think they're the problem.

I thi

Try turning on Read Committed Snapshot isolation (RCSI) (but be aware this will put increased pressure on your tempDB, which should ideally be on its own set of dedicated physical spindles).

There are two 'snapshot' levels available in SQL Server 2005 onwards: READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes; whereas SNAPSHOT ISOLATION performs optimistic reads and optimistic writes. Suggest you try RCSI.

Enabling Row Versioning-Based Isolation Levels

To alter this setting, you need to switch to single-user mode to ensure there are no queries in flight (which would then fail):

ALTER DATABASE dbname 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
GO

ALTER DATABASE dbname
SET READ_COMMITTED_SNAPSHOT ON; 
GO

ALTER DATABASE dbname
SET MULTI_USER; 
GO

StackExchange Database Administrators Q#6660, answer score: 4