Concurrency

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
available - bit - is number available

This table is pre-filled with refN's that have available = 0.

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

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

Any help is appreciated.

I don't think it will work with a simple select.I do not think a simple select will hold a unique value from everyone.
What I'm thinking is first Begin tran then update the first available 0 , then get it's update output and then commit.
Of course you will use where available = 0 with top 1 before the update so at the commit you with have the unique update output generated by the update statement .
That's a thought, maybe something else is possible but I think that's also a valid way to go.

P.S. In case you wonder about update output: https://www.anycodings.com/1question...-in-sql-server

My first try would be:
1) Start Transaction (To get isolation, preferably serializable)
2) SELECT Min(RefN) As MinRef FROM RefNum WHERE available=1 --> get the lowest available RefNum
2a) If none is available, take the next one and set it to not available --> INSERT INTO RefNum('RefN','Available') VALUES((SELECT Max(RefN) FROM RefNum) +1, 0) --> leave out ID since that one should be autoincrement, MaxRef+1 is without "available"-Filter, maybe even inserting a new "bulk" of RefN, set available to 1 except the first one
2b) Set Available for this RefN immediatly to 0 --> UPDATE RefNum SET available=0 WHERE RefN=MyMinRef --> depending on isolation level this should avoid a concurring read to catch this Record
3) Do whatever you want with that refN
4) Commit