Row locks not escalating to table locks after 5000

Question

Row locks not escalating to table locks after 5000

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply

jotate Mr or Mrs. 500 Points: 589 More actions · Answer 1

GilaMonster (7/17/2015)
jotate (7/17/2015)
It looks like this may just be the way that it worked in 2008 R2, and the "grab a table lock after 5000 page locks" was implemented in 2012 for this particular scenario.
No, lock escalation has been in the product for a very long time, before I started, and that was with SQL 2000. Why it's not in this case is a mystery. I don't have a working 2008 R2 instance any longer or I'd poke a bit and see what's up.
As a long-term fix I'd recommend the tablock hint and convert the query to use OPENQUERY, not because of effects on locking, but to ensure that the tables are joined remotely and then sent across. Linked servers tend to be more than a little finicky w.r.t. performance.
And you're right about the SP. 2008 has an SP4, 2008 R2 has SP3.
Edit: You're not the only person who's run into this: http://sqlblog.com/blogs/adam_machanic/archive/2009/10/30/sql-server-2008-lock-escalation-inserts-and-a-potential-bug.aspx. At this point, my money's on 'bug in SQL 2008'

Ahhh, how did I not find that article when I was searching yesterday? I need to improve my Google-foo. I'll work on getting your recommendations for the query structure pushed through and keep an eye out for this issue in the meantime. Thanks for all your help, Gail!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Thanks for pointing out that article Gail!

From Microsoft's response in the Connect item Adam mentioned:

Adam: This is a regression in lock escalation algorithm in SQL2008. We know what the issue is and it can be fixed. We will target to fix this in the next major release of SQL Server or in the next PCU (i.e. service pack) of SQL Server.

Looks like it was closed as fixed, supposedly in SP2. Some additional confirmation here: https://support.microsoft.com/en-us/kb/2448971.

Seems to be a fairly significant bug I was never aware of. Always something new to learn 🙂

jotate Mr or Mrs. 500 Points: 589 More actions · Answer 3

Jacob Wilkins (7/17/2015)
Thanks for pointing out that article Gail!
From Microsoft's response in the Connect item Adam mentioned:
Adam: This is a regression in lock escalation algorithm in SQL2008. We know what the issue is and it can be fixed. We will target to fix this in the next major release of SQL Server or in the next PCU (i.e. service pack) of SQL Server.
Looks like it was closed as fixed, supposedly in SP2. Some additional confirmation here: https://support.microsoft.com/en-us/kb/2448971.
Seems to be a fairly significant bug I was never aware of. Always something new to learn 🙂

I don't think the query in the More Information section of that resolution page is catching the complete story. It works correctly for the first insert into the table or for small inserts, but all subsequent big inserts (more than a million records) fail to escalate.

Here's the original code increased to 4million records that returns 1:

USE tempdb

GO

CREATE TABLE x

(

i INT NOT NULL PRIMARY KEY

)

GO

BEGIN TRAN

INSERT x

SELECT TOP (4000000)

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS r

FROM

master..spt_values a,

master..spt_values b

ORDER BY

r

SELECT

COUNT(*)

FROM sys.dm_tran_locks

WHERE

request_session_id = @@SPID

ROLLBACK

GO

DROP TABLE x

Here's the modified code increased to 4million records with a truncate and another 4million records inserted that returns 6443:

USE tempdb

GO

CREATE TABLE x

(

i INT NOT NULL PRIMARY KEY

)

GO

BEGIN TRAN

INSERT x

SELECT TOP (4000000)

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS r

FROM

master..spt_values a,

master..spt_values b

ORDER BY

r

truncate table x

INSERT x

SELECT TOP (4000000)

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS r

FROM

master..spt_values a,

master..spt_values b

ORDER BY

r

SELECT

COUNT(*)

FROM sys.dm_tran_locks

WHERE

request_session_id = @@SPID

ROLLBACK

GO

DROP TABLE x

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Ah, it took me a few reads, but I think I understand what you're saying now.

If I understand you correctly, your point is that even in the "fixed" version, the script with the TRUNCATE and second INSERT ends up not escalating.

