Lock : Released event

Question

Lock : Released event

Tanulo Bela

Valued Member

Points: 63
More actions
November 8, 2004 at 1:51 am

#182620

I use the profiler, trying to monitor sql server lock mechanism.
I run this:
"update customer set name ='a' where id=1 "
(This affects only one row, id column has index on it)
According to the profiler:
#1. Lock:Acquired 0X057022AC04000000000 (customer Table lock) (IX)
#2. Lock:Acquired 0X0670B0270010000000 (Page lock) (IX)
#3. Lock:Acquired 0X077022AC04010580E9CEE6CD (key lock) (X)
#4. Lock:Released 0X077022AC04010580E9CEE6CD (key lock) (X)
#5. Lock:Released 0X0670B0270010000000 (Page lock)
#6. Lock:Released 0X077022AC04010580E9CEE6CD (key lock)
#7. Lock:Released 0X0670B0270010000000 (Page lock)
#8. Lock:Released 0X057022AC04000000000 (Table lock) (IX)

How can it possible?
The Sql server release the same lock twice (#4, #6)
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

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

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Hi.

The difference is that first of the "duplicate" locks on the key itself was an exclusive lock on the key (preventing others reading/writing at all under the normal isolation level)- the second was a shared lock (which permits reading but not writing). I imagine SQL Server locks the record to prevent others writing to it, then, for the duration of the row's update, locks it again to prevent others reading/writing. At the completion of your transaction (which for you is this one statement), your locks are released.

The IX locks are "intent exclusive" locks and are for lock escalation purposes. SQL to maintain concurrency will lock individual rows - when a lot of rows are locked, the pages containing the rows are instead locked to reduce memory/resource consumption, etc. If lots of pages are being locked, then the table will be locked exclusively. The IX lock indicates that the intent to lock the table exists. It is also more efficient as SQL can easily know to prevent another user from exclusively locking the table - rather than scanning every row/page for exclusive/shared locks, the IX lock on the table prevents the exclusive locking without a table scan.

Hope this helps

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

Thanx for your reply.

The problem is that, if i run:

begin tran

update...

sp_lock

I see, that the eXcluse lock remain on the key. but the profiler shows:

(#1. Lock:Acquired 0X057022AC04000000000 (customer Table lock) (IX)

#2. Lock:Acquired 0X0670B0270010000000 (Page lock) (IX)

#3. Lock:Acquired 0X077022AC04010580E9CEE6CD (key lock) (X)

#4. Lock:Released 0X077022AC04010580E9CEE6CD (key lock) (X)

#5. Lock:Released 0X0670B0270010000000 (Page lock) )

a lock was released from the key (#4). after commit or rollback, there is 3 other lock:released event

(#6. Lock:Released 0X077022AC04010580E9CEE6CD (key lock)

#7. Lock:Released 0X0670B0270010000000 (Page lock)

#8. Lock:Released 0X057022AC04000000000 (Table lock) (IX) )

If there is a shared, and an exclusive lock on the key, which one has been released before rollback (/commit) ? I think none should have.

Anyway if there is two lock on it, why does not the profiler show both (lock:acquired)?

Thanks in advance,

Géza