mysql - 为什么 MySQL InnoDB 可以处理并发更新而 PostgreSQL 不能？

Question

假设您有一个定义如下的表:

CREATE TABLE public.positions
(
   id serial,
   latitude numeric(18,12),
   longitude numeric(18,12),
   updated_at timestamp without time zone
)

并且您在该表中有 50,000 行。现在出于测试目的，您将运行这样的更新:

update positions
set  updated_at = now()
where latitude between 234.12 and 235.00;

该语句将从 50,000 行(在此特定数据集中)更新 1,000 行

如果您在 30 个不同的线程中运行这样的查询，MySQL innodb 将成功，而 postgres 将因大量死锁而失败。

为什么？

Answer 1

普普通通的老运气，我会说。

如果 30 个线程继续并想要更新相同的 1000 行，它们可以以相同的顺序访问这些行(在这种情况下它们将相互锁定并按顺序执行)或以不同的顺序(在这种情况下他们会陷入僵局)。

InnoDB 和 PostgreSQL 也是如此。

要分析在您的案例中结果为何不同，您应该从比较执行计划开始。也许你会明白为什么 PostgreSQL 不以相同的顺序访问所有行。

缺少此信息，我猜您遇到的是 feature introduced in version 8.3加速并发顺序扫描:

• Concurrent large sequential scans can now share disk reads (Jeff Davis)

  This is accomplished by starting the new sequential scan in the middle of the table (where another sequential scan is already in-progress) and wrapping around to the beginning to finish. This can affect the order of returned rows in a query that does not specify ORDER BY. The synchronize_seqscans configuration parameter can be used to disable this if necessary.

检查您的 PostgreSQL 执行计划是否使用顺序扫描并查看更改 synchronize_seqscans 是否可以避免死锁。