TRANSACTION statement [YCQL]

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

ebnf

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 with END TRANSACTION ; (with a semicolon).
There is no COMMIT for transactions started using BEGIN.

SQL syntax

YCQL also supports SQL START TRANSACTION and COMMIT statements.

ebnf

transaction_block ::= START TRANSACTION ';'
                      ( insert | update | delete ) ';'
                      [ ( insert | update | delete ) ';' ...]
                      COMMIT ';'

When using START TRANSACTION, you must use a semicolon. End the transaction block with COMMIT ;.
You can't use END TRANSACTION for transactions started using START.

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, or DELETE statements contains an IF 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

sql

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

sql

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);

sql

ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;

output

 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:

sql

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;

sql

ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;

output

 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

sql

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;

sql

ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;

output

 account_name | account_type | balance | writetime(balance)
--------------+--------------+---------+--------------------
         John |     checking |     100 |   1523314002218558
         John |      savings |     800 |   1523313983201270
        Smith |     checking |     250 |   1523314002218558
        Smith |      savings |    2000 |   1523313964363056

{{< note title="Note" >}} BEGIN/END TRANSACTION doesn't currently support RETURNS STATUS AS ROW. {{< /note >}}

Synopsis

Syntax

Diagram

Grammar

SQL syntax

Semantics

Examples

Create a table with transactions enabled

Insert some data

Update 2 rows with the same partition key

Update 2 rows with the different partition keys

See also