1

Need to create sequence so what the code was currently doing select max(name) from table where item1 = '' and item2= '' and item3 = '' . After fetching max then it insert the element starting from max. But it will lead to concurrency issue.

Note: The query will always be perform in same parition

Currently i have two approach:

One to create a sequence table which will contain the ID and sequence as column. Sequence column will contain the last sequence number for that ID. This will be updated using optimistic concurrency.

Second one is to use stored procedure.

But still i am looking for some more better approach if that is present.

dj Bravo
  • 198
  • 1
  • 3
  • 18
  • Not quite sure why you need a sequence number but... unless you're using something like Strong consistency, coupled with a single writer, I'm not sure you're going to solve this (stored procedures aren't going to help, especially when you cut across partition boundaries). Have you considered a guid instead (which eliminates all of your consistency issues)? – David Makogon Feb 07 '22 at 20:00
  • @DavidMakogon it will always be in same partition. Actually the name is like 000, 001, 002 etc which can't be change to guid because that is being used in system. – dj Bravo Feb 08 '22 at 04:00

1 Answers1

0

There is no better approach other than complicate things dramatically with semaphores etc, but I think you approach with Optimistic Concurrency Control will work fine.

Stored procedures will not help you in this instance at all.

ChangeFeed can be one way to update things after the fact but I dont see how this helps you unless you make a seperate document keeping track of current number etc but that seems like over kill for what you want.

Matt Douhan
  • 677
  • 3
  • 13