An PostgreSQL database transaction in an unknown state

background

A database transaction is an atomic operation that either succeeds or fails. But in fact, from the perspective of the client, there may be a third state: the unknown state.

When the client submits a request for the end of the transaction (rollback, commit, prepare xact, rollback pxact, commit pxact), the database receives the request, the database may fail to execute, the database may execute successfully, either way, write the WAL log for WAL and CLOG, and the database returns the execution result to the client ACK.

There are several possibilities where the client doesn't know what's going on with the execution.

After receiving the client's request, the database did not return any ACK to the client. The client was at a loss about this request, so it could only assume that the database was in the state of UNKNOWN.

Processing of UNKNOWN transactions

An unknown transaction is one in which the client does not receive commit/rollback ACK. I don't know if it's success or failure.

Multiple nodes (quorum based sync replication) and single nodes may have UNKNOWN transactions, with the same effect and shape.

How does handle unknown transactions?

unknown transactions fall into the following categories

unknown treatment for rollback, commit, prepare xact, rollback pxact, commit pxact

1. Solve unknown state problem

in two stages

prepare phase unknown, after switching leader, the client checks the prepare xact status through the pg_prepared_xacts view. If there is no prepare xact, the prepare xact status has failed, and the entire transaction can be restarted. If prepare xact exists, prepare xact has succeeded.

commit or rollback prepare xact stage unknown, check prepare xact status after switching, retry commit or rollback prepare xact. If it doesn't exist, it's a success (we think 2PC is definitely a success) and there's no need to deal with it.

2. For non-two-phase transactions, rollback unknown does not need to process, while rollback fails or succeeds, the result is the same for the client. Because it's going to roll back anyway, that's the atomic guarantee of the database.

3. Non-two-phase transaction, commit unknown processing, extremely rigorous scenario, the program can design the transaction state traceable, such as

When a transaction begins, the transaction number or unique stream number is recorded. The transaction number is a unique stream in the database and its status can be queried based on the transaction number, such as postgresql.

However, not all databases have this interface, such as databases that are not physically replicated, and you can add a globally unique stream number to a transaction to see if the transaction is committed. This takes advantage of the atomic nature of the transaction, which is all or nothing. You can use an example.

Example of transaction state determination using business flow:

begin;  
Generate unique business flow ID, write it to a flow table, and record the flow number in a program or other database for future reference.  
Execute transaction  
Commit transactions;  
 
-- unknown  
 
With unique business flow ID, query the database for the existence of this record.  
If it does not exist, the transaction commit failed.  
If it exists, the transaction commits successfully. (because database transactions are atomic operations)