Processing Method for PostgreSQL database transactions in unknown state, postgresql Processing Method

Processing Method for PostgreSQL database transactions in unknown state, postgresql Processing Method

Background

Database transactions are atomic operations, either successful or failed. However, from the client perspective, there may be a third State: unknown.

After the client submits a transaction (rollback, commit, prepare xact, rollback pxact, commit pxact) Request, the database receives the request and may fail or succeed, in any case, write the WAL log and CLOG, and then the database returns the execution result to the client ACK.

There are several possibilities, so the client does not know what the execution is like?

After receiving the request from the client, the database does not return any ACK to the client. The client is confused about this request. The database can only be in the UNKNOWN state.

UNKNOWN Transaction Processing

An unknown transaction is a transaction in which the client does not receive the commit/rollback ACK. I don't know whether it is successful or failed.

Both quorum based sync replication and single-node transactions may have UNKNOWN transactions, with the same effect and form.

How to handle unknown transactions?

Unknown transactions are divided into the following situations.

Rollback, commit, prepare xact, rollback pxact, commit pxact:

1. Solve the unknown status issue in two phases

Unknown in the prepare phase. After switching the leader, the client checks the prepare xact status through the pg_prepared_xacts view. If no prepare xact is available, the entire transaction is restarted. If prepare xact exists, prepare xact is successful.

Commit or rollback prepare xact stage unknown. Check the prepare xact status after switching. If yes, retry commit or rollback prepare xact. If it does not exist, it indicates that the operation has succeeded (we believe that the 2PC operation is successful.

2. For non-two-phase transactions, rollback unknown does not need to be processed. rollback failure or success results are the same for the client. Because it will be rolled back in any case, which guarantees database atomicity.

3. Non-two-phase transactions, commit unknown processing, extremely rigorous scenarios, the program can design the transaction status to be traceable, for example:

At the beginning of a transaction, the transaction number or unique sequential number is recorded. The transaction number is a unique sequential number in the database and can be queried Based on the transaction number, such as postgresql.

However, not all databases have such interfaces. For example, for a database with non-physical stream replication, you can add a globally unique sequential number to the transaction to check whether the transaction is committed. The atomic feature of the transaction is used here, either completely successful or completely failed. An example is provided.

An example of using business flow to determine the transaction status:

Begin;
Generate a unique business flow ID, write it to a flow meter, and record the flow number in the program or other databases for future reference.
Execute transactions
Commit transactions;

-- Unknown appears

Query whether this record exists in the database by the unique service flow ID.
If the transaction does not exist, the transaction commit fails.
If yes, the transaction is successfully committed. (Because database transactions are atomic operations)