question on locks

[stan cai]

somebody helps ?

1.What kind of lockes are created on what resources when following
querys executes ?
select * from JOBQUEUE where ID='XXX'
update JOBQUEUE set columnA='YYY' where ID='XXX'

2.Under what kind of situation, share lock on a row or on a page or on a
table will convert to exclusive lock?

Thanks a lot.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

stan cai (ca*******@digitalchina.com) writes:

somebody helps ?

1.What kind of lockes are created on what resources when following
querys executes ?
select * from JOBQUEUE where ID='XXX'
update JOBQUEUE set columnA='YYY' where ID='XXX'
Depends. If there is an index on ID, you should get a shared row lock
and a exclusive row lock. If there is no index on ID, both statement
will need a shared table lock. The UPDATE still needs an exclusive row
lock.
2.Under what kind of situation, share lock on a row or on a page or on a
table will convert to exclusive lock?

When you update the row.

If you want to learn more about locking, it could be an idea to check
out Kalen Delaney's E-book, "Hands-On SQL Server 2000 : Troubleshooting
Locking and Blocking". See www.netimpress.com or directly:
http://www.shareit.com/product.html?...currencies=USD
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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