## Synopsis Use the TRANSACTION statement block to make changes to multiple rows in one or more tables in a [distributed ACID transaction](../../../architecture/transactions/distributed-txns). ## Syntax ### Diagram ### Grammar ```ebnf transaction_block ::= BEGIN TRANSACTION ( insert | update | delete ) ';' [ ( insert | update | delete ) ';' ...] END TRANSACTION ';' ``` Where `insert`, `update`, and `delete` are [INSERT](../dml_insert), [UPDATE](../dml_update/), and [DELETE](../dml_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. ```ebnf 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](../grammar_diagrams/#if-expression). ## Examples ### Create a table with transactions enabled ```sql 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 ```sql 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); ``` ```sql ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts; ``` ```output 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 follows: ```sql 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; ``` ```sql ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts; ``` ```output 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 ```sql 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; ``` ```sql ycqlsh:example> SELECT account_name, account_type, balance, writetime(balance) FROM accounts; ``` ```output account_name | account_type | balance | writetime(balance) --------------+--------------+---------+-------------------- John | checking | 100 | 1523314002218558 John | savings | 800 | 1523313983201270 Smith | checking | 250 | 1523314002218558 Smith | savings | 2000 | 1523313964363056 ``` {{< note title="Note" >}} `BEGIN/END TRANSACTION` doesn't currently support `RETURNS STATUS AS ROW`. {{< /note >}} ## See also - [`INSERT`](../dml_insert) - [`UPDATE`](../dml_update/) - [`DELETE`](../dml_delete/)