MS profiler Lock:Released events behavior

Question

MS profiler Lock:Released events behavior

Tanulo Bela

Valued Member

Points: 63
More actions
October 5, 2004 at 6:08 am

#109595

Hi,
I use the profiler, trying to monitor sql server lock mechanism.
I run this:
"begin tran
update customer set name ='a' where id=1 "
According to the profiler:
Lock:Acquired 0X057022AC04000000000 (customer Table lock)
Lock:Acquired 0X0670B0270010000000 (Page lock)
Lock:Acquired 0X077022AC04010580E9CEE6CD (key lock)
Lock:Released 0X077022AC04010580E9CEE6CD (key lock)
Lock:Released 0X0670B0270010000000 (Page lock)
(Only the table lock remaining? No.)
after that i run:
"rollback"
Lock:Released 0X077022AC04010580E9CEE6CD (key lock)
Lock:Released 0X0670B0270010000000 (Page lock)
Lock:Released 0X057022AC04000000000 (Table lock)
How can it possible?
The Sql server release the lock again?
In generally, Lock:Relased event's behavior is very strange to me (occurs where i do not except, occurs twice, etc..)
Will somebody please tell me about how to monitor the lock:release event, or what is the explanation of this.
Thanks in advance,
G

Viewing 3 posts - 1 through 2 (of 2 total)

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

kay SSCommitted Points: 1518 More actions · Answer 1

It's a tricky question and I don't have a real answer for you, but to me it looks like SQL Profiler reports two types of locks to you: "real" locks and "intent" locks.

See http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=9428 (the last entry) for more details.

It seems SQL Server has one "real" lock in your case and two "intent" locks, which are really just markers on a larger scale (e.g. table) that a lock on a finer scale (e.g. row) exists.

Does this make sense ??

Tanulo Bela Valued Member Points: 63 More actions · Answer 2

Thanks for your answer. You are right about the intent locks:

The single row update inducates 3 locks : table Intent eXclusive (IX), page Intent eXclusive (IX), and key (row) eXclusive (X).

My main problem is that the profiler shows the key lock is acquired than released, than released again.

And if i check the syslocks table i can see the server holds the key lock (identificated unequivocally by binary id 0X077022AC04010580E9CEE6CD) until the end of transaction rolled back, so i do not understad the first release event before the rollback.

If anyone has any idea, please reply,

Thanks