Preventing Lock Escalations Caused by UPDATE operations

http://blogs.microsoft.co.il/blogs/srldba/archive/2008/11/10/preventing-lock-escalations-caused-by-update-operations.aspx

http://support.microsoft.com/kb/323630

Preventing Lock Escalations Caused by UPDATE operations

Yoni Okun

Lock Escalation is a situation in which SQL Server decides to convert many row-level locks into a Table Lock . This operation is required in order to prevent excessive memory consumption that needed to support many Lock objects and to maintain them. In effect, it is a trades-off between memory consumption and concurrency ; Lock Escalation to a less-grained lock level (Table Lock) can prevent other users from accessing the table.

Microsoft Support has released a nice KB article that explains this issue and provides 3 different workaround methods to prevent lock escalation. See http://support.microsoft.com/kb/323630

These methods are based on breaking the data changing operation into smaller sections. That is, instead of doing one big DELETE that affects huge amount of records and leads to Lock Escalation, the process deletes the records in smaller chunks.

However, the workarounds provided in this KB article, that are based on setting the ROWCOUNT SET option to a specified numeric value, only apply to a deletion from the table, where rows are deleted in chunks. The deleted records disappear from the table and the next iteration deletes a different chunk of records. Thus, the table is eventually completely deleted without a Lock Escalation.

But if we need to prevent Lock Escalation on UPDATE operation, using ROWCOUNT SET option is not enough nor is the TOP (N) clause technique. This is because the records still exist in the table after the modification, and the next iteration will affect the same records again and again. Of course, we can add a WHERE criteria to the UPDATE statement, but this way we might make performance worse or might not prevent Lock Escalation at all.

I would like to suggest another workaround that uses @@ROWCOUNT global variable and takes advantage of the OUTPUT clause available in DML operation (DELETE, UDPATE, and INSERT). In addition, this method does not change any SET option, specifically not the ROWCOUNT option, which can cause some other undesired effects on the current session.

Let’s see how we can achieve this.

DECLARE @UpdTab TABLE

(

ProdId int

)

DECLARE @x int begin tran

SET @x = 0

WHILE @@ROWCOUNT > 0

BEGIN

UPDATE TOP ( 500 ) Products

SET UnitPrice = UnitPrice + 1

OUTPUT inserted . ProductId INTO @UpdTab FROM Products P

Where NOT EXISTS (

SELECT *

FROM @UpdTab as t WHERE t . ProdId = P . ProductId

)

END

--check locking status

exec sp_lock @@SPID

commit tran

 

In the above code we use the TOP (N) to update a chunk of 500 records, but in the same operation we transfer those records into a table variable using the OUTPUT clause. The WHERE criteria in the UPDATE statement verifies that the next 500 records do not exist in the table variable, and then updates another chunk of 500 records. In this method, the entire table is updated in chunks, in a way that prevents Lock Escalation.

Note that the code line SET @x = 0 is only there in order to make sure that the @@ROWCOUNT value is bigger than 0, letting the WHILE loop continue until the desired end.

We can then use the same method for DELETE operation as well:

Begin tran declare @x int

set @x = 1

while @@ROWCOUNT > 0

delete top ( 500 ) Products

where Discontinued = 1

 

--check locking ststus

exec sp_lock @@SPID

commit tran

In this code we do use the WHERE clause to specify the chunk of records, but the absence of any SET operation, makes this code a better option