当前位置： 文江博客话题详情

如何根据并行交易的日期创建一个顺序数字？

发布于 2025-02-02 06:48:50 字数 4007 浏览 5 评论 0原文

概述

我有一个购买某些商品等的电子商务应用程序。我使用Typescript和ORM续集创建该应用。因此，我有此示例表，该表将根据当前日插入发票数字顺序。

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`)
)

桌子看起来像这样。

|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|

因此，在创建发票编号之前，在我的打字稿代码中，我有此查询。因此，如果更改日期，则每天都会再次开始（就像那里的桌子一样）。

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

或在SQL中，它就是这样

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

，然后我将以1总和为1

const currentCount = count + 1;

，然后将其插入数据库。

问题

当有两个或多个交易同时运行时，我会遇到问题，脚本也在大部分时间同时运行和提交，也许只有毫秒不同。增量会复制，因为运行计数查询脚本时的事务不会捕获第一个插入的值。

我期望的结果将是

ID日期cremement_valueflag_hitcreate_atupdated_at
1182022-05-191N2022-05-19 10:10:10:21 2022-05-19 10:10:21
1262022-2022-292N 2 N 2 N 2 N2022-05-19 10:10:542022-05-19 10:10:54
1342022-05-193N N N2022-05-19 10:11:372022-05-05-19 10:11:37
135135 202222222222222222222 -05-194N2022-05-19 10:12:372022-05-19 10:12:37
1422022-05-201N2022-05-05-20 08:12:142022-05-05-20 0888:14 ：12：14
1502022-05-202N2022-05-20 08:12:342022-05-20 08:12:34

但是相反，我得到了这样的重复数据。

id日期递增_valueflag_hitcreate_atupdated_at
1182022-05-191N2022-05-19 10:10: 10:21 2022-05-19
10:10:10:21 1262022-05-191N2022-19 10:10 10:10 10:10 10:10 ：542022-05-19 10:10:54
1342022-05-191N2022-05-19 10:11:372022-05-19 10:11:37
1352022-05-192N2022-- 2022-- 05-19 10:12:372022-05-19 10:12:37
1422022-05-201N2022-05-05-20 08:12:142022-05-05-20 08:12:14
15020222-050 ​​2022-055 -201N2022-05-20 08:12:342022-05-20 08:12:34

如何解决此问题？我已经尝试了sibal_levels.read_uncommitten，但仍然没有工作。我必须使用代码脚本或数据库解决此问题吗？

原文

OVERVIEW

I have this e-commerce app that buy some goods and such. I create the app using typescript and ORM Sequelize. So I have this example table that will insert generated invoice number sequential based on current days.

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`)
)

The table would look like this.

|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|

so in my typescript code I have this query before created the invoice number. So each day if the date is changed, the incremental is start to one again (just like the table up there).

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

Or in SQL it would be like this

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

And then I would sum with 1 and the result like this line

const currentCount = count + 1;

And then I would insert it into database.

PROBLEM

I got a problem when there are two or more transaction that run at the same time, the script also run and commit at the mostly-exact same time, maybe just milliseconds different. The incremental will duplicate because the transaction when run the count query script, didn't catch the first inserted value.

What I would expect the result will be

iddateincrement_valueflag_hitcreated_atupdated_at
1182022-05-191N2022-05-19 10:10:212022-05-19 10:10:21
1262022-05-192N2022-05-19 10:10:542022-05-19 10:10:54
1342022-05-193N2022-05-19 10:11:372022-05-19 10:11:37
1352022-05-194N2022-05-19 10:12:372022-05-19 10:12:37
1422022-05-201N2022-05-20 08:12:142022-05-20 08:12:14
1502022-05-202N2022-05-20 08:12:342022-05-20 08:12:34

But instead, I got the duplicate data like this.

iddateincrement_valueflag_hitcreated_atupdated_at
1182022-05-191N2022-05-19 10:10:212022-05-19 10:10:21
1262022-05-191N2022-05-19 10:10:542022-05-19 10:10:54
1342022-05-191N2022-05-19 10:11:372022-05-19 10:11:37
1352022-05-192N2022-05-19 10:12:372022-05-19 10:12:37
1422022-05-201N2022-05-20 08:12:142022-05-20 08:12:14
1502022-05-201N2022-05-20 08:12:342022-05-20 08:12:34

How to solve this issue ? I have tried ISOLATION_LEVELS.READ_UNCOMMITTED and still didnt work. Do i have to solve this using code script or database ?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

望笑 2025-02-09 06:48:51

