SQL Server by default adheres to ACID database rules. The A in ACID stands for atomic, meaning that the whole transaction happens, or none of it does. The I in ACID stands for isolation, meaning that transactions are isolated from one another, so either all the results from one transaction are visible to another transaction, or none of them are. This is accomplished in SQL Server by using locks for both reads and writes (at least by default). Reads (
SELECTs) obtain a read lock, which prevents any write (
INSERT) operations on those items (or pages, or the whole table depending on what SQL's black magic lock escalation algorithm decides on). In order to preserve Isolation, read locks for queries must be held until all results have been sent or buffered. This prevents the writes from obtaining their locks (or rather causes them to wait to obtain them), unless the writes are otherwise isolated (see other answer about snapshot isolation). If not, some of the results from a subsequent write could end up in the results of the query that occurred before the write.
There are several solutions for this, each with trade-offs:
- Buffer results on the client side (read all rows immediately as quickly as possible and do further processing later). This may require lots of client-side memory if there are lots of results, and will still block updates until the client is done buffering (which is why it must be completely buffered before any subsequent processing).
- Buffer results on the server-side. You can do this by selecting the data into a temp table and then returning the temp table instead of the original query. This will require server-side memory and/or disk and could therefore interfere with other server-side operations.
WITH (NOLOCK) on the
SELECT of the read operation. This tells SQL Server to ignore isolation for this read, which means that you could get the original version of a row at the beginning of the results and a modified version of another row later in the results when both rows were modified by the same transaction and the versions returned were never in the database at the same time (ie. the rows were modified during the query, after the first row was returned but before the second row was). This answer has the least performance cost, but produces non-ACID results by breaking Isolation and/or Atomicity (depending on how you look at it).
- Use snapshot isolation (see other answer--I'm not an expert with snapshot isolation). Note that I've had significant performance issues with snapshot isolation, and it will require plenty of server-side resources as well.
There may be other solutions as well, but these are the most common.