Eu gostaria de entender melhor um mecanismo de bloqueio em postgres.

Vamos dizer que a árvore pode ter maçãs (via chave estrangeira na mesa da maçã). Parece que ao selecionar uma árvore para o bloqueio de atualização é obtido em uma maçã. No entanto, a operação não é bloqueada, mesmo que outra pessoa já tenha um bloqueio nessa maçã.

Por que é tão?

p.s. Por favor, não sugira remover "selecione para atualização".

Cenário

Transaction 1      Transaction 2
BEGIN              .
update apple;      .
.                  BEGIN
.                  select tree for update;
.                  update apple;
.                  --halts because of the other transaction locking an apple
update apple;      .
-- deadlock        .
                   COMMIT
                   --transaction succeeds

Código

Se você quiser experimentá-lo no seu postgres - aqui está um código que você pode copiar / colar.

Eu tenho um esquema db a seguir

CREATE TABLE trees (
    id       integer primary key
);

create table apples (
    id       integer primary key,
    tree_id  integer references trees(id)
);

e dados muito simples

insert into trees values(1);
insert into apples values(1,1);

Existem duas transações simples. Uma é atualizar maçãs, a segunda é travar uma árvore e atualizar uma maçã.

BEGIN;
    UPDATE apples SET id = id WHERE id = 1;
    -- run second transaction in paralell
    UPDATE apples SET id = id WHERE id = 1;
COMMIT;

BEGIN;
    SELECT id FROM trees WHERE id = 1 FOR UPDATE;
    UPDATE apples SET id = id WHERE id = 1;
COMMIT;

Quando eu os executo - o deadlock ocorre na segunda atualização da primeira transação.

ERROR:  deadlock detected
DETAIL:  Process 81122 waits for ShareLock on transaction 227154; blocked by process 81100.
Process 81100 waits for ShareLock on transaction 227153; blocked by process 81122.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."trees" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"