TRANSACTION

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

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