Multi User updates

Question

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.

you need to lock see https://stackoverflow.com/questions/1483725/select-for-update-with-sql-server — nbk, Sep 12 '22 at 19:26
see then link, the is described how to lock the table for update in a ulti user enviroment — nbk, Sep 12 '22 at 20:39

score 0 · Answer 1 · answered Sep 13 '22 at 07:02

0

use sequence for Ref number & user sequence for update

check below URL

Link : https://www.c-sharpcorner.com/blogs/create-sequence-in-sql

Sequence generate Unique number every time

answered Sep 13 '22 at 07:02

Atul Pathrikar

1
3

score 0 · Accepted Answer · answered Sep 13 '22 at 09:15

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;

Multi User updates

2 Answers2