Saturday, February 25, 2012

Question about "WITH (ROWLOCK)" hint.

(SQL Server 2000, SP3a)
Hello all!
I've got a trigger on a table that I want to cause a column in another table
to be
updated:
update t
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t with (rowlock)
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
Is the WITH (ROWLOCK) hint any different if I express it this way:
update t with (rowlock)
set t.[DateLastUpdated] = getdate()
from [dbo].[MyOtherTable] as t
inner join inserted as i with (nolock) on i.[PK] = t.[PK]
My guess is that it isn't (since the compiler will warn you if there are inc
ompatible lock
types between the "alias" and the table) -- so I just thought it'd be the sa
me. But, I'm
not really sure and was hoping for clarification.
Thanks for any help you can provide!
John PetersonJohn,
I don't believe so but your example is a bad one<g>. First off you don't
need to issue a NOLOCK on the Inserted table since the session doing the
updating is the only one that has access to it. I don't know if it even
does anything to tell you the truth. And the ROWLOCK should not be needed
either. Your joining on a PK and unless your updating most of the table it
should use rowlocks anyway. Hints should only be used where absolutely
necessary. You need to prove to yourself they are needed before using them
otherwise you risk getting poor performance overall.
Andrew J. Kelly
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I've got a trigger on a table that I want to cause a column in another
table to be
> updated:
> update t
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t with (rowlock)
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> Is the WITH (ROWLOCK) hint any different if I express it this way:
> update t with (rowlock)
> set t.[DateLastUpdated] = getdate()
> from [dbo].[MyOtherTable] as t
> inner join inserted as i with (nolock) on i.[PK] = t.[PK]
> My guess is that it isn't (since the compiler will warn you if there are
incompatible lock
> types between the "alias" and the table) -- so I just thought it'd be the
same. But, I'm
> not really sure and was hoping for clarification.
> Thanks for any help you can provide!
> John Peterson
>|||> from [dbo].[MyOtherTable] as t with (rowlock)
What do you think you're gaining here?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hello Andrew!
Thanks -- yeah, I figured my NOLOCK hint was likely superfluous. The table
that being
updated is a fairly high-use table, and explicitly providing a ROWLOCK hint
has been
helpful in the past. But, your words of caution are appreciated!
Thanks for your help!
John Peterson
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OYCc4xb8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> John,
> I don't believe so but your example is a bad one<g>. First off you don't
> need to issue a NOLOCK on the Inserted table since the session doing the
> updating is the only one that has access to it. I don't know if it even
> does anything to tell you the truth. And the ROWLOCK should not be needed
> either. Your joining on a PK and unless your updating most of the table i
t
> should use rowlocks anyway. Hints should only be used where absolutely
> necessary. You need to prove to yourself they are needed before using the
m
> otherwise you risk getting poor performance overall.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:utDiYXb8DHA.488@.TK2MSFTNGP12.phx.gbl...
> table to be
> incompatible lock
> same. But, I'm
>|||"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23wEOE0b8DHA.452@.TK2MSFTNGP11.phx.gbl...
> What do you think you're gaining here?
I had hoped to mitigate the possibility of conversion deadlocks with this ap
proach
(deadlocks that are caused due to row-->page lock escalation). I was just c
urious to
learn whether specifying the hint at one or the other locations might have a
ny difference.

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

No comments:

Post a Comment