Distributed ACID Transactions
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.
YugaByte DB 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 YugaByte DB 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.tables
where 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
Check John’s balance.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance
---------------
1100
Check Smith’s balance.
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 TRANSACTION
UPDATE 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
Check John’s balance.
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 TRANSACTION
UPDATE 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
Check Smith’s balance.
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.tables
where 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
Check John’s balance.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance
---------------
1100
Check Smith’s balance.
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 TRANSACTION
UPDATE 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
Check John’s balance.
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 TRANSACTION
UPDATE 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
Check Smith’s balance.
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.tables
where 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
Check John’s balance.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance
---------------
1100
Check Smith’s balance.
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 TRANSACTION
UPDATE 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
Check John’s balance.
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 TRANSACTION
UPDATE 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
Check Smith’s balance.
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.tables
where 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
Check John’s balance.
cqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
johns_balance
---------------
1100
Check Smith’s balance.
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 TRANSACTION
UPDATE 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
Check John’s balance.
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 TRANSACTION
UPDATE 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
Check Smith’s balance.
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