That's actually not all that surprising. When the TRUNCATE is run, the transaction takes out a schema modification lock on the table.

Since this is all within a transaction, that Sch-M lock is held for the duration of the transaction, and when lock escalation is triggered by the second insert, it fails because it can't escalate to an X lock on the table from a Sch-M lock. The X lock actually allows more than the Sch-M, so the Sch-M has to stay, which means the query continues on with page locks.

You can confirm this in multiple ways. First, you can use Profiler or Xevents to watch for the Lock Escalation event. You'll find that it is triggered by both queries, not just by the first.

Second, if the entire set of queries is not wrapped in a transaction (so that the Sch-M lock from the TRUNCATE does not stick around), then the second query will successfully escalate to an exclusive table lock. Since the lack of an explicit transaction will prevent the locks from persisting, you can watch the locks taken out live from another query window, and/or wrap just the INSERTs in transactions.

Third, if you leave the entire thing wrapped in a transaction, but replace the TRUNCATE with a DELETE (so that nothing more than an X lock is ever needed at the table level), then again lock escalation is successful on the second query.

It seems that it all works more or less as it says on the tin in this case 🙂

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jacob Wilkins (7/17/2015)
Since this is all within a transaction, that Sch-M lock is held for the duration of the transaction, and when lock escalation is triggered by the second insert, it fails because it can't escalate to an X lock on the table from a Sch-M lock. The X lock actually allows more than the Sch-M, so the Sch-M has to stay, which means the query continues on with page locks.

That's not how locking works.

You can never block yourself. If you hold a Sch-M lock on a table, no one else can take any locks at all, but you can still take other locks because your session is the one holding the locks.

If the Sch-M lock was blocking (as you describe), then the second insert would block right from the beginning because there are no locks compatible with an Sch-M and so the insert wouldn't even be able to take the first table-level IX lock it needs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

I didn't say it was blocked; I said lock escalation failed, because it seems the engine can't escalate to X from Sch-M (because Sch-M is a more protective lock).

Very different things, but I may not have been very clear. 🙂

Having said that, I'm not 100% certain of that explanation. It was just the most consistent explanation of all the test results I obtained.

The failure to escalate locks might be for the reason I suggested, or it might be because lock escalation can only convert intent locks (the documentation hints at this as well, but doesn't actually say it explicitly).

It could also be that both explanations are wrong. If you have tests or documentation showing those explanations are wrong, I welcome that! It's how all this testing stuff is supposed to work. 🙂

Until then, though, I'll stand by those two explanations (either you can't escalate from a Sch-* lock because only intent locks can be converted in escalation, or you can't escalate to X from Sch-M, since that would mean the transaction would be taking out a less restrictive lock) as the most plausible explanations of the results of the tests I described in my previous post.

Either way, in this particular case, the issue is the Sch-M lock taken by the truncate. The "why" is more speculative, of course, but seems quite cogent to me. Feel free to test and disconfirm 🙂

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Jacob Wilkins (7/18/2015)
I didn't say it was blocked; I said lock escalation failed, because it seems the engine can't escalate to X from Sch-M (because Sch-M is a more protective lock).

Lock escalation is when row or page locks are converted to a table lock of the same type. So shared row lock to a shared table lock, exclusive page lock to exclusive table lock, etc. Escalation doesn't change lock modes (shared to exclusive or exclusive to schema) and wouldn't require the schema lock to be released

Either way, in this particular case, the issue is the Sch-M lock taken by the truncate. The "why" is more speculative, of course, but seems quite cogent to me. Feel free to test and disconfirm 🙂

Where did you test? 2008 R2 (where we know there's an escalation bug) or any other version where the escalation works correctly? I did a quick test in ~~2012~~ 2014 RTM, JT's code with the truncate in the middle returned 25 for the count of locks at the end and fired two lock escalation XE events.

I'm still going with 'bug in 2008 R2, partially fixed in SP2'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jotate Mr or Mrs. 500 Points: 589 More actions · Answer 8

Jacob Wilkins (7/18/2015)
It could also be that both explanations are wrong. If you have tests or documentation showing those explanations are wrong, I welcome that! It's how all this testing stuff is supposed to work. 🙂

New code to commit each transaction separately and demonstrate that the second insert doesn't escalate like it should:

USE tempdb

GO

CREATE TABLE x

(

i INT NOT NULL PRIMARY KEY

)

GO

begin tran

INSERT x

SELECT TOP (4000000)

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS r

FROM

master..spt_values a,

master..spt_values b

ORDER BY

r

SELECT

COUNT(*)

FROM sys.dm_tran_locks

WHERE

request_session_id = @@SPID

commit tran

GO

begin tran

INSERT x

SELECT TOP (4000000)

ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) + 4000000 AS r

FROM

master..spt_values a,

master..spt_values b

ORDER BY

r

SELECT

COUNT(*)

FROM sys.dm_tran_locks

WHERE

request_session_id = @@SPID

commit tran

go

drop table x

Results are 1 and then 6433 on my 2008 R2 SP3 instance.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

I know what lock escalation is, but thanks for the refresher 🙂

Technically it does rely on changing modes (IS to S and IX to X) at the table level, and that's the bit I'm hypothesizing doesn't work when the transaction already holds a Sch-M lock on the table.

Again, the tests show the escalation definitely gets triggered, but is not successful (page locks get taken out anyway) when the transaction has a Sch-M lock on the table already.

Now, whether that's because escalation expects an IS or IX at the table level before conversion and doesn't find one,, or because it can't convert to X from Sch-M is trickier to determine (or if there's a completely separate explanation, for that matter).

