question on locks

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!stan cai (caichuang@.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...¤cies=USD

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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