我不是 SQL 方面的专家,所以我需要社区的帮助。
我有以下简化表格。
CREATE TABLE "Keyspace" (
"id" SERIAL NOT NULL,
"tenantId" INTEGER NOT NULL,
"name" TEXT NOT NULL,
CONSTRAINT "Keyspace_pkey" PRIMARY KEY ("id")
);
CREATE TABLE "Project" (
"id" SERIAL NOT NULL,
"tenantId" INTEGER NOT NULL,
"name" TEXT NOT NULL,
"keyspaceId" INTEGER NOT NULL,
CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
);
CREATE TABLE "Entry" (
"id" SERIAL NOT NULL,
"tenantId" INTEGER NOT NULL,
"projectId" INTEGER NOT NULL,
CONSTRAINT "Entry_pkey" PRIMARY KEY ("id")
);
CREATE TABLE "KeyspaceEntry" (
"keyspaceId" INTEGER NOT NULL,
"entryId" INTEGER NOT NULL,
"number" INTEGER NOT NULL
);
一Entry
只能是一Keyspace
。这是通过Entry
-> Project
->Keyspace
引用强制执行的。
一个Keyspace
会有很多Entries
。
重要的字段是KeyspaceEntry.number
. 这是同一密钥空间中每个条目的自动递增编号。
例子:
- “Keyspace #1”有 3 个条目,其中包含数字
1
,2
,3
- “Keyspace #2”有 2 个带数字的条目
1
,2
- 当您在“Project X”中创建引用“Keyspace #1”的新条目时,应使用
number=4
.
我需要number
在同一事务中实现 Entry 和 KeyspaceEntry 的原子插入并递增。
注意:可能看起来没有必要拥有 KeyspaceEntry 表,因为您可以简单地将number
属性添加到 Entry,但这是一个简化的模型。当条目移动到不同键空间下的不同项目时,我希望拥有所有键空间-条目关系的历史记录。
另一种选择是创建历史表但保留实际Entry.number
属性。它可能更适合查询条目(它将用于通过 KeyspaceID+EntryNumber 选择单个条目),我稍后也可能会考虑这一点。如果您对此设计变更有意见,请分享。
我正在使用 PostgreSQL 16
测试表:
在两个不同的终端中,同时:
结果:
SELECT FOR UPDATE 将在事务长度内锁定父表中的行,这会强制其他 SELECT FOR UPDATE 等待。当第一个事务(插入编号1)提交时,第二个事务获得锁,它可以看到刚刚插入的编号=1的行,并插入编号=2的行。
在这些类型的查询中,您必须锁定父表(keyspace)中的行,而不是子表(keyspaceEntry)中的行。主要原因是在插入之前需要先获得递增的数字,并且需要锁定来递增数字,因此顺序必须是:锁定、递增、插入。您无法锁定尚未插入的行,并且插入的行在提交之前对并发事务不可见。
锁定父表中的行是明确的,因为您知道外键存在并且是唯一的。该行对于尝试使用相同的parent_id (keyspaceid) 插入的所有并发事务也是可见的。
(parent_id,number) 上的索引将加速 max() 并避免排序,如果您想使用 ORDER BY Parent_id 进行选择,我认为这是该操作的目标之一。
事务应该保持简短,因为持有父表的锁可以防止并发插入子表。
为了确保始终生成 id,这应该进入子级的 BEFORE INSERT 触发器中。
我在论坛上用过类似的东西。分页使用LIMIT和OFFSET,速度很慢。我将其替换为“主题中的帖子编号”列,允许直接找到所需的帖子。
删除行会在序列中造成漏洞。
在删除最高数字后插入新行将重用该数字。对于主键来说,这是不可取的,而且序列没有这种行为是件好事。如果您不想重复使用数字,则必须在父表中放置一个计数器,并在每次插入子表时递增它。例子:
有了这个,就不需要 SELECT FOR UPDATE,因为父表上的 UPDATE 获取了锁。因此,如果您只想在子表中插入一行,则无需显式开始事务,它会在一个查询中完成所有操作。