How is this Postgres function blocked?

Question

Our postgres database reports many deadlocks in tuples with respect to. There are only two functions that use a relation, and usually only one of the functions is involved in dead ends.

The function that most often causes locks has two requests:

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 and UPDATE queries query tuples in the same order using the queries below.

From what I understand, if tuples are always available in album and photo order, then a dead end should not be possible.

The function is called many times per second, and I expected blocking to happen, but at a loss to explain the deadlocks.

Any help is appreciated.

Queries in the function '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;

---

Add more to this question:

, '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)

Answer 1

, ( ), , .

, :

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 . , 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. , , , .