在 PostgreSQL 中,当你尝试删除一个有主键关联的表时,确实会出现锁定的情况。这是因为 PostgreSQL 需要确保数据的一致性和完整性,特别是在处理外键约束时
解决方法:
- 1.检查锁情况:
SELECT
pg_locks.locktype,
pg_locks.database,
pg_locks.relation,
pg_locks.page,
pg_locks.tuple,
pg_locks.virtualxid,
pg_locks.transactionid,
pg_locks.virtualtransaction,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
pg_stat_activity.application_name,
pg_stat_activity.client_addr,
pg_stat_activity.client_port,
pg_stat_activity.backend_start,
pg_locks.pid,
pg_stat_activity.query
FROM
pg_locks
JOIN
pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
- 2.终止阻塞事务:
如果找到了阻塞的事务,可以考虑终止这些事务,但请注意这可能会导致数据不一致。使用以下命令终止事务:
SELECT pg_terminate_backend(pid);
- 3.此时再去删除含有外键的子表中的数据,删除成功后再去删除主表数据