don't understand the following locking behavior

[hendershot]

can't figure out why the following locking scenario works the way it
does:

spid 1:
start transaction
select a row from table T1 with updlock
result: see an Update lock for the row and index key

spid 2:
query for the same row as in session 1
result: the query succeeds

spid 3:
do same as spid 1
result: blocks trying to get Update lock on the index key for row

Now I do the query again in spid 2 and it blocks trying to get a Shared
lock on the index key, and it's waiting for spid3. why was it able to
get a shared lock on a index key that had an Update lock at first but
then can't get the same shared lock when somebody else is also trying
to get an update lock?

[Simon Hayes]

Re: don't understand the following locking behavior


"hendershot " <eyusim@yahoo.c om> wrote in message
news:1108174034 .874699.16400@o 13g2000cwo.goog legroups.com...[color=blue]
> can't figure out why the following locking scenario works the way it
> does:
>
> spid 1:
> start transaction
> select a row from table T1 with updlock
> result: see an Update lock for the row and index key
>
> spid 2:
> query for the same row as in session 1
> result: the query succeeds
>
> spid 3:
> do same as spid 1
> result: blocks trying to get Update lock on the index key for row
>
> Now I do the query again in spid 2 and it blocks trying to get a Shared
> lock on the index key, and it's waiting for spid3. why was it able to
> get a shared lock on a index key that had an Update lock at first but
> then can't get the same shared lock when somebody else is also trying
> to get an update lock?
>[/color]

I tried this quickly, and I had no problems running a query for the row from
spid 2, even with multiple other spids all blocked and waiting for a lock on
the same index key. Perhaps you can post a script which reproduces what
you're seeing? And what version/servicepack are you using (I used Enterprise
Edition 2000 SP3a)?

Simon

[hendershot]

Re: don't understand the following locking behavior

I was using Standard Edition, it says version 8.00.194 (not 100% sure
about the last 3 digits). does SQL Server 8 = SQL Server 2000?

I'll try to post a script on monday.
thanks.

[Erland Sommarskog]

Re: don't understand the following locking behavior

hendershot (eyusim@yahoo.c om) writes:[color=blue]
> I was using Standard Edition, it says version 8.00.194 (not 100% sure
> about the last 3 digits). does SQL Server 8 = SQL Server 2000?[/color]

8.00.194 = The original version of SQL 2000 with no service packs. You
can download the current service pack SP3a from
http://www.microsoft.com/sql/downloads/2000/sp3.asp. I stronly recommend
you download and install this service pack, since it includes a fix for
the Slammer worm.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[hendershot]

Re: don't understand the following locking behavior

installed SP3a and now I am at version 8.00.760, still seeing the same
locking behavior.

this is what I am doing in Query Analyzer w/the Northwind database:

Window #1:

begin transaction
select description from categories
with (updlock) where categoryid = 1


Window #2:

select description from categories
where categoryid = 1

Window #3:

begin transaction
select description from categories
with (updlock) where categoryid = 1

I run the sql in window #1 and that gets the Update locks on the row
and key, run the sql in window #2 and it runs just fine, run the sql in
window #3 and it 's waiting to get update lock on the key which is what
you'd expect. now when I run the query in window #2 again it hangs
waiting on a Shared lock for the key.

If I change description to "*" it behaves the same way, if I change the
query in window 2 to get another field (categoryid) it's just fine.

Any ideas of why it can't get the shared lock when somebody else is
waiting for update lock?

Erland Sommarskog wrote:[color=blue]
> hendershot (eyusim@yahoo.c om) writes:[color=green]
> > I was using Standard Edition, it says version 8.00.194 (not 100%[/color][/color]
sure[color=blue][color=green]
> > about the last 3 digits). does SQL Server 8 = SQL Server 2000?[/color]
>
> 8.00.194 = The original version of SQL 2000 with no service packs.[/color]
You[color=blue]
> can download the current service pack SP3a from
> http://www.microsoft.com/sql/downloads/2000/sp3.asp. I stronly[/color]
recommend[color=blue]
> you download and install this service pack, since it includes a fix[/color]
for[color=blue]
> the Slammer worm.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: don't understand the following locking behavior

hendershot (eyusim@yahoo.c om) writes:[color=blue]
> installed SP3a and now I am at version 8.00.760, still seeing the same
> locking behavior.
>
> this is what I am doing in Query Analyzer w/the Northwind database:
>
> Window #1:
>
> begin transaction
> select description from categories
> with (updlock) where categoryid = 1
> ....
> If I change description to "*" it behaves the same way, if I change the
> query in window 2 to get another field (categoryid) it's just fine.
>
> Any ideas of why it can't get the shared lock when somebody else is
> waiting for update lock?[/color]

I don't really have a good answer, but I note that only happens if the
query in window #2 attempts to access a text column. (Or ntext or image.)

It may be a bug. I can't reproduce the problem in SP4 beta. However, when
I test in the latest drop of SQL 2005, the query does not get blocked.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[hendershot]

Re: don't understand the following locking behavior

yes, I see that it doesn't occur with certain types. I guess I'll
just have to wait for SP4.

thanks

[Erland Sommarskog]

Re: don't understand the following locking behavior

hendershot (eyusim@yahoo.c om) writes:[color=blue]
> yes, I see that it doesn't occur with certain types. I guess I'll
> just have to wait for SP4.[/color]

It does not seem to be fixed in SP4. In SQL 2005, it is.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp