Transactions modes (autocommit)

[den-crane]

I believe to be more ANSI compatible Clickhouse should implement this:

A setting transactions_enabled = false (default)

• false -- old (current) behaviour, no transactions.
• true -- transactions supported.

A settings autocommit = true (default) applicable only when transactions_enabled = true

• true - each command (insert, select ... ) automatically starts a transaction and commits / rollsback automatically (when the command finishes successfully / unsuccessfully or with a timeout).
• false - each command (insert, select ... ) automatically starts a transaction and to end the transaction need to execute commit.

Basically current begin transaction === set autocommit=false (they are synonyms).

[den-crane]

@tavplubix

[aadant]

@den-crane autocommit=true and transactions_enabled = true means that selects are in a transaction and current default transaction isolation level = read uncommitted behavior goes away :-)

[den-crane]

@den-crane autocommit=true and transactions_enabled = true means that selects are in a transaction and current default transaction isolation level = read uncommitted behavior goes away :-)

Exactly. This is the goal.

[UnamedRus]

#38344 ?

[tavplubix]

Yes, the setting implicit_transaction (false by default) that behaves like transactions_enabled = true, autocommit = true is already implemented in #38344. I'm not sure that we need two more settings for now. But maybe we should also support SET AUTOCOMMIT for compatibility.

[den-crane]

interesting

clickhouse-client --implicit_transaction=1

create table test(A Int64) Engine=MergeTree order by tuple();
B::Exception: Transactions are not supported for this type of query (CreateQuery__test). (NOT_IMPLEMENTED)

cat /etc/clickhouse-server/users.d/xxx.xml
<?xml version="1.0"?>
<clickhouse>
	<profiles>
		<default>
                   <implicit_transaction>1</implicit_transaction>
		</default>
	</profiles>
</clickhouse>

$ clickhouse-client
ClickHouse client version 22.9.1.2376.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.9.1 revision 54460.

Cannot load data for command line suggestions: Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Storage SystemFunctions (table system.functions (626458fd-5de4-4944-b25c-78498bd4a508)) does not support transactions. (NOT_IMPLEMENTED) (version 22.9.1.2376)

[tavplubix]

See also throw_on_unsupported_query_inside_transaction

[den-crane]

I see. OK.