Table locked during inserts... website unusable (sql server 2008)

Question

I have a background process that is constantly inserting into a table in real-time. It could be thousands of inserts an hour (with a few updates). This same table is used by the web application to get some data needed to display to the user.

Currently whenever the background process runs its inserts/updates, the web app seems blocked and cannot access the database.

I'm wondering what a good solution could be. Should I create another table where I insert into and than copy the table over to another table that is read only? Is there another method?

Any help and suggestions is greatly appreciated!

Answer 1

Especially for SQL Server you might want to change your database to turn READ_COMMITTED_SHAPSHOT on.

This basically prevents your select queries from locking up if there are pending inserts/updates on the data.