TRANSACTION

Synopsis

The transaction statement block makes changes to multiple rows in one or more tables in a distributed ACID transaction.

Syntax

Diagram

TRANSACTION - 图1

Grammar

  1. transaction_block ::= BEGIN TRANSACTION
  2. ( insert | update | delete ) ';'
  3. [ ( insert | update | delete ) ';' ...]
  4. END TRANSACTION ';'

Where

ANSI SQL syntax

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

  1. transaction_block ::= START TRANSACTION ';'
  2. ( insert | update | delete ) ';'
  3. [ ( insert | update | delete ) ';' ...]
  4. 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

  1. cqlsh:example> CREATE TABLE accounts (account_name TEXT,
  2. account_type TEXT,
  3. balance DOUBLE,
  4. PRIMARY KEY ((account_name), account_type))
  5. WITH transactions = { 'enabled' : true };

Insert some data

  1. cqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
  2. VALUES ('John', 'savings', 1000);
  3. cqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
  4. VALUES ('John', 'checking', 100);
  5. cqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
  6. VALUES ('Smith', 'savings', 2000);
  7. cqlsh:example> INSERT INTO accounts (account_name, account_type, balance)
  8. VALUES ('Smith', 'checking', 50);
  1. cqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;
  1. account_name | account_type | balance | writetime(balance)
  2. --------------+--------------+---------+--------------------
  3. John | checking | 100 | 1523313964356489
  4. John | savings | 1000 | 1523313964350449
  5. Smith | checking | 50 | 1523313964371579
  6. Smith | savings | 2000 | 1523313964363056

Update 2 rows with the same partition key

You can do this as shown below.

  1. cqlsh:example> BEGIN TRANSACTION
  2. UPDATE accounts SET balance = balance - 200 WHERE account_name = 'John' AND account_type = 'savings';
  3. UPDATE accounts SET balance = balance + 200 WHERE account_name = 'John' AND account_type = 'checking';
  4. END TRANSACTION;
  1. cqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;
  1. account_name | account_type | balance | writetime(balance)
  2. --------------+--------------+---------+--------------------
  3. John | checking | 300 | 1523313983201270
  4. John | savings | 800 | 1523313983201270
  5. Smith | checking | 50 | 1523313964371579
  6. Smith | savings | 2000 | 1523313964363056

Update 2 rows with the different partition keys

  1. cqlsh:example> BEGIN TRANSACTION
  2. UPDATE accounts SET balance = balance - 200 WHERE account_name = 'John' AND account_type = 'checking';
  3. UPDATE accounts SET balance = balance + 200 WHERE account_name = 'Smith' AND account_type = 'checking';
  4. END TRANSACTION;
  1. cqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts;
  1. account_name | account_type | balance | writetime(balance)
  2. --------------+--------------+---------+--------------------
  3. John | checking | 100 | 1523314002218558
  4. John | savings | 800 | 1523313983201270
  5. Smith | checking | 250 | 1523314002218558
  6. Smith | savings | 2000 | 1523313964363056

See also