Best practice for avoiding locks on a heavily read table?

Question

Hi,

I have a big database (~4GB), with 2 large tables (~3M records) having ~180K SELECTs/hour, ~2k UPDATEs/hour and ~1k INSERTs+DELETEs/hour.

What would be the best practice to guarantee no locks for the reading tasks while inserting/updating/deleting?

I was thinking about using a NOLOCK hint, but there is so much discussed about this (is good, is bad, it depends) that I'm a bit lost. I must say I've tried this in a dev environment and I didn't find any problems, but I don't want to put it on production until I get some feedback...

UPDATE: I clarify this upon the comment received -- because of the business dealed with this application, I don't mind if a SELECTed record is old because of being deleted/updated in simultaneous. Next read will return the new one and that's fine.

UPDATE 2: In this application, when a record is inserted or updated, then it is very probable to keep intact for a month at least. (so maybe I could partition the table according to the 'lastUpdated' column?)

Thank you! Luiggi

Answer 1

The problem with the NoLock hint is that technically the data could change while you're doing read on a table. Your data could be 'incorrect' if an update or insert occurred at the exact same moment. Depending on your scenario, you're probably going to be ok using it, but you'll have to try it and see. We use it all the time, and haven't found any problem with it, but your mileage may vary depending on your situation.

Answer 2

My suggestion would be to use NOLOCK hint. But in case you are not comfortable, my suggestion would be to partition the table. The table sizes that you have mentioned are huge. You could have them partitioned and then the reads would not conflict the updates if they are on different partitions.

Answer 3

Based on the UPDATED part of your question, NOLOCK is fine.

Answer 4

You could take a look at using Row Versioning-based Isolation Levels such as snapshot isolation or read committed isolation using row versioning.

Row versioning-based isolation levels improve read concurrency by eliminating locks for read operations.

However with the increased concurrency comes increased resource usage to maintain the row versions so you would need to determine the behavior of your application with these isolation levels.

Answer 5

When using NOLOCK please also check the following blog (not only dirty reads are possible): http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx