03-30-07, 12:41 #1Registered User
- Join Date
- May 2004
- toronto, canada
Unanswered: Using cluster servers and preallocated space
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?
03-30-07, 16:53 #29th inning DBA
- 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 --