INSERTs simultâneos que bloqueiam a tabela de destino dentro de um gatilho antes da inserção resultam em um impasse

Estou trabalhando em um aplicativo da web que usa um banco de dados PostgreSQL para armazenamento. Existem muitas entidades lógicas neste aplicativo que estão relacionadas entre si hotel --> hotel_floor --> hotel_room(o exemplo de hotéis usado ao longo da pergunta é puramente hipotético). Cada tabela armazena os IDs de todos os seus pais na hierarquia junto com seu próprio ID que é único no escopo de seus pais . A estrutura do banco de dados é mais ou menos assim:

table hotel (
    hotel_id int,
    ...

    CONSTRAINT hotel_pk PRIMARY KEY (hotel_id)
)


table floor (
    hotel_id int,
    floor_id int,
    ...

    CONSTRAINT floor_pk PRIMARY KEY (hotel_id, floor_id),
    CONSTRAINT floor_hotel_fk FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id)
)


table room (
    hotel_id int,
    floor_id int,
    room_id int,
    ...

    CONSTRAINT room_pk PRIMARY KEY (hotel_id, floor_id, room_id),
    CONSTRAINT room_hotel_fk FOREIGN KEY (hotel_id) REFERENCES hotel (hotel_id),
    CONSTRAINT room_floor_fk FOREIGN KEY (hotel_id, floor_id) REFERENCES floor (hotel_id, floor_id),
)

E os registros armazenados lá têm IDs que se parecem com isso:

  hotel
--------
hotel_id
--------
   1
   2
   3


        floor
-------------------
hotel_id | floor_id
-------------------
    1    |    1
    1    |    2
         |
    2    |    1
    2    |    2


             room
-----------------------------
hotel_id | floor_id | room_id
-----------------------------
    1    |    1     |    1
    1    |    1     |    2
    1    |    1     |    3

    1    |    2     |    1
    1    |    2     |    2
    1    |    2     |    3
    1    |    2     |    4

Obviamente, por causa dessa estrutura de banco de dados (que foi herdada por nossa equipe e não é fácil de refatorar), todos os IDs precisam ser gerados manualmente. Por esse motivo, temos um gatilho de inserção anterior em cada tabela, que se resume a algo semelhante a isto:

CREATE OR REPLACE FUNCTION room_set_new_id() RETURNS trigger LANGUAGE plpgsql AS $
BEGIN
    LOCK TABLE room IN SHARE ROW EXCLUSIVE MODE;

    NEW.room_id = (
        SELECT COALESCE(MAX(room_id), 0) + 1
        FROM room
        WHERE hotel_id = NEW.hotel_id AND floor_id = NEW.floor_id
    );

    RETURN NEW;
END
$;

CREATE TRIGGER room_set_new_id
BEFORE INSERT ON room
FOR EACH ROW EXECUTE PROCEDURE room_set_new_id();

O problema está na LOCK TABLEdeclaração. Até recentemente, não havia nenhum, mas começamos a receber erros de chave duplicada quando 2 operações de inserção eram executadas ao mesmo tempo, então decidi adicionar esses bloqueios na esperança de corrigir o problema. Infelizmente, isso levou a um novo problema: as inserções simultâneas agora resultam em um impasse. Eu experimentei com o código do servidor e descobri que o problema reside exclusivamente nos próprios 's, por exemplo , INSERTduas ou mais INSERT INTO room (hotel_id, floor_id) VALUES (1, 1)consultas emitidas ao mesmo tempo - não há outras operações de leitura/gravação acontecendo com o hotel//floorroomtabelas naquele momento (na verdade, cada solicitação ao servidor web vem de um usuário logado, e há uma conexão entre a tabela de usuários e a tabela de hotéis que é usada para fins de autenticação, mas não acho que um simultâneo "verifique se o usuário atual está relacionado ao hotel em questão" a consulta deve afetar a operação de inserção).

Eu tenho tentado descobrir como consertar isso, mas sem sucesso. É claro que posso adicionar tratamento de erro de bloqueio no servidor da Web, detectando o erro e tentando novamente a execução da consulta, mas prefiro fazer isso apenas se não houver realmente nenhuma maneira de resolver esse problema no nível do banco de dados, ou seja, se o bloqueio da minha tabela estratégia está errada e há uma maneira diferente de evitar a geração de IDs duplicados, gostaria de fazer isso. (Lembre-se, estou preso a essa estrutura de banco de dados e levará muito tempo e esforço para reescrever toda a lógica do banco de dados e do servidor da Web para usar colunas geradas automaticamente apropriadas).