Concurrency during long running update in TSQL

Question

Using Sql Server 2005. I have a long running update that may take about 60 seconds in our production environment. The update is not part of any explicit transactions nor has any sql hints. While the update is running, what's to be expected from other requests that occur on those rows that will be updated? There's about 6 million total rows in the table that will be updated of which about 500,000 rows will be updated.

Some concurrency concerns/questions:

1) What if another select query (with nolock hint) is performed on this table among some of the rows that are being updated. Will the query wait until the update is finished?

2) What the other select query does not have a nolock hint? Will this query have to wait until the update is finished?

3) What if another update query is performing an update on one of these rows? Will this query have to wait until it's finished?

4) What about deletes?

5) What about inserts?

Thanks! Dave

Answer 1

Every statement in sql server runs in a transaction. If you don't explicitly start one, the server starts one for every statement and commits it if the statement is successful, and rolls it back if it is not.

The exact locking you'll see with your update, unfortunately, depends. It will start off as row locks, but it is likely that it will be elevated to at least a few page locks based on the number of rows you're updating. Full elevation to a table lock is unlikely, but this depends in some amount on your server - SQL Server will elevate it if the page locks are using too much memory.

If your select is ran with nolock then you will get dirty reads if you happen to select any rows which are involved in the update. This means you will read the uncommitted data, and it may not be consistent with other rows (since those may not have been updated yet).

For all other cases if your statement encounters a row involved in the update, or a row on a locked page (assuming the lock has been elevated) then the statement will have to wait for the update to finish.