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

ANSI SQL syntax

Alternatively, YugabyteDB supports ANSI SQL START TRANSACTION and COMMIT statements.

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

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.

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

BEGIN/END TRANSACTION doesn't currently support RETURNS STATUS AS ROW.

See also