470,591 Members | 2,313 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

don't 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?

Jul 23 '05 #1
7 1083

"hendershot" <ey****@yahoo.com> wrote in message
news:11*********************@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
Jul 23 '05 #2
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.

Jul 23 '05 #3
hendershot (ey****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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 (ey****@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, es****@sommarskog.se

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


Jul 23 '05 #5
hendershot (ey****@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, es****@sommarskog.se

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

thanks

Jul 23 '05 #7
hendershot (ey****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Nashat Wanly | last post: by
3 posts views Thread by Francois | last post: by
15 posts views Thread by z. f. | last post: by
4 posts views Thread by neo | last post: by
17 posts views Thread by =?Utf-8?B?Y2F0aGFyaW51cyB2YW4gZGVyIHdlcmY=?= | last post: by
3 posts views Thread by Ben Thomas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.