0

I currently am incrementing a column for order number values with the increment() function in Sequelize. ie: .increment("order_no", { by: 1 });

Occasionally a request hits my api at the exact same second and I end up with a duplicate order number value.

Is there any way that I can guarantee that the increment value is unique for an order number? Do I need to wrap the increment in a transaction? Should I have a random timed check after creating the number value to see if there is a duplicate? Not sure of the right way to solve this.

From Sequelizes documentation (https://sequelize.org/docs/v6/core-concepts/model-instances/#incrementing-and-decrementing-integer-values)

In order to increment/decrement values of an instance without running into concurrency issues, Sequelize provides the increment and decrement instance methods.

However, while this appears to be working, I still get duplicate values out when being utilized.

smb
  • 539
  • 2
  • 13
  • 33
  • 2
    MySQL has built in Auto Increment function .. why not use that as the Index as well? It will not allow duplicates be default so no additional code required, just pass it a NULL on inserts, and you get the next value automatically. Let the DB control the serials .. not the front end .. – easleyfixed Jul 14 '23 at 13:48
  • 2
    If the serials are just numbers and integers at that, Auto Incrememnt is the way to go, and if you need the value of the insert you can do a simple stored procedure to insert then select_last_id; so u can get the key u just created. This would keep duplicates from happening because the server controls the values and will not allow a duplicate so its just built in, using a front end to do it is like re-designing the wheel. Maybe i am missing something but I really dont understand why not use the built int feature. If you make them the primary key too would be smart. – easleyfixed Jul 14 '23 at 14:01
  • 1
    Let me know if this something you would consider, and I will type up an answer. – easleyfixed Jul 14 '23 at 15:25
  • Please provide `SHOW CREATE TABLE` and the generated SQLs. I would like to analyze the problem from the database engine's point of view. Be sure to include any `START TRANSACTIONa` and `COMMITs` around SQLs. – Rick James Jul 14 '23 at 23:43
  • Do you have a separate class instance = object = table row -- just to track the current "order_no"? Please also provide that class definition and the code that instantiated the instance. – Rick James Jul 15 '23 at 00:00
  • If duplicate values in a column are not allowed, then then column should have a unique index. – Georg Richter Jul 17 '23 at 04:58

2 Answers2

0

I think you can try to catch the error if duplicate order id occurs and retry the operation and generate a new order id.

dev0717
  • 177
  • 4
0

If I am getting this right, You have an orders model/table that tracks orders. Each newOrder request that hits your API asks sequelize(Order model) to manually increment the order_no column. If two such requests hit the API at the same time, they insert the same value into your orders table twice.

So you want to add "unique key" restrictions to this 'order_num' column but this is not an efficient way to uniquely identify your orders. Whether you use increment...by or increment/decrement functions, sequelize will ultimately query the database with the same values.

In my opinion, you shouldn't be doing this.

Instead, add a order_no column, auto-increment the 'order_no' column. Sequelize supports autoincrement functionality for all dialects. Check out this post: Auto increment id with sequelize in MySQL.

To uniquely identify "orders", generate a random unique ID for each incoming order. It's better than trying to have unique integral "order_no". Check out: https://dev.to/gulshanaggarwal/npm-packages-to-generate-unique-ids-for-your-next-project-1p3b.

With unique "order_id"s, You don't even need the order_no autoincrement column.

To count the number of orders in certain day, combine Sequelize's model querying methods like findAndCountAll plus where clauses based on a the createdAt timestamps.

You can do other tasks you need like sorting, counting the number of orders, finding the latest or earliest orders, etc.

These are well documented in the sequelize docs website.

Consider, redesigning your schema for better efficiency.

If this doesn't help, you can help us help you more by sharing the 'model instance' code, and the increment code in your API.

Hiren Madara
  • 21
  • 3