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.