Blocking Merge Statement

Kendra Little walks through the MERGE command and potential blocking issues with it:

The holdlock hint is a way to get serializable isolation level in SQL Server for a specific table, without having to change the isolation level for your entire session. Serializable is the highest isolation level in SQL Server using pessimistic locking.

When you “HOLDLOCK”, you tell SQL Server to protect any rows you read with a range lock– just in case someone comes along and tries to change one or sneak  one in.

That means that even when you’re just reading ParentTable and not inserting a row, you’re taking out a key range lock. You’re willing to fight other users over those rows to protect your statement.

Kendra’s final advice is to avoid the MERGE command in most cases; read on to find out why.

Related Posts

Rewriting Expensive Updates

Erik Darling takes us through an experiment: Let’s also say that bad query is taking part in a modification. UPDATE u2SET u2.Reputation *= 2FROM Users AS uJOIN dbo.Users AS u2ON CHARINDEX(u.DisplayName, u2.DisplayName) > 0WHERE u2.Reputation >= 100000;AND u.Id <> u2.Id; This query will run for so long that we’ll get sick of waiting for it. […]

Read More

Lock Promotion

Erik Darling tries to figure out why his locks can’t get ahead in the rat race: The first thing I found is that there were 16 attempts at promotion, and four successful promotions.Why did this seem weird? I dunno.Why would there be only 4 successful attempts with no competing locks from other queries?Why wouldn’t all […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031