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

BEGINTRANSACTIONinsertupdatedelete;ENDTRANSACTION;

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 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.

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

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.

See also