UPDATE + WITH (ROWLOCK) + CTE

Question

I can't find any documentation about syntax for T-SQL statement: I need to make an WITH (ROWLOCK) UPDATE on a CTE result.

Something like: (so updated will be top1000 table1.col2. Statement WITH (ROWLOCK) during an UPDATE on rows of table1 is crucial)

    ;WITH CTE AS 
    ( 
        SELECT TOP(1000) table1.col2
        FROM  table1 INNER JOIN table2 ON table1.id = table2.id    
    ) 
    UPDATE CTE WITH (ROWLOCK)
    SET col2 = 1

The above statement is probably syntactically correct, however if someone will find such example, please give me a link.

BUT: my full SQL looks like below. During execute I get error:

Conflicting locking hints are specified for table "table1". This may be caused by a conflicting hint specified for a view.

Why can't I use WITH (NOLOCK) for selecting and WITH (ROWLOCK) on updating?

;WITH CTE AS 
( 
    SELECT TOP(5) table1.col2
    FROM table1 WITH (NOLOCK) INNER JOIN table2 WITH (NOLOCK) ON table1.id = table2.id 
    WHERE table1.col3 = 2
    ORDER BY table1.id    
) 
UPDATE CTE WITH (ROWLOCK)
SET col2 = 1

Solution

NOLOCK does not apply to the part of the query that references the table to be modified. In SQL Server update statements U-lock each row briefly while it is being tested. This is a deadlock avoidance mechanism. It prevents multiple updates to each S-lock a row for reading and then try to X-lock it.

You cannot make the U-locks go away AFAIK. But you can reduce the amount of rows U-locked to the abolute minimum by self joining:

update t1
set ...
from T t1 with (rowlock)
where t1.ID in (select TOP 5 ID from T t2 with (nolock) where ... order by ...)

This adds a little overhead but it allows you to use NOLOCK for reading.

Consider using snapshot isolation for the reads. NOLOCK has certain problems such as queries randomly aborting.