Configuring the Support for Transactions on ClickHouse

Scenario

Atomicity means that a transaction is an inseparable unit of work. A transaction can contain multiple operations, which are either all executed or none executed. However, some exceptions may occur during transaction execution, for example, a user rolls back a transaction, a connection is disconnected, or a power failure occurs. As a result, the transaction execution is interrupted.

ClickHouse supports atomic write and transaction capabilities. The atomicity of a transaction means that after an operation of a transaction fails, the transaction can be rolled back to the state before the transaction is executed.

Start a ClickHouse transaction.

Writing data to local tables achieves better performance. So, multi-replica distributed transactions are recommended for adding, deleting, modifying, and querying data on local tables.
When data is written to a distributed table, the distributed table transaction insert_distributed_sync and local table transaction Mergetree/ReplicateMergeTree must be combined to support data writing.

Parameter Configuration

Log in to FusionInsight Manager and choose Cluster > Services > ClickHouse. Click Configurations then All Configurations, click ClickHouseServer(Role) > Reliability, and search for and modify the following parameters:

Parameter	Description
allow_transactions	Whether to support transactions. The value can be 0 or 1. The default value is 0, indicating that transactions are not supported. Set this parameter to 1, save the configuration, and restart the service for the support for transactions to take effect.
_clickhouse.metrika.cluster.internal_replication	Whether to write data to only one replica. The value can be true or false. The default value is true, indicating that data is inserted only to one replica. If this parameter is set to false, data is inserted to both replicas.

Parameter

Description

allow_transactions

Whether to support transactions. The value can be 0 or 1.

The default value is 0, indicating that transactions are not supported.
Set this parameter to 1, save the configuration, and restart the service for the support for transactions to take effect.

_clickhouse.metrika.cluster.internal_replication

Whether to write data to only one replica. The value can be true or false.

The default value is true, indicating that data is inserted only to one replica.
If this parameter is set to false, data is inserted to both replicas.

You can run the set implicit_transaction='true'; statement to use session-level implicit transactions. Currently, ClickHouse does not support interruption of alter queries. If the execution of alter queries (for example, lightweight delete) is interrupted, it cannot be rolled back even if implicit transactions are enabled. This is the same as open-source ClickHouse.
To insert data into a distributed table, perform the following steps:
Log in to FusionInsight Manager, choose Cluster > Services > ClickHouse, click Configurations then All Configurations, and change the value of _clickhouse.metrika.cluster.internal_replication to false, indicating that data is written to all replicas of a shard when being inserted to a distributed table.

At the session level, set insert_distributed_sync='true'; indicates that data is inserted to each actual table in synchronous mode when being inserted to a distributed table.