SET TRANSACTION

The SET TRANSACTION sets the transaction priority, access mode, and “as of” timestamp after you it but before executing the first statement that manipulates a database.

Synopsis

Required privileges

No 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 . The current priority is also exposed as the read-only `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 `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 .
`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.

CockroachDB now only supports SERIALIZABLE isolation, so transactions can no longer be meaningfully set to any other ISOLATION LEVEL. In previous versions of CockroachDB, you could set transactions to SNAPSHOT isolation, but that feature has been removed.

Examples

Set priority

> BEGIN;

> SET TRANSACTION PRIORITY HIGH;

> SAVEPOINT cockroach_restart;

> UPDATE products SET inventory = 0 WHERE sku = '8675309';

> INSERT INTO orders (customer, sku, status) VALUES (1001, '8675309', 'new');

> RELEASE SAVEPOINT cockroach_restart;

> 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.

> BEGIN;

> SET TRANSACTION AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';

> SELECT * FROM orders;

> SELECT * FROM products;

> 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 . For example:

> SET default_transaction_priority 'high';

> SHOW transaction_priority;

  transaction_priority
------------------------
  high

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

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Required privileges

Parameters

Examples

Set priority

Use the `AS OF SYSTEM TIME` option

Set the default transaction priority for a session

See also

​Synopsis

​Required privileges

​Parameters

​Examples

​Set priority

​Use the AS OF SYSTEM TIME option

​Set the default transaction priority for a session

​See also

Synopsis

Required privileges

Parameters

Examples

Set priority

Use the `AS OF SYSTEM TIME` option

Set the default transaction priority for a session

See also