decoding key/range locks

Question

Is there any way to decode the information returned by sys.dm_tran_locks so that we can what fields, and what values, are being locked by key/range locks?

Thanks,

Josh

Answer 1

Is there any way to decode the information returned by sys.dm_tran_locks so that we can what fields, and what values, are being locked by key/range locks?

Thanks,

Josh

Josh I am sorry but I am hard time understanding your question, may be I do not know what you are asking a more detailed question would help though

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 2

Shanky,

sys.dm_tran_locks shows data like the following, but what tables, what columns, and what ranges of values are being locked?

resource type	resource subtype	resource database id	resource description	resource associated entity id	resource lock partition	request mode
DATABASE		9		0	0	S
KEY		9	(b209e64a8519)	72057594057719800	NULL	RangeS-U
KEY		9	(6a28682ecd62)	72057594057719800	NULL	RangeS-U
KEY		9	(010261f14331)	72057594057719800	NULL	RangeS-U
METADATA	DATABASE_PRINCIPAL	9	principal_id=1	0	NULL	Sch-S
OBJECT		9		1885249771	0	IX
PAGE		9	1:1219622	72057594057719800	NULL	IU

Josh

Answer 3

Yes.  However it rarely tells you much of interest.

Please see "Decoding Key and Page WaitResource for Deadlocks and Blocking":

https://www.littlekendra.com/tag/blocking/

Answer 4

Oh, I'd be very interested.

I'm going to assume that the "resource description" is the "magic hash" for %%lockres%%, and the "resource associated entity id" is the hobt_id.  However, even if I could resolve the %%lockres%% on a gigabyte-scale table, I'm still not clear on the *range* part of the question, I'm pretty sure the lock range is covering many pages, thousands of rows, and Kendra's instructions only get you down to single rows, and unless I missed it without even making it clear just which field in the row it is, that is concerning.  CAN a range be "all rows where col3 = 0"?

I don't know that being shown "a" row from the lock, tells me what a range lock is locking.

Thanks,

Josh

Answer 5

A range lock is on pages, which may contain any number of rows.

This may help explain:

https://technet.microsoft.com/en-us/library/ms191272%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

https://simonlearningsqlserver.wordpress.com/tag/key-range-locks/

It may be interesting academically, but you really can't do anything about it.  So I am not sure how useful it is in the real world to know the pages which are locked.

Answer 6

Hi Josh,

