SET TRANSACTION

On this page Carat arrow pointing down

The SET TRANSACTION statement sets the transaction priority, access mode, "as of" timestamp, and isolation level. These are applied after you BEGIN the transaction and before executing the first statement that manipulates a database.

Note:

Cockroach Labs recommends leaving the transaction priority at the default setting in almost all cases. Changing the transaction priority to HIGH in particular can lead to difficult-to-debug interactions with other transactions executing on the system.

If you are setting a transaction priority to avoid contention or hot spots, or to get better query performance, it is usually a sign that you need to update your schema design and/or review the data access patterns of your workload.

Synopsis

SET SESSION TRANSACTION transaction_mode_list

Required privileges

No privileges are required to set the transaction priority. However, privileges are required for each statement within a transaction.

Parameters

Parameter Description
PRIORITY If you do not want the transaction to run with NORMAL priority, you can set it to LOW or HIGH. Transactions with higher priority are less likely to need to be retried. For more information, see Transactions: Priorities.

The current priority is also exposed as the read-only session variable transaction_priority.

Default: NORMAL
READ Set the transaction access mode to READ ONLY or READ WRITE. The current transaction access mode is also exposed as the session variable transaction_read_only.

Default: READ WRITE
AS OF SYSTEM TIME Execute the transaction using the database contents "as of" a specified time in the past.

The AS OF SYSTEM TIME clause can be used only when the transaction is read-only. If the transaction contains any writes, or if the READ WRITE mode is specified, an error will be returned.

For more information, see AS OF SYSTEM TIME.
NOT DEFERRABLE
DEFERRABLE
This clause is supported for compatibility with PostgreSQL. NOT DEFERRABLE is a no-op and the default behavior for CockroachDB. DEFERRABLE returns an unimplemented error.
ISOLATION LEVEL Set the transaction isolation level. Transactions use SERIALIZABLE isolation by default. They can be configured to run at READ COMMITTED isolation.

This clause only takes effect if specified at the beginning of the transaction.

Examples

Set isolation level

You can set the transaction isolation level to SERIALIZABLE or READ COMMITTED.

If not specified, transactions use the value of the current session's default_transaction_isolation variable.

icon/buttons/copy
BEGIN;
icon/buttons/copy
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Set priority

Warning:

This example assumes you're using client-side retry handling.

icon/buttons/copy
> BEGIN;
icon/buttons/copy
> SET TRANSACTION PRIORITY HIGH;
icon/buttons/copy
> SAVEPOINT cockroach_restart;
icon/buttons/copy
> UPDATE products SET inventory = 0 WHERE sku = '8675309';
icon/buttons/copy
> INSERT INTO orders (customer, sku, status) VALUES (1001, '8675309', 'new');
icon/buttons/copy
> RELEASE SAVEPOINT cockroach_restart;
icon/buttons/copy
> COMMIT;

Use the AS OF SYSTEM TIME option

You can execute the transaction using the database contents "as of" a specified time in the past.

icon/buttons/copy
> BEGIN;
icon/buttons/copy
> SET TRANSACTION AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';
icon/buttons/copy
> SELECT * FROM orders;
icon/buttons/copy
> SELECT * FROM products;
icon/buttons/copy
> COMMIT;

Set the default transaction priority for a session

To set the default transaction priority for all transactions in a session, use the default_transaction_priority session variable. For example:

> SET default_transaction_priority 'high';
> SHOW transaction_priority;
  transaction_priority
------------------------
  high

Note that transaction_priority is a read-only session variable that cannot be set directly.

See also


Yes No
On this page

Yes No