Distributed ACID Transactions Beta

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

Distributed ACID transactions batch a multi-step, multi-table operation into a single, all-or-nothing operation. The intermediate states of the database between the steps in a transaction are not visible to other concurrent transactions or the end user. If the transaction encounters any failures that prevents it from completing successfully, none of the steps are applied to the database.

YugabyteDB is designed to support transactions at the following isolation levels:

  • Snapshot Isolation (currently supported)
  • Serializable (work in progress)

You can read more about transactions in our architecture docs.

If you haven’t installed YugabyteDB yet, do so first by following the Quick Start guide.

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

  1. $ ./bin/yb-ctl destroy

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ ./bin/yb-ctl create

2. Create a table for transactions

Connect to the cluster using cqlsh.

  1. $ ./bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]Use HELP for help.cqlsh>

Create a keyspace.

  1. cqlsh> CREATE KEYSPACE banking;

Create a table with the transactions property set to enabled.

  1. cqlsh> CREATE TABLE banking.accounts (  account_name varchar,  account_type varchar,  balance float,  PRIMARY KEY ((account_name), account_type)) with transactions = { 'enabled' : true };

You can verify that this table has transactions enabled on it by querying the

  1. cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
  1.  keyspace_name | table_name | transactions———————-+——————+——————————-       banking |   accounts | {'enabled': 'true'}(1 rows)

3. Insert sample data

Let us seed this table with some sample data.

  1. INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'savings', 1000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'checking', 100);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'savings', 2000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'checking', 50);

Here are the balances for John and Smith.

  1. cqlsh> select  from banking.accounts;
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     100         John |      savings |    1000        Smith |     checking |      50        Smith |      savings |    2000
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2050

4. Execute a transaction

Here are a couple of examples of executing transactions.

  • Let us say John transfers $200 from his savings account to his checking account. This has to be a transactional operation. This can be achieved as follows.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='savings';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='John' AND account_type='checking';END TRANSACTION;

If we now selected the value of John’s account, we should see the amounts reflected. The total balance should be the same $1100 as before.

  1. cqlsh> select  from banking.accounts where account_name='John';
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     300         John |      savings |     800
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100

Further, the checking and savings account balances for John should have been written at the same write timestamp.

  1. cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     300 |   1517898028890171         John |      savings |     800 |   1517898028890171

  • Now let us say John transfers the $200 from his checking account to Smith’s checking account. We can accomplish that with the following transaction.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='checking';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='Smith' AND account_type='checking';END TRANSACTION;

We can verify the transfer was made as we intended, and also verify that the time at which the two accounts were updated are identical by performing the following query.

  1. cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     100 |   1517898167629366         John |      savings |     800 |   1517898028890171        Smith |     checking |     250 |   1517898167629366        Smith |      savings |    2000 |   1517894361290020

The net balance for John should have decreased by $200 which that of Smith should have increased by $200.

  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-           900
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2250

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ ./bin/yb-ctl destroy

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

  1. $ ./bin/yb-ctl destroy

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ ./bin/yb-ctl create

2. Create a table for transactions

Connect to the cluster using cqlsh.

  1. $ ./bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]Use HELP for help.cqlsh>

Create a keyspace.

  1. cqlsh> CREATE KEYSPACE banking;

Create a table with the transactions property set to enabled.

  1. cqlsh> CREATE TABLE banking.accounts (  account_name varchar,  account_type varchar,  balance float,  PRIMARY KEY ((account_name), account_type)) with transactions = { 'enabled' : true };

You can verify that this table has transactions enabled on it by querying the

  1. cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
  1.  keyspace_name | table_name | transactions———————-+——————+——————————-       banking |   accounts | {'enabled': 'true'}(1 rows)

3. Insert sample data

Let us seed this table with some sample data.

  1. INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'savings', 1000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'checking', 100);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'savings', 2000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'checking', 50);

Here are the balances for John and Smith.

  1. cqlsh> select * from banking.accounts;
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     100         John |      savings |    1000        Smith |     checking |      50        Smith |      savings |    2000
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2050

4. Execute a transaction

Here are a couple of examples of executing transactions.

  • Let us say John transfers $200 from his savings account to his checking account. This has to be a transactional operation. This can be achieved as follows.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='savings';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='John' AND account_type='checking';END TRANSACTION;

If we now selected the value of John’s account, we should see the amounts reflected. The total balance should be the same $1100 as before.

  1. cqlsh> select  from banking.accounts where account_name='John';
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     300         John |      savings |     800
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100

Further, the checking and savings account balances for John should have been written at the same write timestamp.

  1. cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     300 |   1517898028890171         John |      savings |     800 |   1517898028890171

  • Now let us say John transfers the $200 from his checking account to Smith’s checking account. We can accomplish that with the following transaction.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='checking';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='Smith' AND account_type='checking';END TRANSACTION;

We can verify the transfer was made as we intended, and also verify that the time at which the two accounts were updated are identical by performing the following query.

  1. cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     100 |   1517898167629366         John |      savings |     800 |   1517898028890171        Smith |     checking |     250 |   1517898167629366        Smith |      savings |    2000 |   1517894361290020

The net balance for John should have decreased by $200 which that of Smith should have increased by $200.

  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-           900
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2250

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ ./bin/yb-ctl destroy

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

  1. $ ./yb-docker-ctl destroy

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ ./yb-docker-ctl create

2. Create a table for transactions

Connect to cqlsh on node 1.

  1. $ docker exec -it yb-tserver-n1 /home/yugabyte/bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]Use HELP for help.cqlsh>

Create a keyspace.

  1. cqlsh> CREATE KEYSPACE banking;

