Originally Posted by
wqweto
This is extremely inefficient to hold *all* the generated sequences for each base instead of only the last generated number (for each base).
For SELECT-s if you want to lock the rows it touches you can use WITH(UPDLOCK, ROWLOCK, SERIALIZABLE) hint. These hints effectively raises isolation level to SERIALIZABLE so data in base tables stays stable for the duration of the transaction. It's "locked for update" so cannot be modified and cannot be "locked for update" by other connections. This means that if the same stored procedure is executed by another connection it will have to wait for the first one to complete its transaction before second one can "lock any overlapping rows on its own for update".
In your case the SELECT TOP 1 ... ORDER BY Seq DESC will effectively lock the whole table (by lock escalation) so a TABLOCK instead of ROWLOCK will be more appropriate for the "curious" design you've come up with and HOLDLOCK is a synonym (legacy keyword) for SERIALIZABLE so it looks good to me. Inefficient but will get the job done.
Btw, using WITH(UPDLOCK, ROWLOCK, SERIALIZABLE) on the SELECT will (theoretically) allow concurrently generating next sequences for different bases i.e. Base=200 vs Base=201
cheers,
</wqw>