lock acquisition order in sp_lock

Question

Post reply

lock acquisition order in sp_lock

marcocrocevia

SSC Enthusiast

Points: 187
More actions
August 5, 2014 at 4:19 am

#309719

Hi all,
I need to discover the actual order in which locks are acquired on a table during a query.
This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.
From Management Studio I execute:
begin transaction
<my query>
exec sp_lock
rollback transaction
In the output I see interesting information about which locks are acquired, but:
- are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
- if not, how can I get this information?
Thanks!

Viewing 12 posts - 1 through 12 (of 12 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

No, they're not.

If you want to see the order, try a profiler trace or extended events (and don't do that on a production DB)

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

marcocrocevia SSC Enthusiast Points: 187 More actions · Answer 2

Thanks!

Could you please suggest the profiler trace settings to get to this information?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

You need the lock acquired event.

Do NOT do that on a production server, that's a high-volume event, it can have severe impact on the server if it's already under any load at all.

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

marcocrocevia SSC Enthusiast Points: 187 More actions · Answer 4

Thanks Gail,

I tried with lock acquired event and enabled all columns but I'm not able to see the indexes where the locks are acquired, do you know where I can get this information?

With sp_lock I have the objectId of the table and the indexId (0, 1, 2..) where the lock was grabbed, here I can see just the obectId of the table.

I also tried enabling the trace in Management Studio with

DBCC TRACEON (1200, 3604, -1) WITH NO_INFOMSGS;

and then executing the query.

In the Messages tab I see something interesting, for example

Process 66 acquiring X lock on KEY: 7:72057594042515456 (e04afa8841b5) (class bit2000000 ref1) result: OK

7 is my database id, but again, how can I know table and index where the lock was acquired?

Can I use 72057594042515456 and e04afa8841b5 to get to this info, and if so how this can be done?

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

marcocrocevia (8/5/2014)
I tried with lock acquired event and enabled all columns but I'm not able to see the indexes where the locks are acquired, do you know where I can get this information?

The objectid and objectid2 columns contain that. Depending on what type of lock it is, those two may contain the object id (eg schema locks) or may contain the 'hobt id', which you can use with sys.partitions or sys.allocation_units to get the object id and index id.

sp_lock just does that decoding for you, the system tables show the allocation units.

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

marcocrocevia SSC Enthusiast Points: 187 More actions · Answer 6

You can find attached what's in objectId and objectId2.

Since the first is 0, let's take the second which is 72057594042384384

Now, if I run:

use master

select * from sys.objects where object_id=72057594042384384

select * from sys.allocation_units where allocation_unit_id = 72057594042384384

select * from sys.partitions where hobt_id=72057594042384384

GO

I always get 0 rows... maybe 72057594042384384 is a compound id? Or should I find it somewhere else?

Thanks

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

Was the table you were querying in master? If not, why are you checking the catalog views in master?

No, it's not a composite ID. It's either the partition id, allocation unit id or hobt id, in one of the two catalog views I gave you, I can never remember which (that's for a key lock or page lock)

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

marcocrocevia SSC Enthusiast Points: 187 More actions · Answer 8

Ok it's in sys.partitions!

The key was using the correct db 🙂

Thanks a lot!

marcocrocevia SSC Enthusiast Points: 187 More actions · Answer 9

Just one more little question ..

I was able to map the trace output with the sp_lock output and it matches except for a thing.

The query is a simple merge statement on a table, and in the trace output I see a RangeI-N lock on KEY: 7:72057594042515456 which is missing from sp_lock output.

I expected to see the same output from both the methods, the differences being just on the locking order...

I see that RangeI-N lock means: Insert range, null resource lock; used to test ranges before inserting a new key into an index.

So I'm wondering:

- is it a real lock that needs to take into account when evaluating the possibility of a deadlock among multiple processes?

- why is it not visible from sp_lock?

Thanks again!

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

You were running in serialisable isolation?

Did you check the session_id in the trace? Was it from the session you were testing with?

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

marcocrocevia SSC Enthusiast Points: 187 More actions · Answer 11

Hi Gail,

ops I didn't see your answer it was in the next page 😉

Yes it's in the same session, I also see it if I run in Management Studio, in Messages tab

DBCC TRACEON (1200, 3604, -1) WITH NO_INFOMSGS;

my query

And I didn't touch the isolation level.

Thanks again