Create a table with the transactions property set to enabled.

  1. cqlsh> CREATE TABLE banking.accounts (  account_name varchar,  account_type varchar,  balance float,  PRIMARY KEY ((account_name), account_type)) with transactions = { 'enabled' : true };

You can verify that this table has transactions enabled on it by querying the

  1. cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
  1.  keyspace_name | table_name | transactions———————-+——————+——————————-       banking |   accounts | {'enabled': 'true'}(1 rows)

3. Insert sample data

Let us seed this table with some sample data.

  1. INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'savings', 1000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'checking', 100);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'savings', 2000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'checking', 50);

Here are the balances for John and Smith.

  1. cqlsh> select  from banking.accounts;
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     100         John |      savings |    1000        Smith |     checking |      50        Smith |      savings |    2000
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2050

4. Execute a transaction

Here are a couple of examples of executing transactions.

  • Let us say John transfers $200 from his savings account to his checking account. This has to be a transactional operation. This can be achieved as follows.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='savings';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='John' AND account_type='checking';END TRANSACTION;

If we now selected the value of John’s account, we should see the amounts reflected. The total balance should be the same $1100 as before.

  1. cqlsh> select * from banking.accounts where account_name='John';
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     300         John |      savings |     800
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100

Further, the checking and savings account balances for John should have been written at the same write timestamp.

  1. cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     300 |   1517898028890171         John |      savings |     800 |   1517898028890171

  • Now let us say John transfers the $200 from his checking account to Smith’s checking account. We can accomplish that with the following transaction.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='checking';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='Smith' AND account_type='checking';END TRANSACTION;

We can verify the transfer was made as we intended, and also verify that the time at which the two accounts were updated are identical by performing the following query.

  1. cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     100 |   1517898167629366         John |      savings |     800 |   1517898028890171        Smith |     checking |     250 |   1517898167629366        Smith |      savings |    2000 |   1517894361290020

The net balance for John should have decreased by $200 which that of Smith should have increased by $200.

  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-           900
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2250

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ ./yb-docker-ctl destroy

1. Setup - create universe and table

If you have a previously running local universe, destroy it using the following.

  1. $ kubectl delete -f yugabyte-statefulset.yaml

Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.

  1. $ kubectl apply -f yugabyte-statefulset.yaml

Check the Kubernetes dashboard to see the 3 yb-tserver and 3 yb-master pods representing the 3 nodes of the cluster.

  1. $ minikube dashboard

Kubernetes Dashboard

2. Create a table for transactions

Connect to cqlsh on node 1.

  1. $ kubectl exec -it yb-tserver-0 /home/yugabyte/bin/cqlsh
  1. Connected to local cluster at 127.0.0.1:9042.[cqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]Use HELP for help.cqlsh>

Create a keyspace.

  1. cqlsh> CREATE KEYSPACE banking;

Create a table with the transactions property set to enabled.

  1. cqlsh> CREATE TABLE banking.accounts (  account_name varchar,  account_type varchar,  balance float,  PRIMARY KEY ((account_name), account_type)) with transactions = { 'enabled' : true };

You can verify that this table has transactions enabled on it by querying the

  1. cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
  1.  keyspace_name | table_name | transactions———————-+——————+——————————-       banking |   accounts | {'enabled': 'true'}(1 rows)

3. Insert sample data

Let us seed this table with some sample data.

  1. INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'savings', 1000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('John', 'checking', 100);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'savings', 2000);INSERT INTO banking.accounts (account_name, account_type, balance) VALUES ('Smith', 'checking', 50);

Here are the balances for John and Smith.

  1. cqlsh> select * from banking.accounts;
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     100         John |      savings |    1000        Smith |     checking |      50        Smith |      savings |    2000
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
  1.  johns_balance———————-          1100
  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
  1.  smiths_balance————————           2050

4. Execute a transaction

Here are a couple of examples of executing transactions.

  • Let us say John transfers $200 from his savings account to his checking account. This has to be a transactional operation. This can be achieved as follows.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='savings';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='John' AND account_type='checking';END TRANSACTION;

If we now selected the value of John’s account, we should see the amounts reflected. The total balance should be the same $1100 as before.

  1. cqlsh> select * from banking.accounts where account_name='John';
  1.  account_name | account_type | balance———————+———————+————-         John |     checking |     300         John |      savings |     800
  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';

johns_balance

  1.       1100

Further, the checking and savings account balances for John should have been written at the same write timestamp.

  1. cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     300 |   1517898028890171         John |      savings |     800 |   1517898028890171

  • Now let us say John transfers the $200 from his checking account to Smith’s checking account. We can accomplish that with the following transaction.

    1. BEGIN TRANSACTIONUPDATE banking.accounts SET balance = balance - 200 WHERE account_name='John' AND account_type='checking';UPDATE banking.accounts SET balance = balance + 200 WHERE account_name='Smith' AND account_type='checking';END TRANSACTION;

We can verify the transfer was made as we intended, and also verify that the time at which the two accounts were updated are identical by performing the following query.

  1. cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
  1.  account_name | account_type | balance | writetime(balance)———————+———————+————-+——————————         John |     checking |     100 |   1517898167629366         John |      savings |     800 |   1517898028890171        Smith |     checking |     250 |   1517898167629366        Smith |      savings |    2000 |   1517894361290020

The net balance for John should have decreased by $200 which that of Smith should have increased by $200.

  1. cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';

johns_balance

  1.        900

  1. cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';

smiths_balance

  1.        2250

5. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

  1. $ kubectl delete -f yugabyte-statefulset.yaml

  1.                 </code></code></code>