Key Lock

Question

Key Lock

sudhakara

SSCrazy Eights

Points: 9425
More actions
June 18, 2008 at 9:09 am

#381208

create table guid_index (a varchar(20),guid udd_guid )
go
create clustered index guid_index_ind on guid_index (guid)
go
spid - 1
delete from guid_index where guid ='EAE1AA27-A6CD-426C-B1E1-30D2BF19861D'
and a <> '9296C26F-5DE2-429E-'
spid - 2
delete from guid_index where guid ='7AC31559-1C64-406E-A203-970FC738A6B1'
and a <> 'F956F65D-A0FF-4D81-'
when i am deleting record from two session, locking happeing at key level but my key is on guid column only . why its happening ? can you anyone explain ?

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

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

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 1

It's not clear what you mean. Are you getting an error or deadlock? Can you explain more what you are trying to determine or what the problem is?

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

While GUID's can be a unique value, you didn't put a unique constraint on the key, so when combined with the other part of your where clause, you're probably getting an index scan. That's going to block one query while the other runs.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GSquared SSC Guru Points: 260824 More actions · Answer 3

This:

create clustered index guid_index_ind on guid_index (guid)

Needs to be:

create unique clustered index guid_index_ind on guid_index (guid)

That should solve your problem.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 4

then it show lock at page level or table levle but its showing key levle thats why i am asking ...

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

From the BOL:

KEY = Lock within an index that protects a range of keys in serializable transactions.

You're locking a range when what you really want is to lock a row.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 6

key cloumn will have different value for each transaction then how its locking the other transaction ...