TRANSACTION
This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.
Synopsis
Use the TRANSACTION statement block to make changes to multiple rows in one or more tables in a distributed ACID transaction.
Syntax
Diagram
Grammar
transaction_block ::= BEGIN TRANSACTION
( insert | update | delete ) ';'
[ ( insert | update | delete ) ';' ...]
END TRANSACTION ';'
Where insert
, update
, and delete
are INSERT, UPDATE, and DELETE statements.
- When using
BEGIN TRANSACTION
, you don't use a semicolon. End the transaction block withEND TRANSACTION ;
(with a semicolon). - There is no
COMMIT
for transactions started usingBEGIN
.
SQL syntax
YCQL also supports SQL START TRANSACTION
and COMMIT
statements.
transaction_block ::= START TRANSACTION ';'
( insert | update | delete ) ';'
[ ( insert | update | delete ) ';' ...]
COMMIT ';'
- When using
START TRANSACTION
, you must use a semicolon. End the transaction block withCOMMIT ;
. - You can't use
END TRANSACTION
for transactions started usingSTART
.
Semantics
- An error is raised if transactions are not enabled in any of the tables inserted, updated, or deleted.
- Currently, an error is raised if any of the
INSERT
,UPDATE
, orDELETE
statements contains anIF
clause. - If transactions are enabled for a table, its indexes must have them enabled as well, and vice versa.
- There is no explicit rollback. To rollback a transaction, abort, or interrupt the client session.
- DDLs are always executed outside of a transaction block, and like DMLs outside a transaction block, are committed immediately.
- Inside a transaction block only insert, update, and delete statements are allowed. Select statements are not allowed.
- The insert, update, and delete statements inside a transaction block cannot have any if_expression.
Examples
Create a table with transactions enabled
ycqlsh:example> CREATE TABLE accounts (account_name TEXT,
account_type TEXT,
balance DOUBLE,
PRIMARY KEY ((account_name), account_type))
WITH transactions = { 'enabled' : true };
Insert some data
ycqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
VALUES ('John', 'savings', 1000);
ycqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
VALUES ('John', 'checking', 100);
ycqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
VALUES ('Smith', 'savings', 2000);
ycqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
VALUES ('Smith', 'checking', 50);
ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;
account_name | account_type | balance | writetime(balance)
--------------+--------------+---------+--------------------
John | checking | 100 | 1523313964356489
John | savings | 1000 | 1523313964350449
Smith | checking | 50 | 1523313964371579
Smith | savings | 2000 | 1523313964363056
Update 2 rows with the same partition key
You can do this as follows:
ycqlsh:example> BEGIN TRANSACTION
UPDATE accounts SET balance = balance - 200 WHERE account_name = 'John' AND account_type = 'savings';
UPDATE accounts SET balance = balance + 200 WHERE account_name = 'John' AND account_type = 'checking';
END TRANSACTION;
ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;
account_name | account_type | balance | writetime(balance)
--------------+--------------+---------+--------------------
John | checking | 300 | 1523313983201270
John | savings | 800 | 1523313983201270
Smith | checking | 50 | 1523313964371579
Smith | savings | 2000 | 1523313964363056
Update 2 rows with the different partition keys
ycqlsh:example> BEGIN TRANSACTION
UPDATE accounts SET balance = balance - 200 WHERE account_name = 'John' AND account_type = 'checking';
UPDATE accounts SET balance = balance + 200 WHERE account_name = 'Smith' AND account_type = 'checking';
END TRANSACTION;
ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;
account_name | account_type | balance | writetime(balance)
--------------+--------------+---------+--------------------
John | checking | 100 | 1523314002218558
John | savings | 800 | 1523313983201270
Smith | checking | 250 | 1523314002218558
Smith | savings | 2000 | 1523313964363056
Note
BEGIN/END TRANSACTION
doesn't currently support RETURNS STATUS AS ROW
.