sql - PostgreSQL 并发事务问题

Question

我目前正在构建一个爬虫。多个爬行 worker 访问同一个 PostgreSQL 数据库。遗憾的是，我在此处介绍的主要交易中遇到了问题:

BEGIN ISOLATION LEVEL SERIALIZABLE;
    UPDATE webpages
    SET locked = TRUE
    WHERE url IN 
        (
            SELECT DISTINCT ON (source) url
            FROM webpages
            WHERE
                (
                    last IS NULL
                    OR
                    last < refreshFrequency
                )
                AND
                locked = FALSE
            LIMIT limit
        )
    RETURNING *;
COMMIT;

• url 是一个 URL(字符串)
• source为域名(String)
• last 是最后一次抓取页面的时间(日期)
• locked 是一个 bool 值，设置为表示当前正在抓取网页( bool 值)

我尝试了两种不同的事务隔离级别:

• ISOLATION LEVEL SERIALIZABLE，我收到类似could not serialize access due to concurrent update
这样的错误
• ISOLATION LEVEL READ COMMITTED，我从并发事务中得到重复的 url，因为数据从事务首次提交时就被“卡住”了(我认为)

总的来说，我对 PostgreSQL 和 SQL 还很陌生，所以我真的不确定我能做些什么来解决这个问题。

更新:
PostgreSQL 版本为 9.2.x.
网页表定义:

CREATE TABLE webpages (
  last timestamp with time zone,
  locked boolean DEFAULT false,
  url text NOT NULL,
  source character varying(255) PRIMARY KEY
);

Answer 1

澄清

这个问题留有解释的余地​​。这是我对任务的理解:

锁定最多 limit 个满足某些条件但尚未锁定的 URL。为了分散源上的负载，每个 URL 都应该来自不同的源。

数据库设计

假设有一个单独的表 source:这会使工作更快更容易。如果您没有这样的表，请创建它，无论如何它都是正确的设计:

CREATE TABLE source (
  source_id serial NOT NULL PRIMARY KEY
, source    text NOT NULL
);

CREATE TABLE webpage (
  source_id int NOT NULL REFERENCES source
  url       text NOT NULL PRIMARY KEY
  locked    boolean NOT NULL DEFAULT false,        -- may not be needed
  last      timestamp NOT NULL DEFAULT '-infinity' -- makes query simpler
);

或者，您可以有效地使用递归 CTE:

• Optimize GROUP BY query to retrieve latest record per user

带建议锁的基本解决方案

我正在使用 advisory locks即使在默认的 read committed 隔离级别下也能使其安全且便宜:

UPDATE webpage w
SET    locked = TRUE
FROM  (
   SELECT (SELECT url
           FROM   webpage
           WHERE  source_id = s.source_id
           AND   (last >= refreshFrequency) IS NOT TRUE
           AND    locked = FALSE
           AND    pg_try_advisory_xact_lock(url)  -- only true is free
           LIMIT  1     -- get 1 URL per source
          ) AS url
   FROM  (
      SELECT source_id  -- the FK column in webpage
      FROM   source
      ORDER  BY random()
      LIMIT  limit      --  random selection of "limit" sources
      ) s
   FOR    UPDATE
   ) l
WHERE  w.url = l.url
RETURNING *;

或者，您可以使用仅 建议锁，而根本不使用表列locked。基本上只是运行 SELECT 语句。锁一直保持到事务结束。您可以使用 pg_try_advisory_lock() 来保持锁定直到 session 结束。仅UPDATE一次在完成时设置last(并可能释放咨询锁)。

其他要点

• 在 Postgres 9.3 或更高版本中，您将使用 LATERAL 联接而不是相关子查询。

• 我选择 pg_try_advisory_xact_lock() 因为锁可以(并且应该)在事务结束时释放。建议锁详解:

  • Postgres UPDATE ... LIMIT 1

• 如果某些来源没有更多 URL 可供抓取，您将获得少于limit 行。

• 随机选择来源是我的大胆但有根据的猜测，因为没有可用的信息。如果您的 source 表很大，有更快的方法:

  • Best way to select random rows PostgreSQL

• refreshFrequency 实际上应该称为 latest_last，因为它不是“频率”，而是 时间戳 或 日期。

递归选择

要获得完整的限制行数如果可用，请使用RECURSIVE CTE 并迭代所有源，直到找到足够或没有更多可以找到。

正如我上面提到的，您可能根本不需要 locked 列并且仅使用建议锁(更便宜)进行操作。只需在交易结束时设置 last，然后再开始下一轮。

WITH RECURSIVE s AS (
   SELECT source_id, row_number() OVER (ORDER BY random()) AS rn
   FROM source  -- you might exclude "empty" sources early ...
   )
, page(source_id, rn, ct, url) AS (
   SELECT 0, 0, 0, ''::text   -- dummy init row
   UNION ALL
   SELECT s.source_id, s.rn
        , CASE WHEN t.url <> ''
               THEN p.ct + 1
               ELSE p.ct END  -- only inc. if url found last round
        , (SELECT url
           FROM   webpage
           WHERE  source_id = t.source_id
           AND   (last >= refreshFrequency) IS NOT TRUE
           AND    locked = FALSE  -- may not be needed
           AND    pg_try_advisory_xact_lock(url)  -- only true is free
           LIMIT  1           -- get 1 URL per source
          ) AS url            -- try, may come up empty
   FROM   page p
   JOIN   s ON s.rn = p.rn + 1
   WHERE  CASE WHEN p.url <> ''
               THEN p.ct + 1
               ELSE p.ct END < limit  -- your limit here
   )
SELECT url
FROM   page
WHERE  url <> '';             -- exclude '' and NULL

或者，如果您也需要管理 locked，请将此查询与上述 UPDATE 一起使用。

进一步阅读

在即将到来的 Postgres 9.5 中，您会喜欢SKIP LOCKED:

• Lock for SELECT so another process doesn't get old data

相关:

• Atomic UPDATE .. SELECT in Postgres