Inserts and updates are two of the most common operations in SQL Server that cause locking. Updates usually cause row-level locking, while inserts can cause a whole table to be locked, especially if the table has an explicitly-defined primary key. If your database routinely involves mass updates or inserts, it's hard to keep the locks from stopping everything cold—especially if the table in question is used often.
One way to avoid locking tables during these insert operations is to subdivide a bulk insert operation into smaller operations. Depending on how the database is accessed and managed, there are a few ways to do this: through a specially-constructed stored procedure, or through an ASP page (or some other programmatic interface). The first may be more complicated to program, but may be easier to work with, since the same sort of work wouldn't have to be redone in other contexts—in other words, no matter what programming language or interface used, you wouldn't need to rewrite the progressive-insert functionality.
With a stored procedure, the data to be inserted could be loaded into a temporary table, and then gradually inserted into the target table by using a WAITFOR command between each insert operation. At the end of the operation, provided everything inserted successfully, the temporary table can simply be dropped. By contrast, a scripted page or standalone program could simply perform the insert gradually, with a forced pause
A third option is to schedule a mass insert or update for a time when the database would normally be offline for maintenance, or when there would be few users active. This could be done either through one of the mechanisms described above to prevent timeouts, or via the DTS Import Wizard if the whole thing is being done in a one-time fashion.
The records to be inserted could be subdivided in various ways. For instance, a large number of records with a time/date stamp could have each day's or month's worth of data bulk-inserted separately.
(Additionally, if it is not crucial to see the most current committed data, SELECT statements that use the tables in question can use NOLOCK or READ UNCOMMITTED to reduce the amount of locking that does take place.)
Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!
This was first published in March 2005