使用JTA Atomikos连接多个Postgres数据库异常问题咨询

• 参数修改后未生效：max_prepared_transactions是PostgreSQL的静态启动参数，仅修改postgres.conf后执行配置重载（reload）不会加载该参数变更，必须重启数据库服务才能生效，这是该场景下最高发的诱因。
• 修改的配置文件并非运行实例加载的文件：多实例部署、容器化部署场景下很容易出现配置路径错配——修改的postgres.conf不是当前业务连接的数据库实例实际读取的配置文件；另外PostgreSQL的postgresql.auto.conf中配置的参数优先级高于postgres.conf，如果该文件里存在max_prepared_transactions = 0的配置，会直接覆盖手动修改的值。
• 事务触发逻辑存在差异：单库操作走本地事务，完全不会触发两阶段提交的prepare流程，因此不会校验prepared transaction开关；只有同时操作两个数据库、Atomikos触发XA分布式事务的prepare阶段时，才会触发该校验，此时如果任意一个连接对应的数据库实例prepared transaction开关未打开，就会抛出对应错误。
• 账号权限限制：部分云厂商托管的PostgreSQL实例会默认限制普通账号使用prepared transaction能力，即使参数配置正确，非授权账号执行PREPARE TRANSACTION时也会抛出同类错误提示。

按以下顺序排查即可解决问题：

1. 确认两个数据库实例的参数实际运行值
   分别用业务账号连接两个目标PostgreSQL库，执行如下SQL查看参数当前生效值：

   SHOW max_prepared_transactions;
   

   只要任意一个库返回值为0，就说明该库的参数配置未生效。
2. 修正配置并重启数据库
   对参数值为0的库，执行如下SQL确认实例实际加载的配置文件路径：

   SHOW config_file;
   

   打开对应路径的配置文件，确认max_prepared_transactions = 100的配置未被注释，同时检查同目录下的postgresql.auto.conf，删除其中覆盖该参数为0的配置项。修改完成后重启整个PostgreSQL服务，注意不能只执行配置reload，重启后再次执行第一步的校验SQL，确认两个库的参数值都为非0。
3. 校验Atomikos的XA数据源配置
   确认两个数据源的XA驱动类配置正确，必须使用org.postgresql.xa.PGXADataSource，不能配置为普通的非XA驱动类org.postgresql.Driver——非XA驱动无法参与分布式事务，在跨库事务触发时也会抛出同类异常。
4. 检查账号权限
   如果是云托管PostgreSQL实例，确认业务账号已被授予分布式事务（prepared transaction）的使用权限，部分云厂商需要在控制台手动开启该功能白名单；自建PostgreSQL的超级用户默认拥有该权限，普通账号可以通过超级用户授权获得对应能力。

内容的提问来源于stack exchange，提问作者Janu