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.
$ ./bin/yb-ctl destroy
Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.
$ ./bin/yb-ctl create
2. Create a table for transactions
Connect to the cluster using cqlsh
.
$ ./bin/cqlsh
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.
cqlsh> CREATE KEYSPACE banking;
Create a table with the transactions
property set to enabled.
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
cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
keyspace_name | table_name | transactions———————-+——————+——————————- banking | accounts | {'enabled': 'true'}(1 rows)
3. Insert sample data
Let us seed this table with some sample data.
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.
cqlsh> select from banking.accounts;
account_name | account_type | balance———————+———————+————- John | checking | 100 John | savings | 1000 Smith | checking | 50 Smith | savings | 2000
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
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.
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.
cqlsh> select
from banking.accounts where account_name='John';
account_name | account_type | balance———————+———————+————- John | checking | 300 John | savings | 800
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
Further, the checking and savings account balances for John should have been written at the same write timestamp.
cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
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.
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.
cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
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.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 900
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
smiths_balance———————— 2250
5. Clean up (optional)
Optionally, you can shutdown the local cluster created in Step 1.
$ ./bin/yb-ctl destroy
1. Setup - create universe
If you have a previously running local universe, destroy it using the following.
$ ./bin/yb-ctl destroy
Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.
$ ./bin/yb-ctl create
2. Create a table for transactions
Connect to the cluster using cqlsh
.
$ ./bin/cqlsh
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.
cqlsh> CREATE KEYSPACE banking;
Create a table with the transactions
property set to enabled.
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
cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
keyspace_name | table_name | transactions———————-+——————+——————————- banking | accounts | {'enabled': 'true'}(1 rows)
3. Insert sample data
Let us seed this table with some sample data.
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.
cqlsh> select * from banking.accounts;
account_name | account_type | balance———————+———————+————- John | checking | 100 John | savings | 1000 Smith | checking | 50 Smith | savings | 2000
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
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.
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.
cqlsh> select from banking.accounts where account_name='John';
account_name | account_type | balance———————+———————+————- John | checking | 300 John | savings | 800
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
Further, the checking and savings account balances for John should have been written at the same write timestamp.
cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
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.
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.
cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
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.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 900
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
smiths_balance———————— 2250
5. Clean up (optional)
Optionally, you can shutdown the local cluster created in Step 1.
$ ./bin/yb-ctl destroy
1. Setup - create universe
If you have a previously running local universe, destroy it using the following.
$ ./yb-docker-ctl destroy
Start a new local cluster - by default, this will create a 3 node universe with a replication factor of 3.
$ ./yb-docker-ctl create
2. Create a table for transactions
Connect to cqlsh on node 1.
$ docker exec -it yb-tserver-n1 /home/yugabyte/bin/cqlsh
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.
cqlsh> CREATE KEYSPACE banking;
Create a table with the transactions
property set to enabled.
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
cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
keyspace_name | table_name | transactions———————-+——————+——————————- banking | accounts | {'enabled': 'true'}(1 rows)
3. Insert sample data
Let us seed this table with some sample data.
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.
cqlsh> select
from banking.accounts;
account_name | account_type | balance———————+———————+————- John | checking | 100 John | savings | 1000 Smith | checking | 50 Smith | savings | 2000
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
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.
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.
cqlsh> select * from banking.accounts where account_name='John';
account_name | account_type | balance———————+———————+————- John | checking | 300 John | savings | 800
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
Further, the checking and savings account balances for John should have been written at the same write timestamp.
cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
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.
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.
cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
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.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 900
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
smiths_balance———————— 2250
5. Clean up (optional)
Optionally, you can shutdown the local cluster created in Step 1.
$ ./yb-docker-ctl destroy
1. Setup - create universe and table
If you have a previously running local universe, destroy it using the following.
$ 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.
$ 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.
$ minikube dashboard
2. Create a table for transactions
Connect to cqlsh on node 1.
$ kubectl exec -it yb-tserver-0 /home/yugabyte/bin/cqlsh
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.
cqlsh> CREATE KEYSPACE banking;
Create a table with the transactions
property set to enabled.
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
cqlsh> select keyspace_name, table_name, transactions from system_schema.tableswhere keyspace_name='banking' AND table_name = 'accounts';
keyspace_name | table_name | transactions———————-+——————+——————————- banking | accounts | {'enabled': 'true'}(1 rows)
3. Insert sample data
Let us seed this table with some sample data.
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.
cqlsh> select * from banking.accounts;
account_name | account_type | balance———————+———————+————- John | checking | 100 John | savings | 1000 Smith | checking | 50 Smith | savings | 2000
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance———————- 1100
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
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.
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.
cqlsh> select * from banking.accounts where account_name='John';
account_name | account_type | balance———————+———————+————- John | checking | 300 John | savings | 800
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance
1100
Further, the checking and savings account balances for John should have been written at the same write timestamp.
cqlsh> select account_name, account_type, balance, writetime(balance)from banking.accounts where account_name='John';
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.
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.
cqlsh> select account_name, account_type, balance, writetime(balance) from banking.accounts;
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.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance
900
cqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
smiths_balance
2250
5. Clean up (optional)
Optionally, you can shutdown the local cluster created in Step 1.
$ kubectl delete -f yugabyte-statefulset.yaml
</code></code></code>