postgresql - 这个Postgres函数怎么会死锁？

我们的postgres数据库报告了一个关系中元组的大量死锁。
只有两个函数使用关系，并且通常只有一个函数涉及死锁。
最常导致死锁的函数有两个查询：

1. The first query
        looks for ONE photo
        and ROW LOCKS ALL the photo rows
        for ALL albums the the photo is found in

    For example given the below table of data:
           if the query was looking for Photo 2
           then it would LOCK ALL 6 rows of Album A and C.

            album   photo   version
            A       1       1.0     lock
            A       2       1.0     lock    update
            A       3       1.0     lock
            B       8       2.0
            B       9       2.0
            C       1       1.1     lock
            C       2       1.1     lock    update
            C       5       1.1     lock
            D       7       4.0
            D       8       4.0

2. The second query then updates the 2 tuples for Photo 2.

FOR UPDATE和UPDATE查询使用以下查询按相同顺序访问元组。
据我所知，如果元组总是以相册和照片顺序访问，那么就不可能出现死锁。
这个函数每秒被调用多次，我确实预料到阻塞会发生，但无法解释死锁。
如有任何帮助，我们将不胜感激。
函数“album_version_set”中的查询

    PERFORM 1
    FROM work.album a
    WHERE EXISTS (
        SELECT
            x.album
        FROM work.album x
        WHERE
            x.photo = 2
            AND x.album = a.album)
    ORDER BY
        a.album,
        a.photo
    FOR UPDATE;


    WITH cte_update_version (album) AS (
        UPDATE work.album a
        SET
            version = version + .1
        FROM (
            SELECT
                x.album,
                x.photo
            FROM work.album x
            WHERE
                x.photo = 2
            ORDER BY
                x.album
                x.photo
            ) ord
        WHERE
            a.album = ord.album
            AND a.photo = ord.photo
        RETURNING
            a.album)
    INSERT INTO tmp_album_keys(
        album)
    SELECT DISTINCT
        us.album
    FROM
        cte_update_version;

在这个问题上添加更多内容：
从错误日志中，我可以看出函数'album_version_set'与自身冲突并导致死锁。
下面是日志中的条目。日志似乎只显示死锁中涉及的1个进程的语句。由于这个函数有两个查询，我不确定进程31019中的哪个查询是死锁的一部分。
以下是日志中的一个条目：

2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:ERROR:  deadlock detected
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:DETAIL:  Process 31024 waits for ShareLock on transaction 8334317; blocked by process 31019.
    Process 31019 waits for ShareLock on transaction 8334322; blocked by process 31024.
    Process 31024: SELECT * FROM album_version_set($1, $2)
    Process 31019: SELECT * FROM album_version_set($1, $2)
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:HINT:  See server log for query details.
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:CONTEXT:  while locking tuple (11,83) in relation "album"
    SQL statement "SELECT 1
                    FROM work.album a
                    WHERE EXISTS (
                        SELECT
                            x.album
                        FROM work.album x
                        WHERE
                            x.photo = 2
                            AND x.album = a.album)
                    ORDER BY
                        a.album,
                        a.photo
                    FOR UPDATE;"
    PL/pgSQL function album_version_set(character varying,smallint) line 69 at PERFORM
2018-03-06 15:35:20 UTC:10.1.2.1(43636):z1login@atier:[31024]:STATEMENT:  SELECT * FROM album_version_set($1, $2)

最佳答案

看起来至少有一个竞争条件有可能导致死锁（无论如何，在默认情况下transaction isolation level），不过我不能肯定这会导致你的死锁。
假设您的桌子最初看起来是这样的：

album   photo   version
B       2       1.0
C       2       1.0

第一个查询运行，并开始锁定行。
同时，其他人运行INSERT INTO work.album VALUES ('A', 2, 1.0)。
这一新行被FOR UPDATE查询忽略（因为它的数据库快照在语句开头是固定的），但它仍然被随后的UPDATE拾取，并在处理过程中被锁定。
总的来说，事务中的锁定顺序（就album值而言）是'B'，'C'，'A'；您现在面临死锁的风险。
更糟糕的是，如果并发插入包含多行，那么您已经用photo = 2更新了记录，而没有锁定唱片集的其余部分。例如，如果concurrent语句是INSERT INTO work.album VALUES ('A', 2, 1.0), ('A', 3, 1.0)，则您将处于以下状态：

album   photo   version
A       2       1.0             update
A       3       1.0
B       2       1.0     lock    update
C       2       1.0     lock    update

通常，在WHERE查询和FOR UPDATE语句中重复相同的UPDATE条件会使您易受此类死锁的影响。避免此问题的一般模式是让锁定查询返回一些不含糊的行标识符（如果有，则返回生成的主键，如果没有，则返回ctid*），以明确已锁定的行标识符，然后将这些标识符传递给UPDATE语句，以确保它只针对已锁定的元组，例如：

DECLARE
  locked_tuples tid[];
BEGIN
  locked_tuples := ARRAY(
    SELECT ctid
    FROM work.album
    WHERE album IN (
      SELECT x.album
      FROM work.album x
      WHERE x.photo = 2
    )
    ORDER BY album, photo
    FOR UPDATE
  );

  WITH cte_update_version (album) AS (
    UPDATE work.album
    SET version = version + .1
    WHERE
      ctid = ANY(locked_tuples) AND
      photo = 2
    RETURNING album
  )
  INSERT INTO tmp_album_keys(album)
  SELECT DISTINCT album
  FROM cte_update_status;
END

这将消除死锁的可能性，尽管这也意味着并发插入的行将不再被更新（这可能是您希望的，也可能不是您希望的）。
*注意ctid值。不能将它们视为通用行标识符，因为它们可以通过各种内部操作进行更改，但只要在行上持有一个锁，它们就应该是稳定的。

关于postgresql - 这个Postgres函数怎么会死锁？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49121102/