Generating Unique IDs Safely Across Threads

overview use a shared parameter and increment it with interlockincrement to generate ids without collisions across concurrent threads this method delegates the increment to a stored procedure, which makes the update atomic at the database level rather than relying on in memory script logic applies to scripts that generate sequential ids environments where multiple threads may request a new id at the same time parameters stored in the provisioning system and backed by sql problem when multiple threads read and increment the same value independently, two or more threads can read the same current value before any of them writes the next one this creates duplicate ids cause a standard read update write sequence is not safe under concurrency the value must be incremented in a single atomic operation that all threads share resolution store the last issued id in a shared parameter and call interlockincrement on that parameter whenever a new id is needed parameter p = parent getlocalparameter("lastid"); int i = convert toint32(p value); p interlockincrement(10000); // 10000 is the maximum possible value trace writeline(i tostring()); how it works interlockincrement does not simply add one in script memory it calls a sql stored procedure named parameterincrement and passes @parameterid to identify the parameter being incremented @maxvalue to define the maximum allowed value the stored procedure returns the updated parameter value and timestamp, which are then written back to the in memory object relevant behavior from the method opens a new sql connection executes parameterincrement as a stored procedure reads the updated value from the result set updates the parameter timestamp closes the reader and connection in finally wraps failures in unable to increment parameter this means the increment is coordinated through the database, which is what provides thread safety across concurrent script execution important behavior the example reads the parameter value before calling interlockincrement int i = convert toint32(p value); p interlockincrement(10000); that means i contains the value before the increment in practice i is the id being issued for the current request interlockincrement advances the stored counter so the next thread gets a different value this pattern works only if all threads use the same shared parameter and all increments go through interlockincrement step by step create or identify a shared parameter such as lastid retrieve the parameter in script read the current value and use it as the next unique id immediately call interlockincrement(maxvalue) to advance the shared counter atomically persist or log the issued id as needed example parameter p = parent getlocalparameter("lastid"); int nextid = convert toint32(p value); p interlockincrement(10000); trace writeline(nextid tostring()); notes on maxvalue maxvalue is passed to the stored procedure and defines the upper limit for the parameter the exact behavior at that limit depends on the implementation of parameterincrement verify whether it wraps back to a starting value stops incrementing throws an error do not assume rollover behavior unless it is confirmed in the stored procedure logic error handling if the increment fails, the method throws throw new exception("unable to increment parameter", ex); handle this in calling code if id generation is critical to the workflow additional notes this approach is safe for concurrent threads because the increment is handled centrally in sql do not replace interlockincrement with a manual p value = update in multi threaded scenarios ensure every thread uses the same parameter name and same increment path if uniqueness must extend beyond a single shared counter scope, use separate namespaces or prefixes in addition to the numeric id