1.前言

线上一位同事联系我，说给一张表添加字段卡住，alter table xxx add column xxx卡住了，在单机PostgreSQL中常见的情况就是被select等DML阻塞了，因为alter table是8级锁，access exclusive，后面发现不是那么简单。

2.分析

alter table是最重的锁，和其他类型的锁都会互相冲突。

首先通过pg_stat_activity查看是被什么阻塞以及等待的事件：select pg_blocking_pids(pid) as blocked,pid,query,wait_event,wait_event_type from pg_stat_activity where query like '%alter%' and pid <> pg_backend_pid();

可以看到，被0号pid阻塞了，可能第一眼看到0号的pid有点懵，wait_event_type是Lock，Waiting to acquire a lock on a relation.

然后通过pg_locks查看具体等待的锁：

select locktype,relation::regclass as relname,page||','||tuple as ctid,virtualxid,transactionid as xid,virtualtransaction,pid,mode,granted from pg_locks where relation = 't1'::regclass;

select locktype,relation::regclass as relname,page||','||tuple as ctid,virtualxid,transactionid as xid,virtualtransaction,pid,mode,granted from pg_locks where pid = 3490;

一个进程在一个时间点只能等待至多一个锁，等待锁用granted=f表示，会休眠至其他锁被释放，或者系统检测到死锁。

可以看到，3490进程获取不到AccessExclusiveLock，于是又回到了前面所说，被“0”pid阻塞了，0号pid是个什么玩意？

0号pid在PostgreSQL内部对应的是2pc，prepare transaction预备事务。

prepared transaction是独立于会话，抗崩溃和状态维护的事务。事务的状态会持久化在磁盘上，这一点不难理解，因为两阶段提交中一般都涉及多台数据库之间的协同，各台数据库收到prepare transaction的命令后，如果要返回成功，那么该节点必须要确保自己后续能在被要求提交事务的时候去提交，或后续能在被要求回滚的时候回滚，所以PostgreSQL需要把相关信息持久化到存储上，随时响应。

既然持久化了，那么数据库服务器即使从崩溃中重新启动后也可以恢复事务，直到在对prepared transaction执行回滚或提交操作之前，将一直维护该事务。

在PostgreSQL中，使用prepared transaction创建一个2pc，注意需要将max_prepared_transaction设置为非0值，另外注意在standby环境下上，最好将其设置的比max_connections大一点，以免standby不能接收查询。

这样就创建好了一个预备事务，如上所说，会持久化至存储上，对应的目录在~/data/pg_twophase，可以用hexdump简单看一下

重启一下服务器，可以看到该预备事务还是存在，所以可以进行提交或回滚。

那么预备事务可能带来的危害，就不难想到了，首先预备事务会持久化，会“占着”事务，占着茅坑不拉屎，同时还会持有锁，所以alter table等DDL就被该预备事务阻塞。

阻塞样例如下：因为是insert的预备事务，对应就是RowExclusive的意向写锁：

session1：

session2模拟添加列，可以看到被阻塞：

查看锁信息，可以看到，被0号pid阻塞：

除此之外，就是我们熟悉的表膨胀，由于占着xid，会导致oldestxmin特别小，导致表膨胀，因为vacuum里的逻辑如下：

该函数计算当前tuple的xmax是否大于或等于OldestXmin。xmax是删除这个tuple的事务ID，而OldestXmin由GetOldestXmin函数计算，是所有活跃事务的ID，以及所有事务的xmin 组成的集合中最小的事务ID。所有ID大于这个OldestXmin的事务，都是“新近”开启的事务，其他事务可能需要读取这个旧版本用于查询，所以不能物理删除，则返回HEAPTUPLE_RECENTLY_DEAD，保留此tuple。换句话说，就是产生垃圾tuple的事务号，通常在为垃圾tuple的头信息中的xmax版本号大于或等于vacuum开启时数据库中最小的(backend_xmin, backend_xid)，这条垃圾tuple就不能被回收，进而导致表膨胀。

如下可以看到，xmax为540，执行vacuum的时候会跳过这一行，INFO: "test2": found 0 removable, 1 nonremovable row versions in 1 out of 1 pages

其他表同理，可以看到，test3的表大小即使vacuum full也不会收缩，依旧为42MB。INFO: "test3": found 0 removable, 1000000 nonremovable row versions in 5406 out of 5406 pages

还有一大危害，就是年龄，也就是最多只能降低到最早未提交的事务。

下面例子可以看到，年龄一直是17。

3.总结

1．2pc一定要及时commit或者回滚，危害很多，表膨胀、持锁、年龄回收等

2．基于流复制的备库，2PC事务也会复制过去

3．生产中，结合pg_prepared_xacts定制监控，对于长时间不提交的prepared transaction，及时告警。

4．另外对于临时表，也不支持预备事务

好在默认情况下，PostgreSQL并不开启两阶段提交，可以通过在postgresql.conf文件中设置max_prepared_transactions，除非知道自己在做什么，否则不建议使用预备事务。

令人头大的2PC

1.前言

2.分析

3.总结