Multi User updates

dbasnett 0
Viewed 29

We have a table that has reference numbers for documents. The simplified version of this table, call it RefNum, is,

id - int - identity
refN - smallint - the reference number
avail - bit - is number available (0 - is available, 1 - not available)

This table is pre-filled with refN's that have avail = 0. The reference number is available if avail is zero.

How do I write the SQL to select the next available reference number, and update it(set avail to 1), without worrying about two users getting the same number?

Can I simply wrap the SQL statements in a BEGIN / COMMIT TRANSACTION block?

I'm sure I'm over thinking this.

Thanks in advance.

sql-server
2 Answers
Atul Pathrikar0

I would also recommend a sequence but if you really have to use what is there then the following should work:

DECLARE @op TABLE (RefN smallint NOT NULL);

UPDATE RefNums
SET avail = 1
OUTPUT inserted.RefN  
INTO @op
WHERE RefN =
(
    SELECT MIN(RefN)
    FROM RefNums WITH (UPDLOCK)
    WHERE avail = 0
);

SELECT *
FROM @op;
Aardvark0
Related