Sql-server – Database locking issues

performancesql-server-2008

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 think it';s because of the transaction around the message processing, which is locking the UI out.

Can anyone maybe share an experience or tell me where I can look to see why we're getting UI lockouts? The UI should have priority. Message processing is a background thing… the users need to be priority… but it seems the messages are locking the database… and we're unsure if the UI ever locks the message processing.

Hope someone can assist. I can provide as much info as possible to help.

Best Answer

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
Related Question