Results 1 to 3 of 3

Thread: Concurrency

  1. #1

    Thread Starter
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,754

    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.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    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
  •  



Click Here to Expand Forum to Full Width