Table Alias in UPDATE with locking hint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table Alias in UPDATE with locking hint

Author

Topic

kmarshba
Starting Member

24 Posts

Posted - 2004-06-18 : 13:21:06

Alright, is there any real difference caused by the placement of the locking hint "with(rowlock)" in Syntax 1 versus Syntax 2?

Syntax 1:
UPDATE T
SET any_field = 1
FROM dbo.AnyTable T with(rowlock)
INNER JOIN dbo.AnySecondaryTable AST with(nolock)
ON T.my_id = AST.my_id

Syntax 2:
UPDATE T with(rowlock)
SET any_field = 1
FROM dbo.AnyTable T
INNER JOIN dbo.AnySecondaryTable AST with(nolock)
ON T.my_id = AST.my_id

Quick note: I don't believe there is a difference but wasn't sure how to prove it...

Thanks,

Kevin

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 13:41:40

BOL:

quote:

Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely.

Brett

8-)

kmarshba
Starting Member

24 Posts

Posted - 2004-06-18 : 14:59:44

Okay, but that is a BOL generalization AND does not answer the question.

The question remains valid regardless that BOL suggests I not ask it. There are good reasons to use locking hints...but I do not want to digress.

When a locking hint is specified SQL will discard that hint only when it is not feasible.

My generalization: When updating more than 20 rows the "with(rowlock)" hint is useless as SQL will automatically upgrade the lock.

If anyone has an idea how best to determine the answer to my original question please let me know. I will post the answer here if I figure out prior to someone else answering.

Thanks!

Kevin

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-18 : 15:43:12

Use SQL Profiler to follow the locking ?

-PatP

kmarshba
Starting Member

24 Posts

Posted - 2004-06-21 : 09:06:03

Unfortunately SQL Profiler doesn't allow you to look at the granularity of the lock.

sp_lock, as far as I can tell, does not distinguish rowlocks either.

Well I'll keep looking, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources