dont understand the following locking behavior

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?"hendershot" <eyusim@.yahoo.com> wrote in message
news:1108174034.874699.16400@.o13g2000cwo.googlegro ups.com...
> 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?

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|||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.|||hendershot (eyusim@.yahoo.com) writes:
> 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?

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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:
> hendershot (eyusim@.yahoo.com) writes:
> > 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?
> 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@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||hendershot (eyusim@.yahoo.com) writes:
> 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?

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@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes, I see that it doesn't occur with certain types. I guess I'll
just have to wait for SP4.

thanks|||hendershot (eyusim@.yahoo.com) writes:
> yes, I see that it doesn't occur with certain types. I guess I'll
> just have to wait for SP4.

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

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp