2

This is a follow-up question to: Are SQL Server sequences thread safe?

I have two separate stored procedures that are calling the same sequence. The stored procedures are launched "in parallel" from an SSIS package. There is no synchronization of any kind between the two stored procedures (other than the fact that I guarantee that they'll never be updating the same rows, even though they are updating the same table). That being said, there's no particular reason that the sequence couldn't be called more or less simultaneously by the two stored procedures. My question is about exactly what would happen in this case.

In the case of the linked question, the OP had several producer applications that were simultaneously inserting into the table and wanted to know whether they could "count" on them being sequential between the processes (i.e. that if producer 2 called the sequence first, its ID would be smaller than producer 3). (This ended up not being the case due to a race condition due to the fact that generating the IDs and storing them were separate steps).

The same logic would presumably apply to my case (that I can't count on them to be in any particular "order" due to the fact that I also produce and store them in separate steps). In my case, however, I don't particularly care whether they're sequential, just that they're unique.

Can I count on that being the case? Are SQL Server sequences guaranteed to always produce unique values (even if called more or less simultaneously from different connections), or could there be some race condition here that would make this no longer be the case?

Edit: The same sequence number could ultimately be added to multiple rows if that matters (although it will always be added to at least one). I fetch the number from the sequence and then do an update query to add it to the rows that I want it to be part of.

EJoshuaS - Stand with Ukraine
  • 11,977
  • 56
  • 49
  • 78
  • They wouldn't be much use if they didn't produce unique values but I agree the documentation is a bit sparse on this. But what's motivating this question? Do you have an actual problem, the symptoms of which made you suspect it wasn't the case? If so, might be better to present the symptoms rather than guessing at a cause then asking whether the cause is possible. – Damien_The_Unbeliever Dec 09 '20 at 15:41
  • @Damien_The_Unbeliever I don't have a specific test case that suggests otherwise - I just have an application that requires a guarantee that it be the case (because I'll be using the number as an ID). – EJoshuaS - Stand with Ukraine Dec 09 '20 at 16:05
  • Don't want duplicates? Enforce it with a unique constraint. – SMor Dec 09 '20 at 16:23
  • @SMor The value from the sequence has to be unique, but the value in the table will *not* be unique. – EJoshuaS - Stand with Ukraine Dec 09 '20 at 16:25
  • 1
    @EJoshuaS-ReinstateMonica - "but the value in the table will not be unique.". Can you put them in a different table as well where they *would* be unique? It seems like that would be a good solution for your paranoia. – Ben Thul Dec 09 '20 at 22:20

2 Answers2

1

Yes, they do, and that's one of their most important feature, that is described in the documentation (emphasis is mine):

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

  • Each new value is generated based on the current seed & increment.

  • Each new value for a particular transaction is different from other concurrent transactions on the table.

Disclaimer: there is no guarantee that values are sequential.

GMB
  • 216,147
  • 25
  • 84
  • 135
1

If I read correctly, you are just ensuring they are unique (example: you want them to be a primary key?). If so, that is correct. As far as guaranteed order, you are correct that there are conditions, esp. under load, they will not be in a particular order. Does not sound like that is a big problem for you. As long as you are pulling the next value correctly, you are safe.

When I look at created sequences, I think of them like autonumbering in Oracle, where you have to pull the value and then utilize it, rather than IDENTITY in SQL Server (although there are ways to increment IDENTITY to "fill in the hole" later, so it can be utilized in the same/similar manner).

I have not examine the internals, but I would imagine the base sequence concept is utilized for IDENTITY underneath the hood, as the ideas are essentially the same, except IDENTITY is attached to a field in the table.

Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32