Is update with nested select atomic operation?

Question

I need to select first (let's say) 10000 rows in database and return them. There may be more clients that do this operation at one time. I came up with this query:

update v set v.batch_Id = :batchId 
    from tblRedir v 
    inner join (
        select top 10000 id 
            from tblRedir
            where batch_Id is null 
            order by Date asc
    ) v2 on v.id=v2.id

It is a operation that consists from update and nested select. Both the queries work on the same table (tblRedir). The idea is that the rows are first marked by a unique batchId and then returned via

select * from tblRedir where batch_id = :batchId

(the batchid is a unique identifier (e.g. timestamp or guid) for each this update)

My question:

I thought that the operation update with nested select is atomic - that means that every client receives his own set of data that is unique (no other client received a subset of his data).

However it looks that I'm wrong - in some cases there are clients that receive no data, because probably they first both execute the select and then both execute the update (so the first client has no marked rows).

Is this operation atomic or not?

---

I work with Sql server 2005. The query is run via NHibernate like this

session.CreateSQLQuery('update....')

Answer 1

SELECT places shared locks on the rows read which then can be lifted in READ COMMITED isolation mode.

UPDATE places the update locks later promoted to exclusive locks. They are not lifted until the end of the transaction.

You should make the locks to retain as soon as they are placed.

You can do it by making the transaction isolation level REPEATABLE READ which will retain the shared locks until the end of the transaction and will prevent UPDATE part from locking these rows.

Alternatively, you can rewrite your query as this:

WITH    q AS
        (
        SELECT  TOP 10000 *
        FROM    mytable WITH (ROWLOCK, READPAST)
        WHERE   batch_id IS NULL
        ORDER BY
                date
        )
UPDATE  q
SET     batch_id = @myid

, which will just skip the locked rows.