1

I am developing an API backend using ASP.NET Core Minimal API (.NET 7) with Entity Framework Core, HotChocolate (for GraphQL), and a Microsoft SQL Server database. I've encountered a specific challenge regarding data mutations and would appreciate any guidance or suggestions.

Problem:

In my application, users can submit multiple inputs (for demonstration purposes let's use just a number ranging from 1-10) per request for a particular mutation. These inputs are then grouped into sub-groups (e.g., 1-4, 5-8, 9-10, why and how the group a created doesn't matter for this question. ). Each sub-group is assigned a unique global identifier. These identifiers follow a specific format, such as GROUP-XXX-1, GROUP-XXX-2, etc., where the numerical part (e.g., 1, 2) needs to be the lowest available number that other groups have not used in the database.

Challenge:

I struggle to design an efficient and reliable way to reserve and assign these unique, sequential numbers for each request. The key requirements are:

Each number must be unique across all groups. The assigned number should be the lowest available number in the sequence.

Current Approach and Thaughts:

  • DB Sequence, but if a request is canceled then the number should not be included in the reservation.

Questions:

How can I implement a system to reserve and assign these unique, sequential numbers in a way that ensures uniqueness and minimizes gaps in the sequence? Are there any best practices or patterns that could simplify this task? Any insights, code snippets, or references to relevant documentation would be greatly appreciated.

I appreciate any help you can provide.

2
  • maybe im missing something but surely a auto inc primary key on your group table solves this trivially?
    – Ewan
    Jan 8 at 11:46
  • @Ewan I'd guess that an auto inc primary key probably generates out-of-order keys under some interpretation (transactions X and Y where X is begun before Y but Y is committed before X) but would likely be the best choice. Jan 8 at 15:22

1 Answer 1

3

DB sequence would be the right way, but you can't have these together:

  • No gaps
  • Monotonically incrementing IDs
  • Performance

If you give up on "no gaps", you can create IDs from a sequence and ignore the hopefully rare case that something goes wrong between requesting an ID and using it.

If you accept some out-of-order IDs, you can reserve them using a sequence and somehow detect when they have not been used, returning them to some kind of pool. You may also reserve IDs in chunks for better performance.

Otherwise, you can't allow reservation of a new ID before a reserved ID has been stored in the database. Performance will suck badly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.