表中的多个唯一计数器
我遇到一个问题，我需要保留并增加表中的对象编号字段，但是该编号在给定逻辑域内应该是唯一的，而不是在全局表中唯一的。
一个例子是多个企业调度多个工作； Job.Number 在企业内应该是唯一的。
所以我基本上需要确保并发作业创建操作不会产生具有相同编号的作业。
目前我只看到一种实现方法（在 postresql 中）：
使用自锁类型的锁锁定表，例如“SHARE UPDATE EXCLUSIVE”，因此该类型的所有其他操作都必须排队并等待，从而确保 MAX () 函数始终返回唯一值。
然而，该解决方案似乎有一个巨大的缺点 - 它本质上为所有插入作业表的操作造成了瓶颈。
我不认为我可以使用 Postgreql 序列，因为：
- 我不想为每个新业务创建一个新序列
- 它可能有间隙
您会建议任何其他方法来处理该问题吗？
I have a problem where I need to keep and increment an object number field in a table, however this number should be unique within given logical domain, not across table globally.
An example would be multiple Businesses scheduling multiple Jobs; Job.Number should be unique within a business.
So I basically need to make sure that concurrent job creation operations do not produce Jobs with the same Number.
Currently I see only one way to implement that (in postresql):
Lock the table with a self-locking type of lock, say "SHARE UPDATE EXCLUSIVE" so all the other operations of this type have to queue and wait, thus ensuring that MAX() function always returns unique value.
However there seems to be a huge drawback in that solution - it essentially creates a bottleneck for all INSERT operations into Jobs table.
I don't think I can use Postgreql sequences, because:
- I do not want to create a new sequence for each new business
- It can have gaps
would you suggest any other ways to deal with that problem?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
首先，如果您需要的只是一个不同的数字，为什么不使用序列来生成它呢？
如果共享顺序不行，因为会产生“间隙”（即，业务#1 作业可能编号为 1、2、5、6、23，业务#2 作业可能编号为 4、7、8、20 等）或者出于某种原因，为什么不构建一个“作业计数器”表：
因此，当您必须为 Business #2 生成下一个作业时，您必须仅锁定 Business#2 行，递增它，然后继续。
假设 Postgres 可以锁定记录级别，这样就可以使事情更具可扩展性。
First of all, if all you need is a distinct number, why don't you use a sequence to generate it?
If a shared sequence is not ok, because will generate "gaps" (i.e. Business #1 jobs could be numbered 1,2,5,6,23 and Business #2 jobs could get 4,7,8,20 and so on) or for whatever reason, why don't you build a table of "job counters":
So when you have to generate the next Job for Business #2 you have to lock only the Business#2 row, increment it, and proceed.
Assuming Postgres can lock at the record level, you would make things more scalable this way.
SELECT * FOR UPDATE声明？
另外我想注意到 CREATE SEQUENCE 有一个“CACHE”选项，因此如果您担心一些间隙，它可以更快地工作。
What about SELECT * FOR UPDATE statement?
Also I want to notice that CREATE SEQUENCE has a "CACHE" option, so it can work faster if you worries about some gaps.