Atomic exclusive SQL record update

Question

I want to update a single record in a table to reflect that a given client session has acquired the record (and now owns it for further updates) within a multi-session environment. I've got this so far:

create procedure AcquireRow(
  @itemNo   int,          -- Item ID to acquire
  @sessNo   int,          -- Session ID
  @res      char(1) out)  -- Result
as
begin
  -- Attempt to acquire the row
  update Items
    set
      State = 'A',      -- 'A'=Acquired
      SessionID = @sessNo
    where ItemID = @itemNo
      and State = 'N';  -- 'N'=Not acquired  

  -- Verify that the session actually acquired the row
  set @res = 'T';       -- 'T'=Success
  if @@rowcount = 0
    set @res = 'F';     -- 'F'=Failure
end;

The out variable @state is set to 'T' if the procedure successfully acquired the row, otherwise it's set to 'F' to indicate failure.

My question: Is this guaranteed to work atomically, so that only one session successfully acquires (updates) the row if several sessions call AcquireRow() at the same time? Or is there a better way of doing this? Do I need an explicit rowlock?

Amended:
Based on Remus's answer, I would rearrange the code thus:

set @res = 'F';
update ...;
if @@rowcount > 0
    set @res = 'T';

Using an output clause or assigning the resulting row's ItemID to a variable within the update would also be prudent.

Answer 1

UPDATE statements in SQL server acquire an update lock while the database engine is reading the rows that need updating, which is converted into an exclusive lock when the writing occurs.

When an exclusive lock is on a row, all other transactions are blocked from reading and writing it (unless the reading transaction is at READ UNCOMMITTED isolation, or a NOLOCK hint is used).

So yes, as it stands, your UPDATE statement is an atomic autocommited transaction, so this should be fine in regards to multiple sessions calling it at the same time. If you were to break it into multiple statements for any reason, you'd need to make sure you explicitly created a transaction in your SP.

Remus' comments regarding @@ROWCOUNT and general usage of "acquire" are pretty solid advice though.

Answer 2

@@ROWCOUNT is notoriously easy to get wrong. For example, in your case. From MSDN:

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable ...

To be correct you should check the @@ROWCOUNT immediately after the UPDATE. It is safer to use a ROWLOCK but is not necessary. Even if the optimizer decides to use page locks, the 'acquire' semantics are correct.

I would probably prefer another approach, namely to use the OUTPUT clause of UPDATE:

declare @updated table (ItemId int);

update Items
set ...
output inserted.ItemId
into @updated (ItemId)
where ...

This scheme is more error proof and also more flexible, as it allows for acquiring of unknown ItemId: the id acquired is placed in the @updated table variable and can be returned to caller.

As a general note, using real, committed, updates for 'acquire' is riddled with problems as you cannot know what rows are really acquired and which ones are just abandoned (client disconnected or crashed w/o releasing the 'acquisition').