Commit At Most Once

Commit At Most Once#

Commit scope kind: CAMO

Overview#

The objective of the Commit At Most Once (CAMO) feature is to prevent the application from committing more than once.

Without CAMO, when a client loses connection after a COMMIT is submitted, the application might not receive a reply from the server and is therefore unsure whether the transaction committed.

The application can’t easily decide between the two options of:

  • Retrying the transaction with the same data, since this can in some cases cause the data to be entered twice

  • Not retrying the transaction and risk that the data doesn’t get processed at all

Either of those is a critical error with high-value data.

One way to avoid this situation is to make sure that the transaction includes at least one INSERT into a table with a unique index. However, that depends on the application design and requires application-specific error-handling logic, so it isn’t effective in all cases.

The CAMO feature in PGD offers a more general solution and doesn’t require an INSERT . When activated by bdr.commit_scope , the application receives a message containing the transaction identifier, if already assigned. Otherwise, the first write statement in a transaction sends that information to the client.

If the application sends an explicit COMMIT , the protocol ensures that the application receives the notification of the transaction identifier before the COMMIT is sent. If the server doesn’t reply to the COMMIT , the application can handle this error by using the transaction identifier to request the final status of the transaction from another PGD node. If the prior transaction status is known, then the application can safely decide whether to retry the transaction.

CAMO works by creating a pair of partner nodes that are two PGD nodes from the same PGD group. In this operation mode, each node in the pair knows the outcome of any recent transaction executed on the other peer and especially (for our need) knows the outcome of any transaction disconnected during COMMIT . The node that receives the transactions from the application might be referred to as “origin” and the node that confirms these transactions as “partner.” However, there’s no difference in the CAMO configuration for the nodes in the CAMO pair. The pair is symmetric.

Warning

CAMO requires changes to the user's application to take advantage of the advanced error handling. Enabling a parameter isn't enough to gain protection. Reference client implementations are provided to customers on request.

To use CAMO, an application must issue an explicit COMMIT message as a separate request, not as part of a multi-statement request. CAMO can’t provide status for transactions issued from procedures or from single-statement transactions that use implicit commits.

Configuration#

See the CAMO commit scope reference for configuration parameters.

Confirmation#

Confirmation Level

CAMO handling

received

Not applicable, only uses the default, VISIBLE.

replicated

Not applicable, only uses the default, VISIBLE.

durable

Not applicable, only uses the default, VISIBLE.

visible (default)

Confirms the transaction after all of its changes are flushed to disk and it's visible to concurrent transactions.

Limitations#

See the CAMO section of Limitations .

Failure scenarios#

Different failure scenarios occur in different configurations.

Data persistence at receiver side#

By default, a PGL writer operates in bdr.synchronous_commit = off mode when applying transactions from remote nodes. This holds true for CAMO as well, meaning that transactions are confirmed to the origin node possibly before reaching the disk of the CAMO partner. In case of a crash or hardware failure, a confirmed transaction might be unrecoverable on the CAMO partner by itself. This isn’t an issue as long as the CAMO origin node remains operational, as it redistributes the transaction once the CAMO partner node recovers.

This in turn means CAMO can protect against a single-node failure, which is correct for local mode as well as or even in combination with remote write.

To cover an outage of both nodes of a CAMO pair, you can use bdr.synchronous_commit = local to enforce a flush prior to the pre-commit confirmation. This doesn’t work with either remote write or local mode and has a performance impact due to I/O requirements on the CAMO partner in the latency sensitive commit path.

Asynchronous mode#

When the DEGRADE ON ... TO ASYNC clause is used in the commit scope, a node detects whether its CAMO partner is ready. If not, it temporarily switches to asynchronous (local) mode. When in this mode, a node commits transactions locally until switching back to CAMO mode.

This doesn’t allow COMMIT status to be retrieved, but it does let you choose availability over consistency. This mode can tolerate a single-node failure. In case both nodes of a CAMO pair fail, they might choose incongruent commit decisions to maintain availability, leading to data inconsistencies.

For a CAMO partner to switch to ready, it needs to be connected, and the estimated catchup interval needs to drop below the timeout value of TO ASYNC . You can check the current readiness status of a CAMO partner with bdr.is_camo_partner_ready() , while bdr.node_replication_rates provides the current estimate of the catchup time.

