使用 TypeOrm 执行时,原始 PostgreSQL 查询挂起 [英] A raw PostgreSQL query hangs when executed with TypeOrm
问题描述
我正在执行此更新查询:
I'm making this update query:
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};
`,
);
即使我在我的数据库客户端中执行相同的查询并且它按预期工作,它也不会终止.
And it just does not terminate even though I execute the same query in my DB client and it works as expected.
推荐答案
在故障排除会话之后,我们发现查询被锁定,因为另一个连接正在尝试更新相关资源(users
表,带有来自 user_images_image
的外键,用于相同方法的不同事务,两者都在等待提交结果.
After a troubleshooting session we found out that the query was being locked because another connection was trying to update a related resource (a record in a users
table with a foreign key from user_images_image
) for a different transaction in the same method, both awaiting it's results to commit.
通过使用相同的连接,我们最终得到了正确的行为.
By using the same connection we ended up with the right behaviour.
We found that out by using the Lock monitoring query from the docs:
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;
这篇关于使用 TypeOrm 执行时,原始 PostgreSQL 查询挂起的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!