Meta issue for transaction, commit, rollback

[DanRoscigno]

Docs are being added for experimental transaction support so that more community members can be aware and test. Rather than link from the docs to all of the PRs and issues where experimental transaction support is discussed, I will condense information here (and ask the community to add more as they open issues and PRs).

https://www.youtube.com/live/aFQs_zoYoXY?feature=share&t=1355

https://github.com/ClickHouse/ClickHouse/issues/22086

https://github.com/ClickHouse/ClickHouse/pull/38344

https://github.com/ClickHouse/ClickHouse/issues/22086 is a bit outdated, but still relevant. For example, mintid/maxtid were renamed to creation_tid/removal_tid, but the overall idea is almost the same. Although, this issue is mostly about internal implementation details, there are important notes about isolation and durability guarantees (tl;dr: durability is not guaranteed (and probably will never be) with default settings; isolation level is "snapshot isolation" unless you combine transactional and non-transactional queries, transactional mutations are too heavy and often roll back due to conflicts).

There are some tests that can be used as examples:

Simple tests (good for basic understanding): https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01173_transaction_control_queries.sql https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/02345_implicit_transaction.sql https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01172_transaction_counters.sql https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01167_isolation_hermitage.sh

More complex tests: https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01170_alter_partition_isolation.sh https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01171_mv_select_insert_isolation_long.sh https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01174_select_insert_isolation.sh https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01168_mutations_isolation.sh https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/01169_alter_partition_isolation_stress.sh https://github.com/ClickHouse/ClickHouse/blob/master/tests/integration/test_transactions/test.py

For introspection, we have:

• system.transactions table (shows info about running transactions, like system.processes)
• system.transaction_info_log (shows changes history),
• creation/removal_tid/csn and is_visible columns in system.parts
• and a set of functions (transactionID , transactionLatestSnapshot , transactionOldestSnapshot )

TIDs, CSNs and reserved values with special meaning: https://github.com/ClickHouse/ClickHouse/blob/0719d01d39ae3e2853f67f67d53cc77028377584/src/Common/TransactionID.h#L22-L55 https://github.com/ClickHouse/ClickHouse/blob/0719d01d39ae3e2853f67f67d53cc77028377584/src/Common/TransactionID.h#L94-L96

There's a quite tricky setting wait_changes_become_visible_after_commit_mode. Due to some implementation details, new changes do not become visible immediately after the COMMIT statement returns. COMMIT statement waits for the committed changes to become actually visible if it's set to WAIT. SYSTEM SYNC TRANSACTION LOG does the same. Also, COMMIT may throw UNKNOWN_STATUS_OF_TRANSACTION if the ZooKeeper connection was lost during committing. But it waits for the connection to be recovered and obtains actual commit status if the setting is set to WAIT_UNKNOWN (default).

Currently, transactions work with non-replicated MergeTree tables only. The feature is experimental and backward compatibility can be broken.