Skip to content

Latest commit

 

History

History
114 lines (88 loc) · 4.11 KB

set-transaction.md

File metadata and controls

114 lines (88 loc) · 4.11 KB
Raw
title summary toc
SET TRANSACTION
The SET TRANSACTION statement sets the transaction isolation level and/or priority for the current session or for an individual transaction.
false

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.

{{site.data.alerts.callout_info}}You can also set the session's default isolation level.{{site.data.alerts.end}}

Synopsis

{% include sql/{{ page.version.version }}/diagrams/set_transaction.html %}

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 By default, transactions in CockroachDB implement the strongest ANSI isolation level: SERIALIZABLE. At this isolation level, transactions will never result in anomalies. The SNAPSHOT isolation level is still supported as well for backwards compatibility, but you should avoid using it. It provides little benefit in terms of performance and can result in inconsistent state under certain complex workloads. For more information, see Transactions: Isolation Levels.

New in v2.0: Alias: transaction_isolation

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.

New in v2.0: Alias: transaction_priority

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.

{% include copy-clipboard.html %}

> BEGIN;

{% include copy-clipboard.html %}

> SET TRANSACTION ISOLATION LEVEL SNAPSHOT, PRIORITY HIGH;

{{site.data.alerts.callout_success}}You can also set both transaction options as a space-separated list, e.g., SET TRANSACTION ISOLATION LEVEL SNAPSHOT PRIORITY HIGH.{{site.data.alerts.end}}

{% include copy-clipboard.html %}

> SAVEPOINT cockroach_restart;

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

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

{% include copy-clipboard.html %}

> RELEASE SAVEPOINT cockroach_restart;

{% include copy-clipboard.html %}

> COMMIT;

{{site.data.alerts.callout_danger}}This example assumes you're using client-side intervention to handle transaction retries.{{site.data.alerts.end}}

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 DEFAULT_TRANSACTION_ISOLATION;
+-------------------------------+
| default_transaction_isolation |
+-------------------------------+
| SERIALIZABLE                  |
+-------------------------------+
(1 row)

> SET DEFAULT_TRANSACTION_ISOLATION TO SNAPSHOT;
SET

> SHOW DEFAULT_TRANSACTION_ISOLATION;
+-------------------------------+
| default_transaction_isolation |
+-------------------------------+
| SNAPSHOT                      |
+-------------------------------+
(1 row)

See Also