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 DEFERRABLEDEFERRABLE | 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. |
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
Use the AS OF SYSTEM TIME option
You can execute the transaction using the database contents “as of” a specified time in the past.
Set the default transaction priority for a session
To set the default transaction priority for all transactions in a session, use thedefault_transaction_priority . For example:
transaction_priority is a read-only that cannot be set directly.