The switch from CAMO-protected to asynchronous mode is only ever triggered by an actual CAMO transaction. This is true either because the commit exceeds the timeout value of TO ASYNC or, in case the CAMO partner is already known, disconnected at the time of commit. This switch is independent of the estimated catchup interval. If the CAMO pair is configured to require the current node to be the write lead of a group as configured through the enable_routing node group option. See Migration to commit scopes for syntax. This can prevent a split brain situation due to an isolated node from switching to asynchronous mode. If enable_routing isn’t set for the CAMO group, the origin node switches to asynchronous mode immediately.

The switch from asynchronous mode to CAMO mode depends on the CAMO partner node, which initiates the connection. The CAMO partner tries to reconnect at least every 30 seconds. After connectivity is reestablished, it might therefore take up to 30 seconds until the CAMO partner connects back to its origin node. Any lag that accumulated on the CAMO partner further delays the switch back to CAMO protected mode.

Unlike during normal CAMO operation, in asynchronous mode there’s no added commit overhead. This can be problematic, as it allows the node to continuously process more transactions than the CAMO pair can normally process. Even if the CAMO partner eventually reconnects and applies transactions, its lag only ever increases in such a situation, preventing reestablishing the CAMO protection. To artificially throttle transactional throughput, PGD provides the bdr.camo_local_mode_delay setting, which allows you to delay a COMMIT in local mode by an arbitrary amount of time. We recommend measuring commit times in normal CAMO mode during expected workloads and configuring this delay accordingly. The default is 5 ms, which reflects a asynchronous network and a relatively quick CAMO partner response.

Consider the choice of whether to allow asynchronous mode in view of the architecture and the availability requirements. The following examples provide some detail.

Example#

This example considers a setup with two PGD nodes that are the CAMO partner of each other:

- - create a CAMO commit scope for a group over
- - a definite pair of nodes
SELECT bdr.create_commit_scope(
    commit_scope_name := example_scope,
    origin_node_group := camo_dc,
    rule := ALL (left_dc) CAMO DEGRADE ON (timeout=500ms) TO ASYNC
);

For this CAMO commit scope to be legal, the number of nodes in the group must equal exactly 2. Using ALL or ANY 2 on a group consisting of several nodes is an error because the unquantified group expression doesn’t resolve to a definite pair of nodes.

With asynchronous mode#

If asynchronous mode is allowed, there’s no single point of failure. When one node fails:

  • The other node can determine the status of all transactions that were disconnected during COMMIT on the failed node.

  • New write transactions are allowed. If the second node also fails, then the outcome of those transactions that were being committed at that time is unknown.

Without asynchronous mode#

If asynchronous mode isn’t allowed, then each node requires the other node for committing transactions, that is, each node is a single point of failure. When one node fails:

  • The other node can determine the status of all transactions that were disconnected during COMMIT on the failed node.

  • New write transactions are prevented until the node recovers.

Application use#

Overview and requirements#

CAMO relies on a retry loop and specific error handling on the client side. There are three aspects to it:

  • The result of a transaction’s COMMIT needs to be checked and, in case of a temporary error, the client must retry the transaction.

  • Prior to COMMIT , the client must retrieve a global identifier for the transaction, consisting of a node id and a transaction id (both 32-bit integers).

  • If the current server fails while attempting a COMMIT of a transaction, the application must connect to its CAMO partner, retrieve the status of that transaction, and retry depending on the response.

The application must store the global transaction identifier only for the purpose of verifying the transaction status in case of disconnection during COMMIT . In particular, the application doesn’t need another persistence layer. If the application fails, it needs only the information in the database to restart.

To illustrate this, this example shows a retry loop in a CAMO-aware client application, written in a C-like pseudo-code. It expects two DSNs, origin_dsn and partner_dsn , providing connection information. These usually are the same DSNs as used for the initial call to bdr.create_node and can be looked up in bdr.node_summary , column interface_connstr .

PGconn *conn = PQconnectdb(origin_dsn);

The process starts connecting to the origin node. Now enter the loop:

