By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

UPDLOCK on a mult-table join

P: n/a
Hi all,

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?

COmments,thoughts,criticisms?

I have a problem with a query that performs a multi-table join
to get column values and then one of the tables in the join is
being updated. I am getting DEADLOCKs and was wondering if I could
try and reduce that.

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

DrD

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
drdeadpan (vk***********@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, es****@sommarskog.se

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

P: n/a
WIll look into it. Thanks

DrD

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.