您拥有的是种族条件。

使用read_uncommitting无法解决此*，因为您在读取当前值，将其递增的内容之间有一个短暂的时刻，然后将其插入表中。那一刻似乎是如此简短，以至于不可能发生问题，但是请相信我 - 会。

关于不太可能的事件有一个古老的俗语：“下周二是一百万中的一个。 '将比您想象的要早。您还应该阅读有关生日问题。

如果没有某种锁定，您就无法解决此问题，以确保并发交易不在比赛条件下。一个必须等​​待，而另一个人承担其价值。

通常在MySQL中使用自动插入功能来解决。每个会话都可以在并发交易中插入同一表。该桌子跟踪了最近分配的自动收入ID。插入物获得了一个锁锁仅足以生成下一个ID，然后释放该表锁定。

但是自动灌溉功能不支持您想要的内容，这是每天1个重新启动的增量数字。为此，您必须使每个会话锁定表，读取当前的最大值，将其递增，最后插入增量值。您可以使用 lock表语句。

没有传统的交易方式来解决此问题，因为您仍然会有种族条件。

*实际上，我从未见过read_uncomments的合法用途。

What you have is a race condition.

Using READ_UNCOMMITTED doesn't solve this*, because you still have a brief moment between reading the current value, incrementing it in Typescript, and then inserting it into the table. That moment might seem so brief that it's impossible for a problem to occur, but trust me — it will.

There's an old saying regarding unlikely events: "One in a million is next Tuesday." Even if an event is extremely rare, if you repeat the conditions that could lead to the event many, many times, it'll happen sooner than you think. You should also read about the Birthday problem.

You can't solve this without some kind of locking to ensure the concurrent transactions are not in a race condition. One has to wait while the other commits its value.

Typically in MySQL this is solved with the AUTO_INCREMENT feature. Each session can insert to the same table in concurrent transactions. The table keeps track of the most recently allocated auto-increment id. The insert acquires a table lock only long enough to generate the next id, then it releases that table lock.

But the auto-increment feature doesn't support what you want, an incrementing number that restarts from 1 each day. To do that, you have to make each session lock the table, read the current max value, increment it, and finally insert the incremented value. You can do this with the LOCK TABLES statement.

There is no conventional transactional way of solving this, because you would still have a race condition.

* In fact, I have never seen a legitimate use for READ_UNCOMMITTED.

回复 原文
红焚 2025-02-09 06:48:51

因此，我认为您有两个选择：

  1. 计算新的regrement_value，然后在单个客户端事务中进行插入。
  2. 创建一个存储过程，然后在数据库中的单个事务中执行此操作。

read_uncomments隔离级别感觉错误。弄清楚计数数据时，您不需要肮脏的读数。您希望隔离级别告诉您已经承诺的事情。

So, I think you have two options:

  1. Compute the new increment_value then do the insert within a single client transaction.
  2. Create a stored procedure, and do this within a single transaction in the database.

The READ_UNCOMMITTED isolation level feels wrong. You wouldn't want dirty reads when figuring out count data. You want the isolation level to tell you what has already been committed.

回复 原文
画离情绘悲伤 2025-02-09 06:48:51

计划A：

构建一个数据库表以保持序列编号。使用交易避免种族条件。该表可能具有2列：年度和最后使用的序列编号。

如果您不能允许缺少发票号码（出于会计原因），我建议您提供一种将“无效”发票分配给任何丢失的数字的方法。

计划B：

避免发票编号的年度 +序列编号。

（抱歉，我没有会计背景，所以我不知道所有必要的细节。）

Plan A:

Build a database table for keeping the sequence number in. Use transactions to avoid race conditions. The table might have 2 columns: the year and the last used sequence number.

If you cannot allow missing invoice numbers (for accounting reasons), I suggest you provide a way to assign a "VOIDed" invoice to any number that is lost.

Plan B:

Avoid the year + sequence number for invoice numbers.

(Sorry, I have no Accounting background, so I do not know all the necessary details.)

回复 原文
黯淡〆 2025-02-09 06:48:51

专门用于我的SQL，您可以使用二级自动增量
在您的情况下，日期和增量值

使用auto_increment

，如果也需要 id 列 light 您可以为

查询写一个触发器，应该像

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;

希望一样的东西

Specifically for MY SQL you can use secondary auto increment
In your case date and increment value

Using AUTO_INCREMENT

and if id column is required too you may write a trigger for that

Queries should be something like

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;

Hopes that helps

回复 原文
~没有更多了~

关于作者

早乙女

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文