Again, I'm open to tests that disconfirm, but those still stand as the two most consistent explanations of the results so far.

If you have an alternative explanation and tests that debunk mine, by all means let me know. The truth is what I'm after 🙂

I tested in 2008 R2 SP3, where the bug is fixed (see the link provided in my earlier post). For good measure I also tested in 2012 SP2 and saw the same behavior.

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

jotate (7/18/2015)
Results are 1 and then 6433 on my 2008 R2 SP3 instance.

2 and 2 on ~~2012~~ 2014 RTM, with two lock escalation events fired.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Jacob Wilkins (7/18/2015)
I tested in 2008 R2 SP3, where the bug is fixed (see the link provided in my earlier post)

I've seen a bug 'closed as fixed' still present in the product two major version later. I suspect it was partially fixed in SP2 and then fixed again going to 2012, seeing as the incorrect behaviour is still present in SP3 (see JT's last test without the truncate).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Hehe, I think (at least for ~~2012~~ 2014 and the insert after truncate case), the answer here is that SQL's a hell of a lot sneakier than we thought.

The second insert after the truncate (the one in the same transaction as the truncate) does escalate to a table lock, triggering the lock escalation event. However I suspect (need to dig through the lock released events to confirm) that the table-level X lock gets dropped as soon as the insert is complete because it's no longer needed to protect the table until the end of the transaction. The Sch-M lock is a more intensive lock, and that has to be held until the end of the transaction. Hence when dm_tran_locks is queried after the insert finishes, there's no sign of any IX or X locks on the table itself, they've been released to reduce memory usage.

Table-level IX lock taken at the beginning of the insert:

Table-level X lock taken part way through, time matches exactly with the lock_escalation event

Code that was run and looking up the object id.

I think this needs writing up in more detail. JT, do you have a blog? Do you want to write this up or may I?

Edit: To be clear, I ran JT's example up to the end of the truncate table, leaving the transaction open, then started the XE session with lock_acquired, hence all the locks it saw were for the second insert, the one after the truncate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 13

Ah, now it's getting really interesting, since this seems to be a distinct phenomenon.

For that bit of code, I'm seeing the same as you two. On 2008 R2 SP3, page locks get taken out on the second run. On 2012 SP2 the two INSERTs have the same behavior, and escalate to table locks.

Now for the interesting part. I actually ran the same test yesterday, but with one little difference. Instead of committing the first transaction, I rolled it back. Then both INSERTs successfully escalate in 2008 R2 SP3.

So, right now, I have the following set of results:

2008 R2 SP3

1) Both INSERTs in a single transaction, separated by a TRUNCATE TABLE x:

