Início / dba / Perguntas / 340542
Accepted
Adrian B.
Asked: 2024-06-27 05:05:25 +0800 CST

Bloqueio para atualização dentro dos CTEs

  • 772

Se eu fizer uma consulta usando CTEs parecida com esta:

WITH cte_a AS (
    SELECT a.id, a.something
    FROM a
    WHERE a.something IS NOT NULL 
),
-- [...] some other CTEs
cte_d AS (
    SELECT 
        cte_a.id,
        cte_a.something
    FROM cte_a
    JOIN 
        -- something
    JOIN
        -- something
    WHERE 
        -- something
    ORDER BY cte_a.id ASC
    FOR UPDATE  -- Here, will the `FOR UPDATE` locks `a` rows?
),
-- rest of the query, which will update `a.something`.

O bloqueio para atualização será aplicado nas linhas da tabela aou o bloqueio será aplicado em uma tabela materializada gerada por cte_a?

Se o bloqueio se aplicar à tabela materializada, o seguinte resolveria o problema?

WITH cte_a AS NOT MATERIALIZED (
-- Rest of the query

Estou usando o PostgreSQL v14.

Se isso mudar alguma coisa, estou mais interessado no comportamento com o nível de isolamento READ COMMITTED padrão.

postgresql

1 respostas

  1. Best Answer

    A base da minha resposta é fornecida no manual:

    Se tabelas específicas forem nomeadas em uma cláusula de bloqueio, somente as linhas provenientes dessas tabelas serão bloqueadas; quaisquer outras tabelas usadas SELECTsão simplesmente lidas normalmente. Uma cláusula de bloqueio sem uma lista de tabelas afeta todas as tabelas usadas na instrução. [...]
    No entanto, essas cláusulas não se aplicam a WITHconsultas referenciadas pela consulta primária. Se desejar que o bloqueio de linha ocorra em uma WITHconsulta, especifique uma cláusula de bloqueio na WITHconsulta.

    Então:

    • Para bloquear apenas linhas de tabelas específicas na FROMcláusula, liste seus nomes na cláusula de bloqueio. Caso contrário, todas as linhas qualificadas de todas as tabelas da FROMlista serão bloqueadas.

      • Mas você só pode listar nomes de tabelas reais, não nomes de CTE, portanto, para começar, não é possível direcionar CTEs (o que não faria sentido de qualquer maneira, porque a cláusula de bloqueio não se propaga para CTEs, conforme discutido abaixo).

    • Sem restrição de tabela na cláusula de bloqueio, todas as linhas selecionadas de todas as tabelas na FROMcláusula serão bloqueadas.

    • Uma cláusula de bloqueio na consulta primária não é propagada para CTEs.

    Isso ainda não esclarece completamente o seu caso complexo :

    1. SELECTda tabela aem cte_a.
    2. Então você cte_dtem cte_aem sua FROMcláusula, além de algumas outras tabelas, além de mais filtros.
      • Lá você adiciona a FOR UPDATEcláusula de bloqueio sem nomear tabelas específicas.
    3. Finalmente, UPDATE ana consulta primária.

    Por experiência e alguns testes superficiais rápidos adicionais:

    • A cláusula de bloqueio não se aplica a WITHconsultas na FROMlista em nenhum caso. Não apenas na consulta primária, mas também em outros CTEs.

    • Além do mais, adicionar uma cláusula de bloqueio evita o "inlining" do mesmo CTE (otimização automática ou explicitamente exigida no NOT MATERIALIZEDPostgres 12+). Portanto, todas as linhas qualificadas neste CTE específico são bloqueadas, filtros adicionais em outros CTEs ("posteriores") ou na consulta primária não restringem o conjunto.

    Conclusão

    Para bloquear linhas de table a, você deve adicionar a cláusula de bloqueio em uma consulta onde essa tabela esteja listada na FROMcláusula. Sua consulta poderia ser assim:

    WITH cte_a AS (
   SELECT a.id, a.something
   FROM   a
   WHERE  a.something IS NOT NULL 
   ORDER  BY a.id  -- possibly useful to lock rows in particular order (?)
   FOR    UPDATE   -- HERE !!!
   )
--   ... other CTEs
, cte_d AS (
   SELECT a.id, a.something
   FROM   cte_a a
   JOIN   some_table
-- WHERE  something
-- ORDER BY a.id  -- still needed (?)
-- FOR UPDATE     -- still needed for other tables (?)
   )
UPDATE a
SET    something = null
FROM   cte_d d
WHERE  a.id = d.id;

    Porém , isso pode bloquear mais linhas do que o necessário. Tente aplicar todos os filtros antes da cláusula de bloqueio. (Nas mesmas SELECTcontagens.)
    Eu provavelmente reescreveria toda a consulta.

    Como testar?

    Configuração mínima:

    -- drop table a;
CREATE TABLE a (id int PRIMARY KEY, something int);
INSERT INTO a VALUES
  (1, 1)
, (2, NULL)
, (3, 3) -- not in table b !
;

-- drop table b;
CREATE TABLE b (id int PRIMARY KEY, something int);
INSERT INTO b VALUES
  (1, 1)
, (2, NULL)
;

    Em seguida, inicie uma transação em uma sessão e deixe-a aberta:

    BEGIN;

-- EXPLAIN  -- also interesting!
WITH cte_a AS (  -- row 1 & 3
-- WITH cte_a AS NOT MATERIALIZED (  -- ?
   SELECT *
   FROM   a
   WHERE  something IS NOT NULL
-- FOR    UPDATE  -- Here?
   )
-- more CTEs
, cte_d AS (  -- only row 1
   SELECT a.id, a.something
   FROM   cte_a a
   JOIN   b USING (id)  -- eliminates row 3
-- ORDER  BY a.id
-- FOR    UPDATE  -- Here?
   )
TABLE cte_d;

    Em seguida, tente em outra sessão (conexão separada/janela de consulta separada):

    BEGIN;
-- UPDATE b SET something = 0 WHERE id = 1;
UPDATE a SET something = 0 WHERE id = 1;  -- 3?

    Isso ocorrerá ou será interrompido por um bloqueio de linha (nesse caso, você aborta).

    Em cada sessão, execute ROLLBACK;antes do próximo teste.

    Varie para ver os efeitos de cada constelação.

    • 5

relate perguntas