How do you update a table with a large number of updates, while maintaining the availability of the table for a large number of users?

There is more than one way to approach this issue. To give an exact answer with code that would work for you, I would need to know what database you are using and the layouts of your tables. But generally, this approach works for me:

I have a similar table which is very large and all my users rely on. I can insert around 5,000 rows to this table before it locks for more than one second, which I consider unacceptable. I figured out this limit by trial and error. I tried to insert 1,000 rows, 2000 rows, 10,000 rows, etc. I played around with updates until I figured out what the limit was before the DB started to page data and lock the table for more than a second.

This limit will be different for you, depending on how large your table is, how many indexes you have, how many columns etc. Trial and error is pretty much the only way to figure out where your table's particular limit is.

Once you know how many rows is the max you can insert without locking the table (you will always lock it, the point is not to lock it for "too long") then you can simply perform your update in chunks.

So lets say your update limit was 1,000 rows. (always better to go too low than too high with this number)

• Gather the updates you want to do into a temporary table with a RowID, call it #Updates
• Create another temporary table just to hold RowIDs, call it "#Done"
• Start a loop which runs until there are 0 rows in #Updates which aren't in #Done
• Select the top 1,000 rows from #Updates which don't appear in #Done, ordered by RowID and insert them to your table
• Add the top 1,000 rows from #Updates which don't appear in #Done, ordered by RowID and insert them to #Done
• Count how many rows are left
• Loop round and do it again until there's nothing in #Updates that doesn't appear in #Done

Here's some code that does this in SQL Server:

--Variable needed for the loop
  DECLARE @RowsLeft integer

--Create a temp table to hold all your updates (define the columns you actually need)
 CREATE TABLE #Updates (MyUpdateValue varchar(100),
                        RowID integer IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED) --Put a RowID on it and make it the Clustering Key

--Just a dummy query, whatever the source of your update is should go here
 INSERT INTO #Updates WITH(TABLOCKX) (MyUpdateValue)
      SELECT MyUpdateValue 
        FROM Mydatabase.dbo.SourceOfUpdates
       WHERE Conditions = 'True'

--Create a temp table to hold RowIDs of updates you've already inserted into your big table
CREATE TABLE #Done (RowID integer NOT NULL PRIMARY KEY CLUSTERED)

--Count how many rows of updates you have
     SELECT @RowsLeft = COUNT(*) 
       FROM #Updates

--Start a while loop that ends when all your updates are done
      WHILE @RowsLeft > 0
      BEGIN
            --Do the first 1000 updates which aren't in #Done
            --(Replace 1000 with the number that your database / table can handle without locking up)
            INSERT INTO MyBigImportantTable (MyUpdateValue)
                 SELECT TOP 1000 MyUpdateValue
                   FROM #Updates a
              LEFT JOIN #Done b ON a.RowID = b.RowID
                  WHERE b.RowID IS NULL
               ORDER BY a.RowID

            --Insert them into #Done, again replace 1000 with the right number for your particular situation
            INSERT INTO #Done
                 SELECT TOP 1000 a.RowID
                   FROM #Updates a
              LEFT JOIN #Done b ON a.RowID = b.RowID
                  WHERE b.RowID IS NULL
               ORDER BY a.RowID

            --Count how many rows of updates remain
                 SELECT @RowsLeft = COUNT(*) 
                   FROM #Updates a
              LEFT JOIN #Done b ON a.RowID = b.RowID
                  WHERE b.RowID IS NULL
               ORDER BY a.RowID
        END