SEO

#1 (**permalink**) 02-29-2008, 05:33 AM

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

#2 (**permalink**) 02-29-2008, 05:33 AM

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

#3 (**permalink**) 02-29-2008, 05:35 AM

WIll look into it. Thanks

DrD

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

UPDLOCK on a mult-table join