Databases
StackOverflow2013
Postsdbo
POWhat's the best way to update data in a table while it's in use without locking the table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat's the best way to update data in a table while it's in use without locking the table?
    text
    copied!<p>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. </p> <p>... so ...</p> <p>We researched our options and have come up with an idea to mitigate the problem.</p> <ol> <li>We would have two tables. Table A (active) and table B (inactive).</li> <li>We would create a view that points to the active table (table A).</li> <li>All things needing this tables information (4 objects) would now have to go through the view.</li> <li>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.</li> <li>This routine would determine which table is active and basically switch the view between them.</li> </ol> <p>What's wrong with this? Will switching the view mid query cause problems? Can this work?</p> <p>Thank you for your expertise.</p> <p><em>Extra Information</em></p> <ul> <li><p>the routine is a SSIS package that peforms many steps and eventually truncates/updates the table in question</p></li> <li><p>The blocking processes are two other stored procedures that query this table.</p></li> </ul>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload