5

I declared a table in my procedure which you can see below:

Declare @resultTable Table
(
  EmpId int,
  EmpStatusId int,
  CreatedDateTime datetime
)

and i perform a delete function:

Delete From ActualTable
Where Id = (Select EmpId from @resultTable with (nolock) where EmpStatusId = @tempId)

i am trying to avoid locking in the select statement to avoid deadlock even if i read a dirty data. However, "with (nolock)" is not allowed. The error says:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Is there a way to apply NOLOCK in a temporary table?

Vikdor
  • 23,934
  • 10
  • 61
  • 84
raberana
  • 11,739
  • 18
  • 69
  • 95

1 Answers1

10

I'd question whether you're asking the right question:

You own the table variable (note: its not a temp table, but a table variable), so there's no point issuing a nolock against it. You appear to want nolock issued against the target table, ActualTable, but a delete HAS to hold locks. Period.

The usual technique with handling large delete operations is to issue the deletes in batches of row ranges.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541