---
title: "TRANSACTION"
url: "https://docs.yugabyte.com/stable/api/ycql/dml_transaction/"
---

# TRANSACTION

Use the TRANSACTION statement block to make changes to multiple rows in one or more tables in a distributed ACID transaction.

## Synopsis

Use the TRANSACTION statement block to make changes to multiple rows in one or more tables in a [distributed ACID transaction](/stable/architecture/transactions/distributed-txns "distributed ACID transaction").

## Syntax

### Diagram

BEGINTRANSACTION[insert](/stable/api/ycql/grammar_diagrams#insert)[update](/stable/api/ycql/grammar_diagrams#update)[delete](/stable/api/ycql/grammar_diagrams#delete);ENDTRANSACTION;

### Grammar

```ebnf
transaction_block ::= BEGIN TRANSACTION
                          ( insert | update | delete ) ';'
                          [ ( insert | update | delete ) ';' ...]
                      END TRANSACTION ';'
```

Where `insert`, `update`, and `delete` are [INSERT](/stable/api/ycql/dml_insert "INSERT"), [UPDATE](/stable/api/ycql/dml_update/ "UPDATE"), and [DELETE](/stable/api/ycql/dml_delete/ "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](/stable/api/ycql/grammar_diagrams/#if-expression "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

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

## See also

- [INSERT](/stable/api/ycql/dml_insert "INSERT")
- [UPDATE](/stable/api/ycql/dml_update/ "UPDATE")
- [DELETE](/stable/api/ycql/dml_delete/ "DELETE")