drdeadpan (vkat01-nospam@yahoo.com) writes:
> Is this legal ?
>
> SELECT a.col1,b.col2,c.col3
> FROM tab1 a WITH (UPDLOCK) , tab2 b, tab3 c
> WHERE a.col1 = b.col1
> and b.col2 = c.col1
>
> WIll the above cause a UPDLOCK on tab1 and not tab2 and tab3?

Yes. A table hint always affects one table only. You can use the
OPTION clause for hints that affects the entire SELECT statement.
However, you cannot specify locking hints in OPTION.

> Also how can I find out what locks are in effect for a certain
> query..Is it syslocks or are there any other tables..?

master.dbo.syslockinfo is the table to look in. Or use sp_lock.

Or for a more versatile of information, you use my aba_lockinfo,
available at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.

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

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