Key Lock

Hi,
I tried the following statement with two QA.
Use Northwind
Begin Tran
Update customers set country = 'Mexicos' where country = 'Mexico'
-- without commit/rollback here
I open another QA with
Select * from customers
-- of course it is now showing anything since it is being blocked
However when I key in sp_lock
it is showing KEY lock. My question is the "country" column is not a
primary key, not a clustered/non clustered index, how can it be a Key lock
with exclusive lock?
I understand the exclusive lock part, but i have no idea why the Key lock
occurs?
Thanks
EdThis is a row lock, most likely based on the table's primary key. Even if
not used to locate rows, the PK can still be used to acquire row locks.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:48A7B914-4237-4E61-8F73-72A24D3B59B2@.microsoft.com...
> Hi,
> I tried the following statement with two QA.
> Use Northwind
> Begin Tran
> Update customers set country = 'Mexicos' where country = 'Mexico'
> -- without commit/rollback here
> I open another QA with
> Select * from customers
> -- of course it is now showing anything since it is being blocked
> However when I key in sp_lock
> it is showing KEY lock. My question is the "country" column is not a
> primary key, not a clustered/non clustered index, how can it be a Key lock
> with exclusive lock?
> I understand the exclusive lock part, but i have no idea why the Key lock
> occurs?
> Thanks
> Ed
>
>|||Hi Ed
SQL Server doesn't lock individual columns, the minimum it can lock is a
row. The country column was used to determine which row, but once that row
is accessed, that whole row is locked.
If the table has a clustered index, the data rows are actually the leaf
level of the clustered index. Locking a row is then really locking an index
key. In fact, you will never see a row lock from sp_lock, indicated as RID,
for a table with a clustered index. It will always show KEY lock. But for
all practical purposes, it's the same thing.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:48A7B914-4237-4E61-8F73-72A24D3B59B2@.microsoft.com...
> Hi,
> I tried the following statement with two QA.
> Use Northwind
> Begin Tran
> Update customers set country = 'Mexicos' where country = 'Mexico'
> -- without commit/rollback here
> I open another QA with
> Select * from customers
> -- of course it is now showing anything since it is being blocked
> However when I key in sp_lock
> it is showing KEY lock. My question is the "country" column is not a
> primary key, not a clustered/non clustered index, how can it be a Key lock
> with exclusive lock?
> I understand the exclusive lock part, but i have no idea why the Key lock
> occurs?
> Thanks
> Ed
>
>|||thanks for the answer.
I am still not sure -- I created a nonclustered index on "country" columan
and issue the following statement
Select * from customers where country <> 'Mexico'
it still locks the select statement.
Why? or I have to say select * from customers where country <> 'Mexico' and
customerid = 'ALFKI' in order to show the result and avoid honoring the
exclusive lock?
Ed
"Kalen Delaney" wrote:

> Hi Ed
> SQL Server doesn't lock individual columns, the minimum it can lock is a
> row. The country column was used to determine which row, but once that row
> is accessed, that whole row is locked.
> If the table has a clustered index, the data rows are actually the leaf
> level of the clustered index. Locking a row is then really locking an inde
x
> key. In fact, you will never see a row lock from sp_lock, indicated as RID
,
> for a table with a clustered index. It will always show KEY lock. But for
> all practical purposes, it's the same thing.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:48A7B914-4237-4E61-8F73-72A24D3B59B2@.microsoft.com...
>
>|||Ed
I'm not quite sure what you're asking here.
No matter what index SQL Server uses to find the row, it will still have to
lock the row that it is updating.
Unless you tell SQL Server to ignore locks when you run the select, the
SELECT in another connection will block. You can tell SQL Server to ignore
exclusive locks by using the NOLOCK hint.
Select * from customers with (nolock)
Be very careful with this hint. It will allow you to read uncommitted data,
and if the connection that is doing the update gets rolled back, the data
that you read will be completely invalid.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:98877444-30E4-49FC-AA22-911D5B03CB0E@.microsoft.com...
> thanks for the answer.
> I am still not sure -- I created a nonclustered index on "country" columan
> and issue the following statement
> Select * from customers where country <> 'Mexico'
> it still locks the select statement.
> Why? or I have to say select * from customers where country <> 'Mexico'
> and
> customerid = 'ALFKI' in order to show the result and avoid honoring the
> exclusive lock?
> Ed
> "Kalen Delaney" wrote:
>
>