PostgreSQL的单个查询执行不是原子的吗？

Question

PostgreSQL的单个查询执行不是原子的吗？

4

在我的postgresql数据库中，我有一个名为“my_table”的表，它的主键是(a, b)列。

我编写了以下查询语句，用于向此表插入数据，以确保永远不会违反主键约束。

INSERT INTO my_table(a, b, c, d, e, f)
  (SELECT 'a1',
          'b1',
          'c1',
          1442849052013,
          1,
          FALSE
   FROM
     (SELECT 'a1'::VARCHAR(100) AS a,
             'b1'::VARCHAR(50) AS b) AS new_fields
   LEFT OUTER JOIN my_table AS old_fields ON (new_fields.a = old_fields.a
                                              AND new_fields.b = old_fields.b)
   WHERE old_fields.a IS NULL);

由于多个线程同时运行，因此会执行多个类似的查询。有时候我会遇到PSQLException: duplicate key value violates unique constraint "my_table_pkey". Detail: Key (a, b)=(a1, b1) already exists.（PSQLException：重复键值违反唯一约束条件"my_table_pkey"。详细信息：键（a，b）=（a1，b1）已经存在）的错误。

我无法理解为什么这个查询会导致指定的错误情况，因为该查询仅在主键列缺失时才插入到“my_table”中，并且此条件在同一查询中进行检查。

如果这是有效的场景，这是否意味着PostgreSQL中的查询执行不是原子性的？

能否有人解释一下...

这里，Select For Update语法也不起作用，因为Select For Update会锁定所选行，而我想要插入一行（如果它不存在）。

- Vijay Kansal

据我所知，大多数关系型数据库管理系统中的查询执行并不是原子性的；这就是为什么需要事务存在的原因。 - Uueerdo

@ Uueerdo 多个查询的执行不是原子性的，但单个查询的执行必须是原子性的，对吧？ - Vijay Kansal

请格式化您的SQL以使其更易于阅读。 - Basil Bourque

1

“原子性”是指事务要么完成，要么不执行任何操作 - 不允许中间状态。这是事务隔离的一个问题。 - David Aldridge

1

这是因为该语句是原子的。它不会看到在它开始之后发生的任何事情，也不会看到在它开始之前没有提交的任何事情。查询在执行期间看到的是数据库在查询开始时的状态。这就是“原子”的概念。 - user330315

1

@a_horse_with_no_name 我认为这更像是事务隔离级别的描述——ACID中的"I"。当然，它们都有关联，但"A"实际上意味着如果你正在向一个表插入5行数据，另一个表插入3行数据，那么它们要么全部提交，要么全部不提交。 - David Aldridge

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- David Aldridge · Accepted Answer

在PostgreSQL中，默认的事务隔离级别为“读已提交”，不会看到任何未提交的数据。

因此，如果另一个未提交的事务插入了 ('a1'，'b1')，其他事务将无法看到它。

然而，存在约束条件将保证另一个事务尝试插入 ('a1'，'b1')时不能完成，直到第一个事务回滚（您的事务可以成功完成）或提交（您的事务将因约束违反而失败）。

详细说明请参见文档。