Best practice to prevent blocking on a highly readable desk?
I have a large database (~ 4GB), with two large tables (~ 3M records) with ~ 180K SELECTs / hour, ~ 2k UPDATEs / hour, and ~ 1k INSERT + DELETE / hour.
What would be the best way to ensure there are no locks for read tasks on insert / update / delete?
I thought about using the NOLOCK hint, but there is so much talk about it (it's good, it's bad, it depends) that I'm a bit lost. I must say I tried this in a dev environment and I didn't find any problems, but I don't want this in production until I get some feedback ...
UPDATE : I clarify this in the comment I received - due to the business involved with this app, I don't mind if the SELECTed entry is out of date due to the simultaneous delete / update. Next reading will return a new one and be fine.
UPDATE 2 . In this application, when a record is inserted or updated, it is very likely to be intact for at least a month. (so maybe I could split the table according to the "lastUpdated" column?)
Thanks! Luigi
a source to share
The problem with the NoLock hint is that the technical data can change when you read the spreadsheet. Your data may be "wrong" if the update or insert happened at the same time. Depending on your scenario, you will probably be fine using it, but you will have to try it and see. We use it all the time and have not found any problems with it, but your mileage may vary depending on your situation.
a source to share
My suggestion would be to use the NOLOCK hint. But in case you are not comfortable, my suggestion is to split the table. The table sizes you mentioned are enormous. You could split them into sections, and then the reads won't conflict with updates if they're on different sections.
a source to share
You can look at using Version Based Row Based Isolation Levels , such as Snapshot Isolation, or Reading Fixed Isolation Using Row Versioning.
Version based isolation levels improve read concurrency by eliminating locks for read operations.
However, as concurrency increases, there is an increase in resource usage to support row versions, so you will need to define the behavior of your application with these isolation levels.
a source to share