I have a situation where I need to reserve a set number of sequential auto generated IDs. So for example I might need to reserve the IDS

1,2,3,4 and ensure that no other process can obtain these IDs.

I thought that something like:

begin transaction;
INSERT INTO Test WITH (tablockx) (in_use) VALUES (1),(1),(1),(1)
commit; -- until this commit is done access to the table is denied

This would give an lock on the table until I've grabbed the next 4 ids and committed the transaction.

This would be done in a web application, I guess the downside is that if the process fails then we'd be left with a table that's locked.

Does anyone know if there's a better way of reserving the ids? The number to reserve is not fixed and can and will change over time.