title_wip

#### Enable experimental transaction support[​](#enable-experimental-transaction-support "Direct link to Enable experimental transaction support")

```
<clickhouse>
    <allow_experimental_transactions>1</allow_experimental_transactions>
</clickhouse>
```

#### Basic configuration for a single ClickHouse server node with ClickHouse Keeper enabled[​](#basic-configuration-for-a-single-clickhouse-server-node-with-clickhouse-keeper-enabled "Direct link to Basic configuration for a single ClickHouse server node with ClickHouse Keeper enabled")

Note

See the [deployment](/docs/architecture/introduction) documentation for details on deploying ClickHouse server and a proper quorum of ClickHouse Keeper nodes. The configuration shown here is for experimental purposes.

```
<clickhouse replace="true">
    <logger>
        <level>debug</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>3</count>
    </logger>
    <display_name>node 1</display_name>
    <listen_host>0.0.0.0</listen_host>
    <http_port>8123</http_port>
    <tcp_port>9000</tcp_port>
    <zookeeper>
        <node>
            <host>clickhouse-01</host>
            <port>9181</port>
        </node>
    </zookeeper>
    <keeper_server>
        <tcp_port>9181</tcp_port>
        <server_id>1</server_id>
        <log_storage_path>/var/lib/clickhouse/coordination/log</log_storage_path>
        <snapshot_storage_path>/var/lib/clickhouse/coordination/snapshots</snapshot_storage_path>
        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
            <raft_logs_level>information</raft_logs_level>
        </coordination_settings>
        <raft_configuration>
            <server>
                <id>1</id>
                <hostname>clickhouse-keeper-01</hostname>
                <port>9234</port>
            </server>
        </raft_configuration>
    </keeper_server>
</clickhouse>
```

### Example[​](#example "Direct link to Example")

#### Verify that experimental transactions are enabled[​](#verify-that-experimental-transactions-are-enabled "Direct link to Verify that experimental transactions are enabled")

Issue a `BEGIN TRANSACTION` or `START TRANSACTION` followed by a `ROLLBACK` to verify that experimental transactions are enabled, and that ClickHouse Keeper is enabled as it is used to track transactions.

```
BEGIN TRANSACTION
```

```
Ok.
```

Tip

If you see the following error, then check your configuration file to make sure that `allow_experimental_transactions` is set to `1` (or any value other than `0` or `false`).

```
Code: 48. DB::Exception: Received from localhost:9000.
DB::Exception: Transactions are not supported.
(NOT_IMPLEMENTED)
```

You can also check ClickHouse Keeper by issuing

```
echo ruok | nc localhost 9181
```

ClickHouse Keeper should respond with `imok`.

```
ROLLBACK
```

```
Ok.
```

#### Create a table for testing[​](#create-a-table-for-testing "Direct link to Create a table for testing")

Tip

Creation of tables is not transactional. Run this DDL query outside of a transaction.

```
CREATE TABLE mergetree_table
(
    `n` Int64
)
ENGINE = MergeTree
ORDER BY n
```

```
Ok.
```

#### Begin a transaction and insert a row[​](#begin-a-transaction-and-insert-a-row "Direct link to Begin a transaction and insert a row")

```
BEGIN TRANSACTION
```

```
Ok.
```

```
INSERT INTO mergetree_table FORMAT Values (10)
```

```
Ok.
```

```
SELECT *
FROM mergetree_table
```

```
┌──n─┐
│ 10 │
└────┘
```

Note

You can query the table from within a transaction and see that the row was inserted even though it has not yet been committed.

#### Rollback the transaction, and query the table again[​](#rollback-the-transaction-and-query-the-table-again "Direct link to Rollback the transaction, and query the table again")

Verify that the transaction is rolled back:

```
ROLLBACK
```

```
Ok.
```

```
SELECT *
FROM mergetree_table
```

```
Ok.

0 rows in set. Elapsed: 0.002 sec.
```

#### Complete a transaction and query the table again[​](#complete-a-transaction-and-query-the-table-again "Direct link to Complete a transaction and query the table again")

```
BEGIN TRANSACTION
```

```
Ok.
```

```
INSERT INTO mergetree_table FORMAT Values (42)
```

```
Ok.
```

```
COMMIT
```

```
Ok. Elapsed: 0.002 sec.
```

```
SELECT *
FROM mergetree_table
```

```
┌──n─┐
│ 42 │
└────┘
```

### Transactions introspection[​](#transactions-introspection "Direct link to Transactions introspection")

You can inspect transactions by querying the `system.transactions` table, but note that you cannot query that
table from a session that is in a transaction. Open a second `clickhouse client` session to query that table.

```
SELECT *
FROM system.transactions
FORMAT Vertical
```

```
Row 1:
──────
tid:         (33,61,'51e60bce-6b82-4732-9e1d-b40705ae9ab8')
tid_hash:    11240433987908122467
elapsed:     210.017820947
is_readonly: 1
state:       RUNNING
```

## More Details[​](#more-details "Direct link to More Details")

See this [meta issue](https://github.com/ClickHouse/ClickHouse/issues/48794) to find much more extensive tests and to keep up to date with the progress.

- [Case 1: INSERT into one partition, of one table, of the MergeTree\* family](#case-1-insert-into-one-partition-of-one-table-of-the-mergetree-family)- [Case 2: INSERT into multiple partitions, of one table, of the MergeTree\* family](#case-2-insert-into-multiple-partitions-of-one-table-of-the-mergetree-family)- [Case 3: INSERT into one distributed table of the MergeTree\* family](#case-3-insert-into-one-distributed-table-of-the-mergetree-family)- [Case 4: Using a Buffer table](#case-4-using-a-buffer-table)- [Case 5: Using async\_insert](#case-5-using-async_insert)- [Notes](#notes)- [Transactions, Commit, and Rollback](#transactions-commit-and-rollback)
              - [Requirements](#requirements)- [Notes](#notes-1)- [Configuration](#configuration)- [Example](#example)- [Transactions introspection](#transactions-introspection)- [More Details](#more-details)

Was this page helpful?