Start transaction​

.md

Copy page

Description​

This command starts a new transaction block.

Any Gel command outside of an explicit transaction block starts an implicit transaction block; the transaction is then automatically committed if the command was executed successfully, or automatically rollbacked if there was an error. This behavior is often called "autocommit".

When isolation is not specified, it defaults to serializable.

Parameters​

The transaction-mode can be one of the following:

isolation serializable

All statements in the current transaction can only see data changes that were committed before the first query or data modification statement was executed within this transaction. If a pattern of reads and writes among concurrent serializable transactions creates a situation that could not have occurred in any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization failure.

This level is the default isolation level.

Note that, compared to repeatable read, serializable level has a significantly higher probability of resulting in serialization failures, requires the whole transaction to be retried. If acceptable, consider using repeatable read or prefer repeatable read.

isolation repeatable read

All statements in the current transaction can only see data changes that were committed before the first query or data modification statement was executed within this transaction.

Compared to serializable, this level is less likely to result in serialization failures.

It is however possible for this level to allow serialization anomalies. This constitutes a series of transactions that would not be allowed if they were executed serially instead of concurrently.

For example, assume type X { is_selected: bool } and following query:

Copy

# transaction A: unselect all selected
update X filter .is_selected set { is_selected := false };

# transaction B: select all unselected
update X filter not .is_selected set { is_selected := true };

Running these two transactions serially would result in either all X being select or none being selected. But if executed concurrently, even with the repeatable read isolation level, we can end up with some X being selected and some not.

To avoid this, we can use the serializable isolation level.

read write

Sets the transaction access mode to read/write.

This is the default.

read only

Sets the transaction access mode to read-only. Any data modifications with insert, update, or delete are disallowed. Schema mutations via DDL are also disallowed.

deferrable

The transaction can be set to deferrable mode only when it is serializable and read only. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a serializable transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups.

Examples​

Start a new transaction and rollback it:

start transaction;
select 'Hello World!';
rollback;

Start a serializable deferrable transaction:

start transaction isolation serializable, read only, deferrable;

Prefer repeatable read​

In addition to the isolation levels above, some client libraries also support PreferRepeatableRead as a transaction isolation level. In this mode, the server will analyze the query and use repeatable read isolation level if it can. When it cannot, it will use serializable isolation level.

Client libraries that currently support this mode:

• TypeScript/JS

• Python

• Go