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
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, orDELETEstatements contains anIFclause.
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.