Creating a numeric sequence based on the date of a corresponding transaction - a step-by-step guide

Question

Creating a numeric sequence based on the date of a corresponding transaction - a step-by-step guide

INTRO

I built an e-commerce app with TypeScript and Sequelize ORM. In the app, I have a table that generates sequential invoice numbers based on the current day.

CREATE TABLE `dm_generate_trx` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `increment_value` int NOT NULL DEFAULT '0',
  `flag_hit` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

This is how the table looks like:

|id |date      |increment_value|flag_hit|created_at         |updated_at         |
|---|----------|---------------|--------|-------------------|-------------------|
|118|2022-05-19|1              |N       |2022-05-19 10:10:21|2022-05-19 10:10:21|
|126|2022-05-19|2              |N       |2022-05-19 10:10:54|2022-05-19 10:10:54|
|134|2022-05-19|3              |N       |2022-05-19 10:11:37|2022-05-19 10:11:37|
|142|2022-05-20|1              |N       |2022-05-20 08:12:14|2022-05-20 08:12:14|
|150|2022-05-20|2              |N       |2022-05-20 08:12:34|2022-05-20 08:12:34|

In my TypeScript code, I have a query to generate the invoice number daily starting from 1 each day.

const count = await dm_generate_trx.count({
        where: {
          createdAt: {
            [Op.gt]: todayStart, // will be "DATE NOW 00:00:00"
            [Op.lt]: now,
          },
        },
        transaction,
      });
      const currentCount = count + 1;

The SQL equivalent would be:

SELECT count(*) FROM dm_generate_trx 
WHERE createdAt > 'DATE NOW 00:00:00' AND createdAt < 'DATE NOW HH:MM:SS'

I then add 1 to this count and insert it into the database.

ISSUE

The problem arises when multiple transactions run almost simultaneously, resulting in duplicates because the script doesn't capture the initially inserted value.

I expect the data to display as below:

...

id	date	increment_value	flag_hit	created_at	updated_at
118	2022-05-19	1	N	2022-05-19 10:10:21	2022-05-19 10:10:21

However, the actual data shows duplicates.

...

id	date	increment_value	flag_hit	created_at	updated_at
118	2022-05-19	1	N	2022-05-19 10:10:21	2022-05-19 10:10:21

How can I address this issue? I've tried using

ISOLATION_LEVELS.READ_UNCOMMITTED

without success. Should I resolve this at the code level or within the database?

mysql typescript transactions duplicates

Answer 1

Answer №1

It appears that you are dealing with a race condition in your current setup.

Simply using the READ_UNCOMMITTED option will not eliminate this issue, as there is still a small window of time between reading the current value, incrementing it in Typescript, and then inserting it into the table. This brief moment may seem insignificant, but trust me — problems can arise.

There's a saying that goes, "One in a million is next Tuesday." Even if an event seems highly unlikely, when certain conditions are repeated many times, it becomes more probable. You may want to explore the concept of the Birthday problem.

To prevent concurrent transactions from encountering a race condition, some form of locking mechanism is necessary. One transaction must wait until another completes before proceeding.

In MySQL, the AUTO_INCREMENT feature is commonly used to address this issue. It allows multiple sessions to insert into the same table simultaneously by keeping track of the latest auto-incremented ID. However, this feature does not support resetting an incrementing number daily. In such cases, each session would need to lock the table, retrieve the current maximum value, increment it, and then insert the updated value. The LOCK TABLES statement can be utilized for this purpose.

Unfortunately, there is no straightforward transactional solution to this problem, as a race condition would remain a concern.

* I have yet to encounter a valid use case for the READ_UNCOMMITTED setting.

Answer 2

It appears that you are dealing with a race condition in your current setup.

Simply using the READ_UNCOMMITTED option will not eliminate this issue, as there is still a small window of time between reading the current value, incrementing it in Typescript, and then inserting it into the table. This brief moment may seem insignificant, but trust me — problems can arise.

There's a saying that goes, "One in a million is next Tuesday." Even if an event seems highly unlikely, when certain conditions are repeated many times, it becomes more probable. You may want to explore the concept of the Birthday problem.

To prevent concurrent transactions from encountering a race condition, some form of locking mechanism is necessary. One transaction must wait until another completes before proceeding.

In MySQL, the AUTO_INCREMENT feature is commonly used to address this issue. It allows multiple sessions to insert into the same table simultaneously by keeping track of the latest auto-incremented ID. However, this feature does not support resetting an incrementing number daily. In such cases, each session would need to lock the table, retrieve the current maximum value, increment it, and then insert the updated value. The LOCK TABLES statement can be utilized for this purpose.

Unfortunately, there is no straightforward transactional solution to this problem, as a race condition would remain a concern.

* I have yet to encounter a valid use case for the READ_UNCOMMITTED setting.

Answer 3

Answer №2