the decoding is quite simple and (as Tom has written) is more an academically thing :). Depending on your example you have selected SERIALIZABLE READ as isolation level. This is the only isolation level which can hold a RANGE-Lock (see https://technet.microsoft.com/en-us/library/ms191272).

SERIALIZABLE is the most restrictive isolation level and prevents inserting new records in BETWEEN a range of values. So you could have three different object locks (Table -> PAGE -> ROW). If your Index allows ROWLOCK you will see these kind of "Range Locks". Based on these details the example is quite easy to understand and decrypt.

1. RangeS-U locks can only occur in SERIALIZABLE isolation level with ROWLOCK=ON option for the indexes

2. The resource_description shows the %%lockres%% if the resource_type is 'KEY'

3. The resource_associated_entity_id is the HOBT if the resource_type = 'KEY' or 'PAGE'

4. The resource_associated_entity_id is the object_id of the table if the resource_type is "OBJECT"

You will find a very good explanation (step by step) from Simon Peacock here
(https://simonlearningsqlserver.wordpress.com/tag/physloc/)

I wish you all a happy new year. May 2017 a better one than 2016 with all its surprises :)

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 7

Well, I will have to study both of Tom's links and my case here.

Yes, we are running some of the transactions as serializable.

The technet article states:

>The number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.

That should be clarifying.  It may be some aspect of that which is causing my confusion.  

Note that even with decoding the "intent" of the lock is not clear, is not necessarily quite what the lock actually *does*.  We have a four-field clustered PK and some additional filter fields, we are specifying just the first of the four PK fields and then one or more of the filter fields.  I'm not entirely clear what the lock "thinks" it's doing, and even enumerating all the rows locked leaves a bit of deduction still to be done.  I suppose some of that logic would have to be posted by the compiler in addition to the locks themselves, to be clearly visible.  I haven't closely examined the plans to see if there is some clearer statement there.  This is involved in a deadlock we have gotten a few times, and the real question is how we avoid the deadlock - actually the real question is how we structure the data and the query to avoid situations where the range locks cause a deadlock, please see below.  I think I have a solution but it's a bit messy and I'm still trying to make sure I'm not overlooking something a lot easier.

Happy New Year to all.

Josh

related to:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f360e77a-043d-4849-b3da-90fe3187c39b/pick-a-card-any-card?forum=sqldatabaseengine#f360e77a-043d-4849-b3da-90fe3187c39b

Answer 8

Good Morning Josh,

let me try to explain what SERIALIZABLE isolation level means. SERIALIZABLE need ALWAYS a range lock because it has to prevent that...

• existing records will be changed
• new records will be inserted BETWEEN the range of records

by other processes. Maybe it is helpful to show a little demo for it. A table [dbo].[CustomerOrderDetails] is given with a clustered PK on (Order_Id, Position)

ALTER TABLE dbo.CustomerOrderDetails
ADD CONSTRAINT pk_CustomerOrderDetails_Order_Id
PRIMARY KEY CLUSTERED
(
    Order_Id,
    Position
);
GO

The definition of the PK is quite simple an INT (Order_Id) and an INT for Position!

The next script creates a RangeS-S range lock when I run a query against the Order_Id = 1000.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO


BEGIN TRANSACTION;
GO
    SELECT * FROM dbo.CustomerOrderDetails
    WHERE	Order_Id = 1000;
    GO

    SELECT * FROM sys.dm_tran_locks
    WHERE	request_session_id = @@SPID;
    GO
COMMIT TRANSACTION;
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

This SELECT will return only ONE record with the Order_Id = 1000 but we have 2 key locks in the session.

If you have a look at the [resource_description and compare it with the records which belongs to this values you will understand what happens here:

SELECT  TOP 10
        %%lockres%%,
        *
FROM    dbo.CustomerOrderDetails
WHERE   %%lockres%% IN ('(bfd2b1f9f3b4)', '(e6ec39179281)');

Do you see what happens here? There is NO CHANCE to insert a new order detail for the order_id = 1000 because the "range" between" 1000 | 1 and 1001 | 1 is locked now. An insert of 1000 | 2 is forbidden!

If you have a SELECT on only the first (n) key attributes of your clustered index + a filter it is clear that Microsoft SQL Server has to prevent any inserts in between. Only if you cover the FULL C.I. than only this record will be locked (when the CI is unique!) because there can be no other value in between.

HTH - have a good start into 2017...

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 9

Uwe,

Thanks, you always give such wonderfully detailed and technical explanations.

I have two hesitations here, one is that I'm not sure that what I'm seeing in action is working according to these explanations, I will have to go back and redo them to see.  The second is that I don't really want quite all this behavior, it so happens that I already have a fully populated set of rows, and nobody is going to insert or delete any, so that is not the purpose of our using serialization.  Perhaps we could try downgrading to repeatable read, I'm not certain, would that avoid range locks, not quite certain why it would.  The third is similar, we want the serialization so that readers don't chose the same, but once a reader becomes a writer we'd actually prefer the writer have priority.  I'm afraid our read sometimes locks fifty rows in order to select the top one, and then someone wants to update one of them and is blocked or deadlocked.  The problem is too many are locked for too long, but I can't see an easy way around it.

I do see a hard way around it.  It's all very educational.  I just keep wondering why what seems like a simple task is so hard in a relational database - and I guess it's party because it *is* a relational database and does things in sets when what we want is inherently more of a sequential process.  We do have a highly concurrent process, so even little overlaps will cause problems, and even if the deadlocks are eliminated we don't really want the whole thing serialized, just the critical part.  Which I think we can get if we switch to using TSQL sequences, where the issuing of the "next" value is outside of the transaction model, it's atomic without.  But then we have to "statically serialize" our process so that a sequential number maps to the four PK fields.  Yes, we have to add the identity surrogate - that probably should have been used in the first place.  Oh, the irony! :)  But it's actually a bit of an unusual case where what *is* after all a surrogate, turns out to save the day.

Thanks,

Josh

Answer 10

Hi Josh,

"Perhaps we could try downgrading to repeatable read, I'm not certain, would that avoid range locks, not quite certain why it would."

YES - Repeatable Read will not use Range Locks but only holds locks on the "affected" records while the transaction is running. If you take my example above (i use another Order_Id) you will see what locks Microsoft SQL Server will hold:

The explanation is quite simple - the REPEATABLE READ isolation level need to make sure that a lock - hold on a selected record - will be hold until the whole transaction ends. This kind of lock will be used to avoid changes on records when they have been read from the process.

The "normal" behavior" of READ COMMITTED is that a lock on a resource will be released when the resource has been read (with SELECT). When it comes again to the same resource and it has been changed by another process in the meanwhile the data are not the same as at the start time. Therefore the REPEATABLE READ (as the name imply :) ) avoid such situations but is less restrictive as the SERIALIZABLE isolation level.

"The third is similar, we want the serialization so that readers don't chose the same, but once a reader becomes a writer we'd actually prefer the writer have priority.  I'm afraid our read sometimes locks fifty rows in order to select the top one, and then someone wants to update one of them and is blocked or deadlocked.  The problem is too many are locked for too long, but I can't see an easy way around it."

You could use SERIALIZABLE in conjunction with READ COMMMITTED SNAPSHOT isolation modus. This will give you the opportunity that readers won't block readers BUT....

the usage of SERIALIZABLE will always block writers because the original transaction will hold an "S"-Lock which is not compatible to an X-Lock :)

