Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    toronto, canada

    Unanswered: Using cluster servers and preallocated space

    Hi All,

    I am doing some design work on a new system. The plan is to use clustered servers accessing the same backend instance. I want to prepoulate the table with our unique id (for say 5M rows for sake of discussion). Where I am puzzled is how do I guarantee unique ids to the application when both servers are looking for the same next available row? The sql could be something like

    select min(id) from table where user_id is null

    The trouble our current system runs into is that there are occasions where the users generate the same id which of course messes up the application.

    Is this a case where a store procedure would do the insert and return the id to the user is a better solution?


  2. #2
    Join Date
    Jan 2004
    In a large office with bad lighting
    Create a table that holds 1 row and 1 column ... the next available id. As the proc starts, begin a transaction, get the value into a variable, then update the table with the next incremented value to use, then commit the transaction.

    That prevents any other procs from obtaining any values while the current proc is getting the next value to use.

    Thsi will scale in a low to moderatly high use systems. Ultra-high volume systems will end up blocking as several procs wait on the one to finish.

    Do not pre-populate a table ... that would not be a best-practice implementation for the scenario yoiu described.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts