No privileges are required to initiate a transaction. However, privileges are required for each statement within a transaction.
In CockroachDB, the following are aliases for the
The following aliases also exist for isolation levels:
REPEATABLE READis an alias for
READ COMMITTEDare aliases for
For more information on isolation level aliases, see Comparison to ANSI SQL Isolation Levels.
||If you don’t want the transaction to run as
For more information, see Transactions: Isolation Levels.
||If you don’t want the transaction to run with
Transactions with higher priority are less likely to need to be retried.
For more information, see Transactions: Priorities.
Begin a Transaction
Use Default Settings
Without modifying the
BEGIN statement, the transaction uses
SERIALIZABLE isolation and
> BEGIN; > 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;
Change Isolation Level & Priority
You can set a transaction’s isolation level to
SNAPSHOT, as well as its priority to
> BEGIN 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;
You can also set a transaction’s isolation level and priority with
Begin a Transaction with Automatic Retries
CockroachDB will automatically retry all transactions that contain both
COMMIT in the same batch. Batching is controlled by your driver or client’s behavior, but means that CockroachDB receives all of the statements as a single unit, instead of a number of requests.
From the perspective of CockroachDB, a transaction sent as a batch looks like this:
> BEGIN; DELETE FROM customers WHERE id = 1; DELETE orders WHERE customer = 1; COMMIT;
However, in your application’s code, batched transactions are often just multiple statements sent at once. For example, in Go, this transaction would sent as a single batch (and automatically retried):
db.Exec( "BEGIN; DELETE FROM customers WHERE id = 1; DELETE orders WHERE customer = 1; COMMIT;" )
Issuing statements this way signals to CockroachDB that you do not need to change any of the statement’s values if the transaction doesn’t immediately succeed, so it can continually retry the transaction until it’s accepted.