阅读使人充实,讨论使人敏捷,写作使人精确。
案例场景模拟
l 版本:使用PostgreSQL-12.0
akendb=# select version(); version -------------------------------------------------------------------------------------------------------- PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-5), 64-bit+(1 row)akendb=#
l 现象:drop语句挂起
akendb=# drop table tab_aken; ---被长时间挂起,truncate也会被挂起
什么原因阻塞了DDL
由于关系型数据库锁机制的实现,上述drop table操作长期挂起,基本其他的DDL同样也会被挂起,比如下面的create index、truncate table等。
l 执行truncate table,挂起
akendb=# truncate table tab_aken; ---被长时间挂起,truncate也会被挂起^CCancel request sentERROR: canceling statement due to user requestakendb=#
l 执行create index,挂起
akendb=# create index idx_test on public.tab_aken(id) where id=837214094;^CCancel request sentERROR: canceling statement due to user requestakendb=#
l 等待事件查看
语句drop table由于拿不到relation lock资源,被pid=16878的会话进程阻塞,而holder之所以长期阻塞后面的ddl,是因为它正处于以下等待事件中:
autovacuum: VACUUM ANALYZE public.tab_aken (to prevent wraparound)
akendb=# select pid waiter_pid,pg_blocking_pids(pid) holder_pid,backend_type,state,wait_event_type,wait_event,query from pg_stat_activity where state <> 'idle' and pid <> pg_backend_pid(); waiter_pid | holder_pid | backend_type | state | wait_event_type | wait_event |