PostgreSQL：防止锁定左连接的自表更新

Question

我在使用PostgreSQL 9.3。我是唯一一个处理数据库的人，我的代码按顺序运行单元测试的查询。

大多数情况下，以下更新查询运行正常，但有时会锁定PostgreSQL server。然后，查询似乎永远不会结束，而通常只需要3秒钟。我必须确定查询在单元测试上下文中运行，即数据完全相同，而锁是否发生。代码是唯一更新数据的进程。

我知道在对自更新表使用更新查询时，PostgreSQL可能存在锁问题。当使用左连接时，大多数情况都会发生。

我也知道LEFT JOIN查询可以替换为UPDATE的NOTEXISTS查询，但在我的例子中，LEFT JOIN要快得多，因为要更新的数据很少，而NOTEXISTS应该访问相当多的行候选。

所以我的问题是：我应该使用什么样的PostgreSQL命令（比如表上的显式锁定LOCK）或选项（比如SELECT FOR UPDATE），以确保运行查询时不会出现永无止境的锁定。

查询：

-- for each places of scenario #1 update all owners that
-- are different from scenario #0
UPDATE t_territories AS upt
SET id_owner = diff.id_owner
FROM (
    -- list of owners in the source that are different from target
    SELECT trg.id_place, src.id_owner
    FROM t_territories AS trg
    LEFT JOIN t_territories AS src
       ON  (src.id_scenario = 0)
       AND (src.id_place = trg.id_place)
    WHERE (trg.id_scenario = 1)
    AND (trg.id_owner IS DISTINCT FROM src.id_owner)
    -- FOR UPDATE -- bug SQL : FOR UPDATE cannot be applied to the nullable side of an outer join
) AS diff
WHERE (upt.id_scenario = 1)
AND (upt.id_place = diff.id_place)

表结构：

CREATE TABLE t_territories
(
  id_scenario integer NOT NULL,
  id_place integer NOT NULL,
  id_owner integer,
  CONSTRAINT t_territories_pk PRIMARY KEY (id_scenario, id_place),
  CONSTRAINT t_territories_fkey_owner FOREIGN KEY (id_owner)
      REFERENCES t_owner (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE RESTRICT
)

Answer 1

我认为您的查询已被另一个查询锁定。您可以通过以下方式找到此查询：

SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()

我在这里找到了这个问题http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

还可以使用访问独占锁来防止任何读取和写入表的查询t\u

LOCK t_territories IN ACCESS EXCLUSIVE MODE;

有关锁的更多信息，请点击此处https://www.postgresql.org/docs/9.1/static/explicit-locking.html