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.
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 hotspots, 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
Required privileges
No privileges are required to set the transaction priority. However, privileges are required for each statement within a transaction.Parameters
Examples
Set isolation level
You can set the transaction isolation level toSERIALIZABLE or READ COMMITTED.
If not specified, transactions use the value of the current session’s default_transaction_isolation variable.
Set priority
This example assumes you’re using client-side retry handling.
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 session variable. For example:
transaction_priority is a read-only session variable that cannot be set directly.