sql - 此 INSERT 是否可能导致任何锁定/并发问题？

Question

为了在这个特定的数据库中出于某种原因避免自动序列号等，我想知道是否有人能看到这方面的任何问题:

INSERT INTO user (label, username, password, user_id)
SELECT 'Test', 'test', 'test', COALESCE(MAX(user_id)+1, 1) FROM user;

我正在使用 PostgreSQL(但也试图尽可能地与数据库无关)..

编辑:我想这样做有两个原因。

• 保持对任何特定 RDBMS 的低依赖性。
• 如果数据批量更新到中央数据库，则不必担心更新序列。

插入性能不是问题，因为唯一需要它的表是设置表。

EDIT-2:我的想法是数据库中的每个表都有一个人工生成的 SiteCode 作为其键的一部分，所以我们总是有一个复合键。这有效地划分了 SiteCode 上的数据，并允许从特定站点获取数据并将其放在其他地方(显然是在相同的数据库结构上)。例如，这将允许将各种操作站点备份到一个中央数据库，但也允许该中央数据库有使用它的操作站点。我仍然可以使用序列，但它看起来很乱。实际的 INSERT 看起来更像这样:

INSERT INTO user (sitecode, label, username, password, user_id)
SELECT 'SITE001', 'Test', 'test', 'test', COALESCE(MAX(user_id)+1, 1)
FROM user
WHERE sitecode='SITE001';

如果这有道理..我以前做过类似的事情并且工作正常，但是在那种情况下中央数据库从未运行(它更像是一种集中查看数据/分析的方式)所以它不需要生成ID。

EDIT-3:我开始认为只允许集中式数据库处于仅事件状态或仅备份状态会更简单，从而完全避免该问题并允许更简单的设计。

哦，回到绘图板!

Answer 1

有几点:

• Postgres 使用多版本并发控制 (MVCC)，因此读者永远不会等待编写者，反之亦然。但是当然在每次写入时都会发生序列化。如果您要将大量数据加载到系统中，请查看 COPY 命令。它比运行大量 INSERT 语句要快得多。
• MAX(user_id) 可以用索引来回答，如果 user_id 列上有索引，则可能是这样。但真正的问题是，如果两个事务同时开始，它们将看到相同的 MAX(user_id) 值。它引导我到下一点:
• 处理像 user_id 这样的数字的规范方法是使用 SEQUENCE。这些基本上是您可以从中提取下一个用户 ID 的地方。如果您真的担心生成下一个序列号的性能，您可以为每个线程生成一批，然后仅在用完时请求一个新批处理(有时称为 HiLo 序列)。
• 您可能希望随着数字的增加将 user_id 收拾得井井有条，但我认为您应该尝试摆脱它。原因是删除 user_id 无论如何都会造成一个漏洞。所以如果序列不是严格递增的，我也不会太担心。