-
Sep 12th, 2022, 08:35 AM
#1
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.
Last edited by dbasnett; Sep 12th, 2022 at 02:02 PM.
-
Sep 12th, 2022, 02:17 PM
#2
Re: Concurrency
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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 13th, 2022, 01:59 AM
#3
Re: Concurrency
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
Last edited by Zvoni; Sep 13th, 2022 at 04:23 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|