Can any one give me any URL where I can find the locking Hint with example.

I have a two query in one my stored procedure and in the that its returning
session ID to users like.

Set @SessionID = (Select TOP 1 SessionID from Session)

UPDATE Session
Set UsedYN = 1
where SessionID = @SessionID

Select SessionID from Session

What my concern is if any user call this stored procedure then it wont
return the same session id to more than one user. What if I can rewrite the
same query like that .

set begin transaction

Set @SessionID = (Select TOP 1 SessionID from Session (UPDLOCK) Where UserYN
= 0)

UPDATE Session
Set UsedYN = 1
where SessionID = @SessionID

Commit Transaction

Can any guide gave his comments regarding above queries and I want to clear
my locking concepts, I would really appreciate if any one send me the URL
that contains this sort of example and locking HINT.

Thanks in advance.

What you are doing seems a bit odd...

Using a TRANSACTION may help in your situation. However, you don't need to
use any locking hints (such as UPDLOCK). By starting a TRANSACTION , the
TRANSACTION is controlling the necessary locks.

If you are experiencing serialization problems, you may wish to investigate
the TRANSACTION ISOLATION LEVEL settings in Books Online.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"Rogers" <naissani (AT) hotmail (DOT) com> wrote

Not sure exactly what you're looking for but the lock hints are explained
here:
http://msdn2.microsoft.com/en-us/library/ms187373.aspx
I think you should also look at the READPAST hint for your application.
In SQL 2005 you can do an update top 1 with output that would be quite a bit
more efficient.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Rogers" <naissani (AT) hotmail (DOT) com> wrote

On Wed, 22 Nov 2006 11:57:51 -0800, Arnie Rowland wrote:

Hi Arnie,

This is not true.

With standard isolation level (READ COMMITTED), embedding the statements
in a transaction would not help. The shared lock requested for the first
SELECT (to get the TOP 1 SessionID) is released as soon as the statement
finishes. Other transaction might be reading the same TOP 1 value at the
same time as this one (since the lock is shared) and obtain an exclusive
lock between the release of the shared lock after the SELECT has
executed and the acquiring of an exclusive lock for the UPDATE that
follows.

With higher isolation levels, it only gets worse. For instance, with
isolation level SERIALIZABLE or REPEATABLE READ, the shared lock
acquired for the SELECT will be held, so now there can be two
connections both acquiring the shared lock first, both requesting an
upgrade to an exclusive lock for the UPDATE ... boom! deadlock.

--
Hugo Kornelis, SQL Server MVP

On Wed, 22 Nov 2006 08:44:18 -0500, Rogers wrote:

Hi Rogers,

Are you aware that the result of a TOP clause without accompanying ORDER
BY is completely undefined?

You definitely need to make sure to acquire an exclusive lock, or at
least an update lock (as you do with the UPDLOCK hint in your revised
code) to make sure that nobody else gets the same SessionID. Also, do
take a look at the READPAST hint as Roger advises. If you are doing what
I think you are doing, you'll benefit from it.

--
Hugo Kornelis, SQL Server MVP