如何处理独特的“伪序”。在数据库中多个进程？

Question

我正在使用Sqlalchemy Orm和Oracle数据库使用Python 3.10。

数据定义无法更改（要求）。
我有一个具有独特约束和“伪序列”的字段，这意味着该领域是唯一的，它由以下形式组成：Year-2000 + sequence-number。
示例是220001、220002、220003等。它必须严格增加而没有空白。

目前，我们将这样的字段设置为这样（假设一年不会改变）：

WHILE True
   SELECT max(sequence)
   Add 1 to the current maximum from above
   INSERT new record with this updated value in DB. 
   IF Success or counter > max_tries: break
   ELSE counter = counter + 1

自然存在种族条件，因为我们的网络服务器运行了多个进程（不是线程）（也是要求）。

现在，我们尝试“乐观地”多次插入值，这意味着，如果另一个记录在同一时间同时再次尝试上述“算法”。如果将来我们获得更多的流量，那么这不会比许多重试相比，这并不是那么良好的重点，而DB负载则增加。

有其他方式而不是以中等费用的乐观锁定？

Answer 1

[tl; dr]只需使用普通序列，如果要生成伪序列，则可以计算出出于显示目的。

---

如果您有一个带有列的表：

CREATE TABLE table_name (
  id NUMBER
     GENERATED ALWAYS AS IDENTITY
     CONSTRAINT table_name__id__pk PRIMARY KEY,
  dt DATE
     CONSTRAINT table_name__dt__nn NOT NULL
);

注意：Identity列可从Oracle 12. 获得

，则您有一个增量序列（您可以用作主键，并且用于任何参考约束）和日期列。

您可以使用row_number（）分析函数和算术来生成伪序列（ display 目的，而不是作为参考约束

SELECT t.*,
       (EXTRACT(YEAR FROM dt) - 2000)*10000
       + ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM dt) ORDER BY id)
         AS pseudo_seq
FROM   table_name t

---

）伪序值，然后您可以使用：

ALTER TABLE table_name ADD ( pseudo_seq NUMBER(8,0) );

MERGE INTO table_name dst
USING (
  SELECT ROWID rid,
         (EXTRACT(YEAR FROM dt) - 2000)*10000
         + ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM dt) ORDER BY id)
           AS pseudo_seq
  FROM   table_name
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE
  SET   pseudo_seq = src.pseudo_seq
  WHERE pseudo_seq IS NULL;

但是，鉴于id列将依次增加，然后这是不必要的，并且pseudo_seq列只能动态生成。

db＆lt;＆gt;＆gt;