loop {
    PQexec(conn, "BEGIN");

Next, start the transaction and begin populating it with changes:

PQexec(conn, "INSERT INTO ...");
...

Once you’re done, you need to make a record of the local node id and the transaction id. Both are available as parameters.

node_id = PQparameterStatus(conn, "bdr.local_node_id");
xid = PQparameterStatus(conn, "transaction_id");

Now it’s ready to try to commit.

PQexec(conn, "COMMIT");
if (PQresultStatus(res) == PGRES_COMMAND_OK)
    return SUCCESS;

If the result is PGRES_COMMAND_OK , that’s good, and you can move on. But if it isn’t, you need to use CAMO to track the transaction to completion. The first question to ask is, “Was the connection bad?”

else if (PQstatus(res) == CONNECTION_BAD)
{

If it was a bad connection, then you can check on the CAMO partner node to see if the transaction made it there.

conn = PQconnectdb(partner_dsn);
if (!connectionEstablished())
    panic();

If you can’t connect to the partner node, there’s not a lot you can do. In this case, panic, or take similar actions.

But if you can connect, you can use bdr.logical_transaction_status() to find out how the transaction did. The code recorded the required values, node_id and xid (the transaction id), just before committing the transaction.

    sql = "SELECT bdr.logical_transaction_status($node_id, $xid)";
    txn_status = PQexec(conn, sql);
    if (txn_status == "committed")
        return SUCCESS;
    else
        continue;   // to retry the transaction on the partner
}

If the transaction reports it’s been committed, then you can call this transaction a success. No more action is required. If, on the other hand, it doesn’t report it’s been committed, continue in the loop so the transaction can be retried on the partner node.

    else
    {
        if (isPermanentError())
            return FAILURE;
        else
        {
            sleep(increasing_retry_delay);

            continue;
        }
    }
}

If status of the transaction wasn’t success or bad connection, check if the problem was a permanent error. If so, report a failure of the transaction. If not, you can still retry it. Have the code sleep for a period of time that increases with each retry, and then retry the transaction.

Working with the CAMO partner#

role assigned to them.

bdr.is_camo_partner_connected() allows checking the connection status of a CAMO partner node.

To check that the CAMO partner is ready, use the function bdr.is_camo_partner_ready . Underneath, this triggers the switch to and from local mode.

To find out more about the configured CAMO partner, use bdr.get_configured_camo_partner() . This function returns the local node’s CAMO partner.

You can wait on the CAMO partner to process the queue with the function

bdr.wait_for_camo_partner_queue() . This function is a wrapper of bdr.wait_for_apply_queue . The difference is that bdr.wait_for_camo_partner_queue()

defaults to querying the CAMO partner node. It returns an error if the local node isn’t part of a CAMO pair.

To check the status of a transaction that was being committed when the node failed, the application must use the function bdr.logical_transaction_status() .

You pass this function the node_id and transaction_id of the transaction you want to check on. You can use this function only on a node that’s part of a CAMO pair.

In all cases, you must call the function within 15 minutes after of issuing the commit. The CAMO partner must regularly purge such meta-information and therefore can’t provide correct answers for older transactions.

Before querying the status of a transaction, this function waits for the receive queue to be consumed and fully applied. This mechanism prevents early negative answers for transactions that were received but not yet applied.

Despite its name, it’s not always a read-only operation. If the status is unknown, the CAMO partner decides whether to commit or abort the transaction, storing that decision locally to ensure consistency going forward.

The client must not call this function before attempting to commit on the origin. Otherwise the transaction might be forced to roll back.

Connection pools and proxies#

Consider the effect of connection pools and proxies when designing a CAMO cluster. A proxy might freely distribute transactions to all nodes in the commit group, that is, to both nodes of a CAMO pair.

Take care to ensure that the application fetches the proper node id. When using session pooling, the client remains connected to the same node, so the node id remains constant for the lifetime of the client session. However, with finer-grained transaction pooling, the client needs to fetch the node id for every transaction, as in the example that follows.

A client that isn’t directly connected to the PGD nodes might not even notice a failover or switchover. But it can always use the bdr.local_node_id parameter to determine the node it’s currently connected to. In the crucial situation of a disconnect during COMMIT, the proxy must properly forward that disconnect as an error to the client applying the CAMO protocol.

For CAMO in received mode, a proxy that potentially switches between the CAMO pairs must use the bdr.wait_for_camo_partner_queue function to prevent stale reads.

CAMO limitations#

CAMO limitations are covered in Known issues and limitations .

Performance implications#

CAMO extends the Postgres replication protocol by adding a message roundtrip at commit. Applications have a higher commit latency than with asynchronous replication, mostly determined by the round-trip time between involved nodes. Increasing the number of concurrent sessions can help to increase parallelism to obtain reasonable transaction throughput.

The CAMO partner confirming transactions must store transaction states. Compared to non-CAMO operation, this might require an added seek for each transaction applied from the origin.

Client application testing#

Proper use of CAMO on the client side isn’t trivial. We strongly recommend testing the application behavior with the PGD cluster against failure scenarios, such as node crashes or network outages.

Contents