BEGIN

{{% include-from-yaml data="txn_details" name="txn-details" %}}

Materialize only supports read-only transactions or write-only (specifically, insert-only) transactions. See Details for more information.

Syntax

BEGIN [ <option>, ... ];

You can specify the following optional settings for BEGIN:

Details

Transactions in Materialize are either read-only transactions or write-only transactions as determined by either:

• The first statement after the BEGIN, or

• The READ ONLY option is specified.

Read-only transactions

In Materialize, read-only transactions can be either:

• a SELECTonly transaction that only contains SELECT statements or

• a SUBSCRIBE-based transactions that only contains a single DECLARE ... CURSOR FOR SUBSCRIBE statement followed by subsequent FETCH statement(s). ¹

{{< note >}}

• During the first query, a timestamp is chosen that is valid for all of the objects referenced in the query. This timestamp will be used for all other queries in the transaction.

• The transaction will additionally hold back normal compaction of the objects, potentially increasing memory usage for very long running transactions.

{{</ note >}}

SELECT-only transactions

A SELECT-only transaction only contains SELECT statement.

The first SELECT statement:

• Determines the timestamp that will be used for all other queries in the transaction.

• Determines which objects can be queried in the transaction block.

Specifically,

• Subsequent SELECT statements in the transaction can only reference objects from the schema(s) referenced in the first SELECT statement (as well as a subset of objects from the mz_catalog and mz_internal schemas).

• These objects must have existed at beginning of the transaction.

For example, in the transaction block below, first SELECT statement in the transaction restricts subsequent selects to objects from test and public schemas.

BEGIN;
SELECT o.*,i.price,o.quantity * i.price as subtotal
FROM test.orders as o
JOIN public.items as i ON o.item = i.item;

-- Subsequent queries must only reference objects from the test and public schemas that existed at the start of the transaction.

SELECT * FROM test.auctions limit 1;
SELECT * FROM public.sales_items;
COMMIT;

Reading from a schema not referenced in the first statement or querying objects created after the transaction started (even if in the allowed schema(s)) will produce a Same timedomain error. Same timedomain error provides a list of the allowed objects in the transaction.

Same timedomain error

Transactions can only reference objects in the same timedomain.

The first SELECT statement in a transaction determines which schemas the subsequent SELECT statements in the transaction can query. If a subsequent SELECT references an object from another schema or an object created after the transaction started, the transaction will error with the same time domain error.

The timedomain error lists both the objects that are not in the timedomain as well as the objects that can be referenced in the transaction (i.e., in the timedomain).

If an object in the timedomain is a view, it will be replaced with the objects in the view definition.

SUBSCRIBE-based transactions

A SUBSCRIBE-based transaction only contains a single DECLARE ... CURSOR FOR SUBSCRIBE statement followed by subsequent FETCH statement(s). ¹

BEGIN;
DECLARE c CURSOR FOR SUBSCRIBE (SELECT * FROM flippers);

-- Subsequent queries must only FETCH from the cursor

FETCH 10 c WITH (timeout='1s');
FETCH 20 c WITH (timeout='1s');
COMMIT;

Write-only transactions

In Materialize, a write-only transaction is an INSERT-only transaction that only contains INSERT statements.

INSERT-only transactions

{{% include-from-yaml data="txn_details" name="txn-insert-only" %}}

See also

• COMMIT
• ROLLBACK