Results 1 to 2 of 2

Thread: Reserving a set number of sequential auto generated IDs

  1. #1
    Join Date
    May 2019
    Posts
    1

    Reserving a set number of sequential auto generated IDs

    Hello,

    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.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Oracle allows you to cache a block of sequences. If you are using SQL Server then you should use table like you are proposing. But if you do not use the sequence then you may have gaps.

Posting Permissions

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