If you don't want to use READ COMMITTED SNAPSHOT you have another option "ALLOW SNAPSHOT". But with this isolation level you HAVE to declare it manually in your codings.

---

Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)

Answer 11

The "normal" behavior" of READ COMMITTED is that a lock on a resource will be released when the resource has been read (with SELECT). When it comes again to the same resource and it has been changed by another process in the meanwhile the data are not the same as at the start time. Therefore the REPEATABLE READ (as the name imply :) ) avoid such situations but is less restrictive as the SERIALIZABLE isolation level.

You know, it takes a situation like the one I'm in, where the fine details really matter, to really bring home the meaning of all these.  Thanks for the info, and I will look more closely at "repeatable read".

We *do* want readers to block readers, so the snapshot modes seem contraindicated.  But we want to be able to lock just one row out of many.  In our case, just considering, I don't think repeatable read will help, remember inserting and deleting are not our issues here ... but I will still take another look.

I'm also fascinated by your description of READ COMMITTED - "lock on a resource will be released when the resource has been read".  This is exactly the problem we have with TSQL "update - from" statements all the time these days, that cause deadlocks, that it releases the lock - that is, it only acquires a shared lock at read time it doesn't hold it pessimistically until the update.  But perhaps that is off-topic for the moment.

Thanks,

Josh

Answer 12

You could use SERIALIZABLE in conjunction with READ COMMMITTED SNAPSHOT isolation modus. This will give you the opportunity that readers won't block readers BUT....

Actually I should have mentioned, that in order to avoid deadlocks we are using "with (updlock)" on the select, which is going to be a mixing of modes if we are not running the whole transaction in SERIALIZABLE.

SELECT TOP(@NeededQuantity) * 
FROM MyTable with (updlock) 
WHERE PKField1 = @PKF1 -- remembering there are 4 PK fields
  AND PKField2 = @PKF2
  AND FilterField = @FilterField;

This produces 82 range locks when run serializable, 3 U locks when run in read committed or repeatable read modes.

This is on Azure, where read committed snapshot is on by default.

I am not really certain what is going on when you have read committed snapshot on by default and start playing with (updlock).

Actually, the idea to use serializable on the transaction may be obsoleted by the use of "with (updlock)".

BTW, from above:

>The number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.

Note that this "number of RangeS-S *locks*" appears NOT to be the number of rows, but the number of ranges, requiring two rows each, I suppose.  The number of qualifying *rows* is 782, the number of RangeS-S locks under serializable is 82. 

Josh

Answer 13

"This is exactly the problem we have with TSQL "update - from" statements all the time these days, that cause deadlocks, that it releases the lock - that is, it only acquires a shared lock at read time it doesn't hold it pessimistically until the update.  But perhaps that is off-topic for the moment."

"am not really certain what is going on when you have read committed snapshot on by default and start playing with (updlock)."

It places a U lock on each row the query reads, and holds it until the end of the transaction.  And should be used whenever you are reading with the intent to immediately update.  And yes that includes UPDATE when using a subquery, CTE, or UPDATE ... FROM.  Only UPDATE ... WHERE reads with a U lock by default. 

This is true for both flavors of READ COMMITTED, but is more frequently a problem in READ COMMITTED SNAPSHOT.

David

---

Microsoft Technology Center - Dallas

My Blog

Answer 14

David,

So in read committed snapshot it still places the U lock on the "real" row, even though it has the snapshot rows in tempdb?  OK, that was my guess and hope, as I couldn't figure out what else might make sense.

I was also just going to mention that I had skipped over your earlier statement, that "A range lock is on pages, which may contain any number of rows".  I see that some page locks are *also* taken, and was just about to start trying to decode this to see if it is also taking key/range locks on the index pages involved, since we might have several indexes.  Otherwise I was trying to make sense out of the "n+1" row business and your "page" comment.

Will it always take these page locks, even if we turn off page locks for the given indexes?  Was just about to start experimenting down that way too.

Thanks,

Josh

ps - if I disallow page locks on the indexes, guess what, no page locks - and no other locks replace them that I can see.  Odd, that.  What could those page locks be doing, that is not happening just as well without them?

Answer 15

>So in read committed snapshot it still places the U lock on the "real" row, even though it has the snapshot rows in tempdb?

Yes.  And you will always read the current row with this (or almost any other) lock hint. 

David

---

Microsoft Technology Center - Dallas

My Blog