What's the best way to update data in a table while it's in use without locking the table?

Question

I have a table in a SQL Server 2005 Database that is used a lot. It has our product on hand availability information. We get updates every hour from our warehouse and for the past few years we've been running a routine that truncates the table and updates the information. This only takes a few seconds and has not been a problem, until now. We have a lot more people using our systems that query this information now, and as a result we're seeing a lot of timeouts due to blocking processes.

... so ...

We researched our options and have come up with an idea to mitigate the problem.

1. We would have two tables. Table A (active) and table B (inactive).
2. We would create a view that points to the active table (table A).
3. All things needing this tables information (4 objects) would now have to go through the view.
4. The hourly routine would truncate the inactive table, update it with the latest information then update the view to point at the inactive table, making it the active one.
5. This routine would determine which table is active and basically switch the view between them.

What's wrong with this? Will switching the view mid query cause problems? Can this work?

Thank you for your expertise.

Extra Information

• the routine is a SSIS package that peforms many steps and eventually truncates/updates the table in question

• The blocking processes are two other stored procedures that query this table.

Answer 1

Have you considered using snapshot isolation. It would allow you to begin a big fat transaction for your SSIS stuff and still read from the table.

This solution seems much cleaner than switching the tables.

Answer 2

I think this is going about it the wrong way - updating a table has to lock it, although you can limit that locking to per page or even per row.

I'd look at not truncating the table and refilling it. That's always going to interfere with users trying to read it.

If you did update rather than replace the table you could control this the other way - the reading users shouldn't block the table and may be able to get away with optimistic reads.

Try adding the with(nolock) hint to the reading SQL View statement. You should be able to get very large volumes of users reading even with the table being regularly updated.