在MySQL中严格自动递增值
我必须创建一个MySQL InnoDB表,使用严格顺序ID到表(行)中的每个元素。在ID中不能有任何间隙 - 每个元素必须具有不同的ID,并且它们被顺序地分配。并发用户在此表上创建数据。
I have to create a MySQL InnoDB table using a strictly sequential ID to each element in the table (row). There cannot be any gap in the IDs - each element has to have a different ID and they HAVE TO be sequentially assigned. Concurrent users create data on this table.
我经历了MySQL的自动增量行为,如果一个事务失败,PK号码不使用,留下一个空白。我读了在线复杂的解决方案,没有说服我和其他一些没有真正解决我的问题(在MySQL / InnoDB中模拟自动递增,设置手动同步mysql服务器上的增量值)
I have experienced MySQL "auto-increment" behaviour where if a transaction fails, the PK number is not used, leaving a gap. I have read online complicated solutions that did not convince me and some other that dont really address my problem (Emulate auto-increment in MySQL/InnoDB, Setting manual increment value on synchronized mysql servers)
- 我想最大限度地写并发。
- 我可能需要分割表格,但仍保留ID计数。
- 表中元素的顺序并不重要,但是ID必须是连续的(即,如果一个元素在另一个元素之前创建时不需要具有较低的ID,但不允许ID之间存在差距)。
我可以想到的唯一解决方案是使用额外的COUNTER表来保存计数。然后在表中用空的ID(不是PK)创建元素,然后锁定COUNTER表,获取数字,将其写入元素,增加数字,解锁表。我认为这将工作正常,但有一个明显的瓶颈:在锁定的时间没有人能够写任何ID。
另外,如果保存表的节点不可用,是单点故障。我可以创建一个主 - 主?复制,但我不知道如果这样我冒险使用过期的ID计数器(我从来没有使用复制)。
The only solution I can think of is to use an additional COUNTER table to keep the count. Then create the element in the table with an empty "ID" (not PK) and then lock the COUNTER table, get the number, write it on the element, increase the number, unlock the table. I think this will work fine but has an obvious bottle neck: during the time of locking nobody is able to write any ID. Also, is a single point of failure if the node holding the table is not available. I could create a "master-master"? replication but I am not sure if this way I take the risk of using an out-of-date ID counter (I have never used replication).
谢谢。 p>
Thanks.
很抱歉这样说,但允许高并发性同时实现高性能和要求严格单调序列是相互矛盾的要求。
I am sorry to say this, but allowing high concurrency to achieve high performance and at the same time asking for a strictly monotone sequence are conflicting requirements.
您有一个单点控制/失败,发出ID,并确保既没有重复也没有跳过,或,您必须接受这些情况之一或两者的机会。
Either you have a single point of control/failure that issues the IDs and makes sure there are neither duplicates nor is one skipped, or you will have to accept the chance of one or both of these situations.
正如您所说,尝试规避这种问题,但最终你总是会发现,你需要在速度和正确性之间进行权衡,因为只要你允许并发,你就可以遇到裂脑情况或竞态条件。
As you have stated, there are attempts to circumvent this kind of problem, but in the end you will always find that you need to make a tradeoff between speed and correctness, because as soon as you allow concurrency you can run into split-brain situations or race-conditions.
也许一个严格的单调序列对于每个可能的许多服务器/数据库/表都是确定的?
Maybe a strictly monotone sequence would be ok for each of possibly many servers/databases/tables?