Geek Answers Handbook

UPDATE + WITH (ROWLOCK) + CTE

I can not find the syntax documentation for the T-SQL statement: I need to do an WITH (ROWLOCK)UPDATE of the CTE result.

Something like: (top1000 table1.col2 will be updated. The statement WITH (ROWLOCK)during UPDATE for the rows of table1 is critical)

    ;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 finds such an example, give me a link.

BUT: my full SQL is as follows. At runtime, I get an error message:

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

Why can't I use WITH (NOLOCK)to select and WITH (ROWLOCK)to update?

;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
+4
1

NOLOCK , , . SQL Server U-lock . . S- , X- .

U-locks AFAIK. U-locked , :

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

, NOLOCK .

. NOLOCK , .

+3

More articles:


All Articles