Key lock ?

I'm trying to find the actual data row that was locked in
a blocking situation. I have the info from the
syslockinfo table at the time the blocking was occuring as
well as the resource, as well as other assorted facts. It
is a key resource (clustered index). Is there a way to
pull out the rowid and find the row?
resource = (580168b11779)John,
The resource you cite appears to be the hash value of the index key. If you
could include the entire set of lock rows, it might be easier to figure out
how to find out the actual row. It might be a secondary index lock for the
row.
Ron
--
Ron Talmage
SQL Server MVP
"jehub" <john.hubbard@.oneneck.com> wrote in message
news:010801c3635c$0bd7f7f0$a501280a@.phx.gbl...
> I'm trying to find the actual data row that was locked in
> a blocking situation. I have the info from the
> syslockinfo table at the time the blocking was occuring as
> well as the resource, as well as other assorted facts. It
> is a key resource (clustered index). Is there a way to
> pull out the rowid and find the row?
> resource = (580168b11779)
>

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:
>
>

keeping a lock on table or row

I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?You can do it with optimizer hints:

HOLDLOCK, UPDLOCK, PAGLOCK, TABLOCK, TABLOCKX|||thanks for your reply!

Would you elborate for me how to do that, I'm not familiar with SQL Server.

I would like to be able to do this in a single statement if possible, perhaps something to append to a regular update statement to hold the lock? (I tried appending 'holdlock' to the statement but that dosen't appear to be correct syntax)

thanks|||Not True

BEGIN TRAN

Some code....

won't COMMIT until you COMMIT the TRAN with COMMIT TRAN...

Sure in SQL you have to start the transaction..

and in Oracle that's a setting that you can change...

Also, I really hope this is not for an application your building and just in SQL+

In any event getting in and out as FAST as possible should be the way to think...regardless of what you're doing...|||begin tran
select * from your_table (TABLOCK) where 1=2
update your_table set...where...
commit tran