为何Atomikos定期查询pg_prepared_xacts？如何调整查询周期？

Hey there! Let’s dig into your questions about Atomikos and that recurring PostgreSQL query—this is a great deep dive into how distributed transaction recovery works.

Why does the SELECT gid FROM pg_prepared_xacts where database = current_database() query run every 10 seconds? #

This query is part of Atomikos's built-in recovery mechanism. When working with distributed transactions (XA transactions), there’s always a risk of partial failures—like your app crashing mid-transaction, a network blip disconnecting a database, or a resource becoming unavailable.

Atomikos’s RecoveryManager runs periodically to scan for "prepared" transactions (those that have completed the first phase of 2PC but haven’t finalized the commit/rollback). The pg_prepared_xacts table in PostgreSQL stores exactly these in-doubt transactions. The query fetches the global transaction IDs (gid) so Atomikos can check if any transactions need to be resolved to keep your data consistent. The default 10-second interval is Atomikos’s out-of-the-box setting for balancing recovery speed and database load.

Can you adjust the query interval (e.g., to 1 minute)? #

Absolutely! You can tweak this interval using Atomikos configuration properties, depending on which version you’re using:

• If you’re using the classic Atomikos Transaction Essentials library:
  Add this to your configuration (e.g., application.properties):

  com.atomikos.icatch.thread.interval=60000
  

  (Value is in milliseconds—60000 = 1 minute)
• If you’re using the Atomikos Spring Boot Starter:
  Use this property instead:

  spring.jta.atomikos.recovery.interval=60
  

  (Value is in seconds—60 = 1 minute)

Just keep in mind: Longer intervals mean slower recovery from failures, but less frequent database polling. Shorter intervals speed up recovery but add minor ongoing load to your databases. Pick a value that aligns with your system’s fault tolerance needs.

What happens when the query returns a gid? #

When Atomikos finds a gid in pg_prepared_xacts, it kicks off its recovery workflow:

1. Locate the transaction context: It uses the gid to retrieve the metadata of the distributed transaction (which resources were involved, what state each was in).
2. Check resource status: Atomikos contacts all PostgreSQL databases (and any other XA resources) involved in the transaction to confirm whether they’re ready to commit or need to roll back.
3. Finalize the transaction:
   • If all resources confirm they can commit, Atomikos triggers the commit phase for the entire distributed transaction.
   • If any resource failed or can’t confirm, Atomikos initiates a rollback to undo partial changes across all resources.
4. Clean up: Once the transaction is resolved (committed or rolled back), Atomikos removes the corresponding entry from pg_prepared_xacts to avoid reprocessing it in future scans.

This process ensures that no transactions get stuck in a "limbo" state—your databases will always end up in a consistent state even after unexpected failures.

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