Concurrency

[Zvoni]

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