Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Oct 10 '11 at 11:16

This question came from our site for professional and enthusiast programmers.

2 Answers

The problem of blocking sessions has been already discussed a few times.

I'm sure you can find some good references in the following questions:

First you need to find if you truly have a blocking issue. That can be easily found ad-hoc by using Adam Machanic's WhoIsActive stored procedure (this has to be run manually when you believe that your specific session is blocked or can be automated) or some other report that shows blocking sessions. In order to have a better understanding of the times and durations of the blocking situations you would better prepare some server side traces that will gather needed information about blocked sessions.

Details about finding and fighting blocking you can find in this answer.

share|improve this answer
add comment

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
share|improve this answer
    
We tried this - but it didn't seem to help. Also, when we put it passed the infrastructure chaps, the DBAs we not happy with enabling snapshot isolation - as it has to be enabled DB wide. I will loom into it again, but the DBAs were saying we needed to do more analysis into this option. –  cdotlister Oct 9 '11 at 2:10
2  
"We tried this - but it didn't seem to help" - Suggest you include this important information in your question. What is your tempDB configuration? Have you benchmarked your I/O subsystem(s)?? –  Mitch Wheat Oct 9 '11 at 2:11
    
Thanks Mitch - my testing wasn't very long... So, I will try again on Monday. But they will be asking what impact it would have on the rest of the system. Also, does the symptoms I mention above sound like locking issues caused by the large transaction? –  cdotlister Oct 9 '11 at 4:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.