|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
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 This 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. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "Ed" <E*@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. Show quote "Ed" <E*@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 Show quote "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 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" <E*@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 > > > > > > > > > > > > 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. Show quote "Ed" <E*@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: > >> >> 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" <E*@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 >> > >> > >> > >> > >> >> >> >> >  |
|||||||||||||||||||||||
Other interesting topics