TRANSACTION
Attention
This page documents an earlier version. Go to the latest (v2.3) version.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
, orDELETE
statements contains anIF
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
.