The SET TRANSACTION statement sets the transaction isolation level or priority after you BEGIN it but before executing the first statement that manipulates a database.

You can also set the session's default isolation level.

Synopsis

SET TRANSACTION ISOLATION LEVEL SNAPSHOT SERIALIZABLE , PRIORITY LOW NORMAL HIGH PRIORITY LOW NORMAL HIGH , ISOLATION LEVEL SNAPSHOT SERIALIZABLE

Required Privileges

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

Parameters

Parameter Description
ISOLATION LEVEL If you don’t want the transaction to run as SERIALIZABLE (CockroachDB’s default, which provides the highest level of isolation), you can set the isolation level to SNAPSHOT, which can provide better performance in high-contention scenarios.

For more information, see Transactions: Isolation Levels.

Default: SERIALIZABLE
PRIORITY If you don’t 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.

Default: NORMAL

Examples

Set Isolation & Priority

You can set a transaction’s isolation level to SNAPSHOT, as well as its priority to LOW or HIGH.

> BEGIN;

> SET TRANSACTION ISOLATION LEVEL SNAPSHOT, 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;

Set Session’s Default Isolation

You can also set the default isolation level for all transactions in the client’s current session using SET DEFAULT_TRANSACTION_ISOLATION TO <isolation level>.

> SHOW TRANSACTION ISOLATION LEVEL;
+-----------------------------+
| TRANSACTION ISOLATION LEVEL |
+-----------------------------+
| SERIALIZABLE                |
+-----------------------------+
> SET DEFAULT_TRANSACTION_ISOLATION TO SNAPSHOT

> SHOW TRANSACTION ISOLATION LEVEL;
+-----------------------------+
| TRANSACTION ISOLATION LEVEL |
+-----------------------------+
| SNAPSHOT                    |
+-----------------------------+

See Also



Yes No