In my opinion, there are a couple of routes you could take:

Determine the updated increment value and perform the insertion in a single transaction on the client side.
Develop a stored procedure and execute it within a single database transaction.

Utilizing the READ_UNCOMMITTED isolation level may not be ideal here. It's crucial to avoid uncommitted data when dealing with count information. The chosen isolation level should reflect the data that has been officially committed.

Answer 4

In my opinion, there are a couple of routes you could take:

Determine the updated increment value and perform the insertion in a single transaction on the client side.
Develop a stored procedure and execute it within a single database transaction.

Utilizing the READ_UNCOMMITTED isolation level may not be ideal here. It's crucial to avoid uncommitted data when dealing with count information. The chosen isolation level should reflect the data that has been officially committed.

Answer 5

Answer №3

Option A:

Create a database table to store the sequential numbers with proper transaction handling to prevent concurrency issues. The table should include columns for the year and the last used sequence number.

If it's crucial to avoid missing invoice numbers for accounting purposes, consider implementing a system to assign a "VOIDed" label to any lost invoice numbers.

Option B:

Explore alternatives to using the year + sequence number combination for generating invoice numbers.

(Disclaimer: I lack an Accounting background, so I may not be aware of all the specific requirements.)

Answer 6

Option A:

Create a database table to store the sequential numbers with proper transaction handling to prevent concurrency issues. The table should include columns for the year and the last used sequence number.

If it's crucial to avoid missing invoice numbers for accounting purposes, consider implementing a system to assign a "VOIDed" label to any lost invoice numbers.

Option B:

Explore alternatives to using the year + sequence number combination for generating invoice numbers.

(Disclaimer: I lack an Accounting background, so I may not be aware of all the specific requirements.)

Answer 7

Answer №4

If you are working with MY SQL, consider utilizing a secondary auto increment feature specifically designed for it. In this scenario, focus on the date and increment value.

Explore the implementation of AUTO_INCREMENT

Additionally, if the id column is also necessary, you can create a trigger to handle that aspect.

Your queries could resemble something similar to the following:

CREATE TABLE `dm_generate_trx` (
  `id` int,
  `date` date NOT NULL,
  `increment_value` int NOT NULL AUTO_INCREMENT,
  `flag_hit` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`, `increment_value`)
)

CREATE TRIGGER dm_generate_trx_id
BEFORE INSERT ON dm_generate_trx
FOR EACH ROW
BEGIN
      DECLARE nid INT;
      SELECT  COALESCE(MAX(id), 0) + 1
      INTO    nid
      FROM    dm_generate_trx

      
      SET NEW.id = nid;
END;

I hope this information proves to be helpful.

Answer 8

If you are working with MY SQL, consider utilizing a secondary auto increment feature specifically designed for it. In this scenario, focus on the date and increment value.

Explore the implementation of AUTO_INCREMENT

Additionally, if the id column is also necessary, you can create a trigger to handle that aspect.

Your queries could resemble something similar to the following:

CREATE TABLE `dm_generate_trx` (
  `id` int,
  `date` date NOT NULL,
  `increment_value` int NOT NULL AUTO_INCREMENT,
  `flag_hit` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`date`, `increment_value`)
)

CREATE TRIGGER dm_generate_trx_id
BEFORE INSERT ON dm_generate_trx
FOR EACH ROW
BEGIN
      DECLARE nid INT;
      SELECT  COALESCE(MAX(id), 0) + 1
      INTO    nid
      FROM    dm_generate_trx

      
      SET NEW.id = nid;
END;

I hope this information proves to be helpful.

Creating a numeric sequence based on the date of a corresponding transaction - a step-by-step guide

Answer №1

Answer №2

Answer №3

Answer №4

Similar questions

Angular2 Interactive Modal Pop Up

Utilizing a single mysql connection across client and server JavaScript files within a node.js environment

Insert data into Typeorm even if it already exists

having difficulty incorporating if/else statements in a SQL query

Efficient File Upload Feature Compatible with all Browsers, Including Internet Explorer versions 7, 8, and 9

Typescript - neglecting a package that lacks typings

Retrieve the Ionic storage item as a string

Arranging a Multidimensional Array Based on Array Values

The parameters provided for ionic2 do not align with any acceptable signature for the call target

What is the way to send custom properties to TypeScript in combination with StyledComponents?

Even when there is a change in value within the beforeEach hook, the original value remains unchanged and is used for dynamic tests

Inject Angular 2 component into designated space

What is the method for retrieving the second type of property from an array of objects?

Creating a TypeScript class with methods to export as an object

Uploading selected radio buttons to the database

I am having trouble getting MySQL to accept Excel's date format when inserting

Make an indirect mention of a distant JavaScript web address

Tips for integrating tsconfig with webpack's provide plugin

closing custom components in Ag-Grid React columns

Indexing a JSON array column in MySQL with the possibility of non-JSON data present