Una query PostgreSQL non elaborata si blocca quando viene eseguita con TypeOrm
Aug 18 2020
Sto effettuando questa query di aggiornamento:
await this.userRepository.query(
`
update "user" u
set profile_pic = i.name
from user_images_image uii
inner join image i on uii."imageId" = i."id"
where uii."userId" = u.id and u.id = ${userId} and i.id = ${imageId};
`,
);
E semplicemente non termina anche se eseguo la stessa query nel mio client DB e funziona come previsto.
Risposte
1 GustavoKawamoto Aug 19 2020 at 01:48
Dopo una sessione di risoluzione dei problemi abbiamo scoperto che la query era stata bloccata perché un'altra connessione stava tentando di aggiornare una risorsa correlata (un record in una users
tabella con una chiave esterna da user_images_image
) per una transazione diversa nello stesso metodo, entrambi in attesa dei risultati per il commit .
Usando la stessa connessione abbiamo trovato il comportamento giusto.
Lo abbiamo scoperto utilizzando la query di monitoraggio del blocco dai documenti :
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process,
blocked_activity.application_name AS blocked_application,
blocking_activity.application_name AS blocking_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;