Lock Promotion In Parallel Plans

I Don’t Always Talk About Locks


But when I do, it’s usually to tell people they should use RCSI, and then disappear in a cloud of smoke.

Recently I was thinking about lock promotion, because that’s what happens when I get lonely.

While digging around, I found some interesting stuff.

This is the part where I share it with you.

Without Five I Couldn’t Count To Six


The first thing I wanted was a table that I wouldn’t care about messing up, so I made a copy of the Users table.

SELECT *
INTO dbo.IDontCareAboutUsers
FROM dbo.Users AS u

ALTER TABLE dbo.IDontCareAboutUsers 
    ADD CONSTRAINT pk_IDontCareAboutUsers_id PRIMARY KEY CLUSTERED (Id);

Then I picked on a Reputation that only has one entry in the table: 20720.

BEGIN TRAN
UPDATE idcau
SET idcau.Reputation = 0
FROM dbo.IDontCareAboutUsers AS idcau
WHERE idcau.Reputation = 20720
OPTION(MAXDOP 4)

ROLLBACK

What followed was a full morning of wishing I paid more attention in internals class.

Number One


The first thing I found is that there were 16 attempts at promotion, and four successful promotions.

4×4

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 16 get promotions?

Number Two


Well, that’s a parallel plan. It’s running at DOP 4.

I added the hint in the update query above so I wouldn’t have to, like, do more to prove it.

Plantar

Okay, maybe this makes a little more sense. Four threads.

If each one tried four times, maybe another thread was like “nah, yo”, and then got by on the fifth try.

Number Three


Looking at perfmon counters before and after running showed.. exactly four!

Divisible
Still nowhere to go

Number Four


sp_WhoIsActive only showed single locks

Hrm.

This isn’t wrong, necessarily. This is how things look in the DMVs it touches after the update runs, but the transaction is still open.

I’m not mad, but I am curious. I wanna know what happened in the middle.

Number Five


I set up a couple Extended Event sessions, one to capture locks acquired, and one to capture lock escalations.

This was neat.

Tell’em, picture

The red rectangle comes from locks acquired during the course of the update. You can see four separate threads going through and grabbing locks.

Each thread got the okay to escalate at 6,249 page locks.

Number Six


Lock promotion isn’t only denied when competing locks on the table are held by other queries.

Modification queries taking locks will attempt promotion every 1,250 locks.

Documentation regarding lock promotion points to at least 5,000 locks needing to be held before it occurs, as one factor (incompatible locks not being present are another).

If we have four threads asking every 1,250 locks (in this case on pages), they all will have made four attempts before finally escalating at 6,249.

6,249 / 1,250 is right around 5, for those who don’t have a calculator installed.

Don’t freak out if your monitoring tool tells you there’s a lot of attempts at escalation, and very few are successful.

It’s not always a sign that there’s blocking, though you may be able to correlate that with lock waits if both are present.

Thanks for reading!



One thought on “Lock Promotion In Parallel Plans

Leave a Reply

Your email address will not be published. Required fields are marked *