First INSERT escalates successfully, second INSERT triggers escalation but takes out page locks anyway.

2) Both INSERTs in a single transaction, separated by a DELETE FROM x:

Both INSERTs escalate successfully.

3) Both INSERTs in a single transaction, with nothing separating them (using the +4000000 to avoid PK violations):

First INSERT escalates successfully, and second INSERT takes out page locks without even triggering escalation.

4) Each INSERT in its own transaction, first transaction is rolled back:

Both INSERTs escalate successfully (same as 2)

5) Each INSERT in its own transaction, first transaction is committed:

First INSERT escalates successfully, second INSERT takes out page locks, but doesn't even trigger escalation. (same as 3)

In 2012 SP2:

1) Both INSERTs in a single transaction, separated by a TRUNCATE TABLE x:

First INSERT escalates successfully, second INSERT triggers escalation but takes out page locks anyway. This is the same behavior as in 2008 R2 SP3

2) Both INSERTs in a single transaction, separated by a DELETE FROM x:

Both INSERTs escalate successfully. This is the same behavior as in 2008 R2 SP3

3) Both INSERTs in a single transaction, with nothing separating them (using the +4000000 to avoid PK violations):

Both INSERTs escalate successfully. This is different than 2008 R2 SP3 (i.e., fixed)

4) Each INSERT in its own transaction, first transaction is rolled back:

Both INSERTs escalate successfully. This is the same behavior as in 2008 R2 SP3.

5) Each INSERT in its own transaction, first transaction is committed:

Both INSERTs escalate successfully. This is different than 2008 R2 SP3 (again, fixed).

So, we seem to have two different things at play here.

First, in 2008 R2 SP3, there is some issue with lock escalation when the data from the first INSERT is still in the table. When the first INSERT is rolled back, or the data is deleted before the second INSERT, the second INSERT escalates as expected.

When there is no DELETE or ROLLBACK, then escalation doesn't even trigger. That definitely does seem like some leftover bits of the bug, since MS described the bug as not counting the locks correctly, which would lead to escalation not being triggered at all.

That all seems to be have been fixed by the time of 2012 SP2.

Second, the behavior around TRUNCATE and the Sch-M lock is the same in 2012 SP2 as it is in 2008 R2 SP3.

That, combined with the fact that escalation is triggered for the INSERT after a TRUNCATE (as opposed to the other scenarios jotate's example exposed, where escalation doesn't trigger at all in 2008 R2 SP3), suggests there's a different mechanism at work there.

Again, I'm not married to my ideas being right (after all, if I'm right, then I'm no better off than I was to start with; if I'm wrong, then I've improved my knowledge :-)), but so far the two explanations I've offered are the only consistent explanations I've seen or come up with.

Cheers!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 14

Ah, very interesting Gail! Thanks for that additional info.

So, just to double check, when you run the second INSERT after the TRUNCATE (all within the same transaction) in 2012 SP2, do you see the 6200ish page locks taken out before committing/rolling back, as I do in my test?

Cheers!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

Jacob Wilkins (7/18/2015)
1) Both INSERTs in a single transaction, separated by a TRUNCATE TABLE x:
First INSERT escalates successfully, second INSERT triggers escalation but takes out page locks anyway. This is the same behavior as in 2008 R2 SP3

It's not the same as 2008 R2. With 2008 JT was seeing > 6000 locks held at the end of the query.

In ~~2012~~ 2014 I get 25 locks, most of them are page locks, but when I look at the header info of the page, they're not allocated to the table that's being inserted into. (DBCC Page returns object id 0, index id -1). I suspect they're pages which were deallocated by the truncate and not (for whatever reason) reallocated with the insert. If I change the rollback at the end of that code to a commit, then all the page numbers that are listed with page locks are, when looked at with DBCC Page, deallocated pages, not part of table x.

At this point, after digging through lock acquired and DBCC Page (and on SQL ~~2012~~ 2014 and above), I'm going to say:

Both INSERTs in a single transaction, separated by a TRUNCATE TABLE x

Both inserts escalate as expected, different behaviour to that seen in SQL 2008 R2.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass