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

Question

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

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

我正在使用 PostgreSQL（但也试图尽可能与数据库无关）。

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

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

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

编辑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。

编辑3： 我开始认为只允许集中式数据库仅活动或仅备份会更简单，从而完全避免问题并允许更简单的设计。

哦，好吧，回到绘图板吧！

Answer 1

无论如何使用序列来生成唯一的数字。它们速度快，交易安全可靠。

“序列生成器”的任何自写实现要么对于多用户环境不可扩展（因为您需要进行大量锁定），要么根本不正确。

如果您确实需要独立于 DBMS，那么创建一个抽象层，该抽象层使用那些支持它们的 DBMS（Posgres、Oracle、Firebird、DB2、Ingres、Informix 等）的序列，并在那些不支持它们的 DBMS 上创建一个自编写的生成器。 t。

尝试创建一个不独立于 DBMS 的系统，仅仅意味着如果您不利用每个 DBMS 的优势，它在所有系统上的运行速度都会同样慢。

Answer 2

你的目标是一个好的目标。避免使用 IDENTITY 和 AUTOINCRMENT 列意味着避免大量的管理问题。这只是一个示例。

• 然而，SO 的大多数响应者不会欣赏它，流行的（而不是技术）响应是“始终在所有移动的内容上粘贴 Id AUTOINCREMENT 列”。

• 下一个序列号就可以了，所有供应商都已经对其进行了优化。

• 只要此代码位于事务内部（理应如此），两个用户就不会获得相同的 MAX()+1 值。有一个称为隔离级别的概念，在对事务进行编码时需要理解它。

• 远离 user_id 并使用更有意义的键，例如 ShortName 或 State 加上 UserNo 甚至是更好（前者分散了争用，后者完全避免了下一个顺序争用，与大容量系统相关）。

• MVCC 的承诺和它实际提供的东西是两件不同的事情。只需上网或搜索 SO 即可查看有关 PostcreSQL/MVCC 的数百个问题。在计算机领域，物理定律适用，没有什么是免费的。 MVCC 存储所触及的所有行的私有副本，并在事务结束时解决冲突，从而导致更多的回滚。而 2PL 在交易开始时阻塞并等待，无需大量存储副本。

  • 大多数具有 MVCC 实际经验的人不建议将其用于高争用、高容量的系统。

第一个示例代码块很好。

根据评论，此项不再适用：第二个示例代码块有问题。 “SITE001”不是复合键，它是复合列。不要这样做，将“SITE”和“001”分成两个离散的列。如果“SITE”是固定的重复值，则可以将其消除。

Answer 3

不同的用户可以有相同的 user_id，并发的 SELECT 语句将看到相同的 MAX(user_id)。

如果您不想使用 SEQUENCE，则必须使用带有单个记录的额外表，并在每次需要新的唯一 id 时更新该单个记录：

CREATE TABLE my_sequence(id INT);

BEGIN;
UPDATE my_sequence SET id = COALESCE(id, 0) + 1;
INSERT INTO 
  user (label, username, password, user_id)
SELECT 'Test', 'test', 'test', id FROM my_sequence;
COMMIT;

Answer 4

我同意 maksymko 的观点，但并不是因为我不喜欢序列或自动递增数字，因为它们有自己的位置。如果您需要一个在“各个操作站点”中唯一的值，即不仅在单个数据库实例的范围内，那么全局唯一标识符是一个强大、简单的解决方案。

Answer 5

有几点：

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

Answer 6

是的，我可以看到一个巨大问题。 不要这样做。

多个连接可以同时获取完全相同的 ID。我本来打算添加“负载下”，但它甚至不需要 - 只需要两个查询之间的正确时机即可。

为了避免这种情况，您可以使用特定于每个数据库的事务或锁定机制或隔离级别，但是一旦我们到达该阶段，您不妨使用特定于 dbms 的序列/身份/自动编号等。

编辑

对于question edit2，没有理由担心 user_id 中存在间隙，因此您在所有站点上都有一个序列。如果间隙可以，某些选项将

• 使用有保证的更新语句，例如（在 SQL Server 中）

update tblsitesequenceno set @nextnum = nextnum = nextnum + 1

此语句的多个调用者都保证获得唯一的编号。

• 使用生成标识/序列/自动编号（特定于数据库）的单个表

如果根本不能有间隙，请考虑使用在运行 max() 查询时限制访问的事务机制。或者使用与单个序列相同的技术使用动态 SQL 进行操作的增殖（具有标识列的序列/表/具有自动编号的表）。