Início / dba / Perguntas / 273163
Accepted
Theron Luhn
Asked: 2020-08-04 16:41:48 +0800 CST

Por que adicionar uma chave estrangeira auto-referencial congelou a tabela?

  • 772

Estou executando o PostgreSQL 11.5. Eu tenho uma tabela simples de registros.

create table register(
  id serial primary key,
  name text not null
);

Esta tabela é pequena (~6000 linhas), leitura relativamente pesada (100s consultas/s), quase sem gravação. Eu queria adicionar uma chave estrangeira auto-referencial:

alter table register
  add column leader_id integer references register(id);

Dado o tamanho da mesa, presumi que essa seria uma mudança bastante mundana. Ele funcionou bem localmente e no servidor de teste. No entanto, quando eu o executei na produção, a tabela travou. Os logs mostram ALTER e várias instruções SELECT levando ~ 10m para serem concluídas.

2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);

2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost

O que está acontecendo aqui que está causando o travamento da mesa? Como posso trabalhar com segurança com uma chave estrangeira auto-referenciada?

postgresql postgresql-11

1 respostas

  1. Best Answer

    A coisa mais rápida a tentar é set lock_timeout=1000;, então tente seu ALTER um monte de vezes. O mais provável é que você atinja o tempo limite a cada vez, mas pode ter sorte e isso o salvará de ter que caçar a correção real, que é, lamento dizer, não tão fácil.

    Quase certamente o que está acontecendo é que os outros processos mantêm suas transações abertas após realizarem seu SELECT no registro. Depois que o primeiro SELECT for executado, a transação terá um bloqueio ACCESS SHARE nessa tabela até que a transação seja confirmada ou revertida.

    Infelizmente, ADD COLUMN requer um bloqueio ACCESS EXCLUSIVE - independentemente de haver uma chave estrangeira, autorreferencial ou não, envolvida. E, de acordo com a tabela de modos de bloqueio conflitantes , o ACCESS EXCLUSIVE entra em conflito com o ACCESS SHARE.

    Você pode investigar quais bloqueios estão envolvidos especificamente com pg_locks, e é útil ingressar pg_stat_activitypara ver as consultas reais. O que se segue são 3 sessões psql, intercaladas, para que você possa ver o que está acontecendo.

    -- psql session 1: what the always-reading application code is doing
testdb=# BEGIN; SELECT * from register where id=10;
BEGIN
 id | name 
----+------
 10 | bar
(1 row)

testdb=# -- now going to psql session #2.


-- psql session 2: look at the locks from the SELECT's still-open transaction.

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |      mode       |                query                
---------------+-----------------+-------------------------------------
 register      | AccessShareLock | SELECT * from register where id=10;
 register_pkey | AccessShareLock | SELECT * from register where id=10;
(2 rows)

-- psql session #3: perform the ALTER
testdb=# begin; ALTER TABLE register ADD COLUMN leader_id integer;
BEGIN
-- is now hanging on the ALTER - back to session #2

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query                        
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
(3 rows)

    Como você pode ver, ALTER TABLE está tentando, e falhando (eu adiciono um asterisco quando pg_locks.granted é false) para obter um AccessExclusiveLock na tabela de registradores.

    Neste ponto, seu aplicativo começará a ter problemas. Vamos abrir um 4º psql e tentar outro SELECT:

    testdb=#  BEGIN; SELECT * from register where id=12;
BEGIN
--- aaaand... the SELECT is hanging now too! Let's go back to #2 and check the locks again

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query                        
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
 register      | AccessShareLock*     | SELECT * from register where id=12;
(4 rows)

    O PG, vendo que já existe um bloqueio ACCESS EXCLUSIVE em espera na tabela, não concederá mais bloqueios ACCESS SHARE adicionais na tabela até que o ACCESS EXCLUSIVE termine sua operação e libere o bloqueio. Então agora seus SELECTs estão se acumulando e tudo está travado até que todas as transações que foram selecionadas a partir registerde quando ALTER TABLE tentou pela primeira vez pegar o bloqueio, seja commit ou rollback.

    A correção aqui, infelizmente, é "não faça isso"; o código da aplicação precisa emitir um COMMIT ou ROLLBACK (desde que o SELECT seja a única coisa que ocorreu na transação, não importa qual) logo após o SELECT, para que o bloqueio ACCESS SHARE seja liberado imediatamente. Você também pode dividir as etapas ADD COLUMN e ADD FOREIGN KEY, pois apenas ADD COLUMN requer ACCESS EXCLUSIVE (ADD FOREIGN KEY requer apenas SHARE ROW EXCLUSIVE, o que não entra em conflito com ACCESS EXCLUSIVE), mas ficaria surpreso se isso ajudasse em tudo ; é a sequência de bloqueio que está causando problemas, não o pouco de trabalho extra envolvido na execução de ADD FOREIGN KEY.

    • 1

relate perguntas