UPDLOCK, HOLDLOCK combination hint

12 Jan 2007 1:53 PM

thejamie

In Goran Rissler's question earlier today (Transaction deadlock on
Tablelocks), a suggestion was made to use a locking hint combination. I do
not understand how a locking hint combination works. Does it use the first
hint first and apply the second hint when the first hint no longer applies?

--
Regards,
Jamie

12 Jan 2007 2:21 PM

Göran Rissler

"thejamie" <theja***@discussions.microsoft.com> wrote in message
news:0FD5BCDB-7885-4985-8D09-27066FB1B5FA@microsoft.com...
> In Goran Rissler's question earlier today (Transaction deadlock on
> Tablelocks), a suggestion was made to use a locking hint combination. I
> do
> not understand how a locking hint combination works. Does it use the
> first
> hint first and apply the second hint when the first hint no longer
> applies?
> --
> Regards,
> Jamie

There a multiple types of hints, some control the granularity of the locks
(TABLOCK, TABLOCKX, ROWLOCK, PAGELOCK) others control what type of lock to
take (NOLOCK, UPDLOCK, XLOCK..). You can only specify one of each type, so
one controling granulairy and one controling type. Then there are other
hints wich control how long to hold the lock HOLDLOCK

Hope that gives you an idea.

/Göran

12 Jan 2007 2:48 PM

thejamie

Then you can only apply one hint at a time?

Example:
Select * from ATable with (updlock, xlock) where situation=event

Is not a possibility...?

--
Regards,
Jamie

Show quoteHide quote

"GÃ¶ran Rissler" wrote:

>
> "thejamie" <theja***@discussions.microsoft.com> wrote in message
> news:0FD5BCDB-7885-4985-8D09-27066FB1B5FA@microsoft.com...
> > In Goran Rissler's question earlier today (Transaction deadlock on
> > Tablelocks), a suggestion was made to use a locking hint combination. I
> > do
> > not understand how a locking hint combination works. Does it use the
> > first
> > hint first and apply the second hint when the first hint no longer
> > applies?
> > --
> > Regards,
> > Jamie
>
> There a multiple types of hints, some control the granularity of the locks
> (TABLOCK, TABLOCKX, ROWLOCK, PAGELOCK) others control what type of lock to
> take (NOLOCK, UPDLOCK, XLOCK..). You can only specify one of each type, so
> one controling granulairy and one controling type. Then there are other
> hints wich control how long to hold the lock HOLDLOCK
>
> Hope that gives you an idea.
>
> /GÃ¶ran
>
>
>

12 Jan 2007 2:58 PM

Göran Rissler

> Then you can only apply one hint at a time?
>
> Example:
> Select * from ATable with (updlock, xlock) where situation=event
>
> Is not a possibility...?
> --

That is not possible no. It wouldn't know what to do. Read BOL it describes
what can be used together.

/Göran

Thread options