YugaByte YugaByte
Enterprise
Open Source
Customers
Use Cases
Globally Distributed Applications Real-Time Streaming & Analytics Transactional NoSQL Planet-Scale SQL Distributed Transactional Key-Value DB
Resources
All Resources Docs Blog
Get Started
latest v1.0
  • Introduction
  • Quick Start
    • 1. Install YugaByte DB
    • 2. Create Local Cluster
    • 3. Test YCQL API
    • 4. Test YEDIS API
    • 5. Test YSQL API
    • 6. Run Sample Apps
  • Explore Core Features
    • Cloud Native
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. Observability
      • 4. Orchestration Readiness
    • Transactional
      • 1. ACID Transactions
      • 2. Secondary Indexes
      • 3. JSON Documents
    • High Performance
      • Tunable Reads
    • Planet Scale
      • 1. Global Distribution
      • 2. Auto Sharding
      • 3. Auto Rebalancing
    • PostgreSQL (Beta)
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. JOINs
      • 4. Aggregations
      • 5. Expressions
      • 6. Views
  • Develop
    • Tools
      • cqlsh
      • TablePlus
    • Client Drivers
      • C/C++
      • C#
      • Go
      • Java
      • NodeJS
      • Python
    • Learn App Dev
      • 1. SQL vs NoSQL
      • 2. Data Modeling
      • 3. Data Types
      • 4. ACID Transactions
      • 5. Aggregations
      • 6. Batch Operations
    • Ecosystem Integrations
      • Apache Kafka
      • Apache Spark
      • JanusGraph
      • KairosDB
      • Presto
      • Metabase
    • Real World Examples
      • E-Commerce App
      • IoT Fleet Management
      • Retail Analytics
  • Deploy
    • Checklist
    • Manual Deployment
      • 1. System Configuration
      • 2. Install Software
      • 3. Start YB-Masters
      • 4. Start YB-TServers
      • 5. Verify Deployment
    • Kubernetes
      • Helm Chart
      • Local SSD
    • Docker Swarm
    • Public Clouds
      • Amazon Web Services
      • Google Cloud Platform
      • Microsoft Azure
    • Pivotal Cloud Foundry
    • Enterprise Edition
      • 1. Initial Setup
      • 2. Install Admin Console
      • 3. Configure Admin Console
      • 4. Configure Cloud Providers
  • Secure
    • Security Checklist
    • Authentication
    • Authorization
      • 1. RBAC Model
      • 2. Create Roles
      • 3. Grant Permissions
    • TLS Encryption
      • 1. Prepare Nodes
      • 2. Server-server encryption
      • 3. Client-server encryption
      • 4. Connect to cluster
  • Manage
    • Backup and Restore
      • Backing Up Data
      • Restoring Data
    • Data Migration
      • Bulk Import
      • Bulk Export
    • Change Cluster Config
    • Upgrade Deployment
    • Diagnostics Reporting
    • Enterprise Edition
      • Create Universe - Multi-Zone
      • Create Universe - Multi-Region
      • Edit Universe
      • Edit Config Flags
      • Health Checking and Alerts
      • Node Status & Actions
      • Read Replicas
      • Backup & Restore
      • Upgrade Universe
      • Delete Universe
  • Troubleshoot
    • Troubleshooting Overview
    • Cluster Level Issues
      • YCQL Connection Issues
      • YEDIS Connection Issues
    • Node Level Issues
      • Check Processes
      • Inspect Logs
      • System Stats
    • Enterprise Edition
      • Troubleshoot Universes
  • Architecture
    • Basic Concepts
      • Single Node
      • Universe, Cluster, YB-TServer, YB-Master
      • Sharding
      • Replication
      • Persistence
      • Query Layer
      • Acknowledgements
    • Core Functions
      • Universe Creation
      • Table Creation
      • Write IO Path
      • Read IO Path
      • High Availability
    • Transactions
      • Isolation Levels
      • Single Row Transactions
      • Distributed Transactions
      • Transactional IO Path
  • Comparisons
    • Apache Cassandra
    • MongoDB
    • Redis In-Memory Store
    • FoundationDB
    • Amazon DynamoDB
    • Azure Cosmos DB
    • Google Cloud Spanner
    • Apache HBase
  • API Reference
    • YCQL
      • ALTER KEYSPACE
      • ALTER ROLE
      • ALTER TABLE
      • CREATE INDEX
      • CREATE KEYSPACE
      • CREATE ROLE
      • CREATE TABLE
      • CREATE TYPE
      • DROP INDEX
      • DROP KEYSPACE
      • DROP ROLE
      • DROP TABLE
      • DROP TYPE
      • GRANT PERMISSION
      • GRANT ROLE
      • REVOKE PERMISSION
      • REVOKE ROLE
      • USE
      • INSERT
      • SELECT
      • UPDATE
      • DELETE
      • TRANSACTION
      • TRUNCATE
      • Simple Value
      • Subscript
      • Function Call
      • Operator Call
      • BLOB
      • BOOLEAN
      • MAP, SET, LIST
      • FROZEN
      • INET
      • Integer & Counter
      • Non-Integer
      • TEXT
      • Date & Time Types
      • UUID & TIMEUUID
      • JSONB
      • Date & Time Functions
    • YEDIS
      • APPEND
      • AUTH
      • CONFIG
      • CREATEDB
      • DELETEDB
      • LISTDB
      • SELECT
      • DEL
      • ECHO
      • EXISTS
      • EXPIRE
      • EXPIREAT
      • FLUSHALL
      • FLUSHDB
      • GET
      • GETRANGE
      • GETSET
      • HDEL
      • HEXISTS
      • HGET
      • HGETALL
      • HINCRBY
      • HKEYS
      • HLEN
      • HMGET
      • HMSET
      • HSET
      • HSTRLEN
      • HVALS
      • INCR
      • INCRBY
      • KEYS
      • MONITOR
      • PEXPIRE
      • PEXPIREAT
      • PTTL
      • ROLE
      • SADD
      • SCARD
      • SET
      • SETRANGE
      • SISMEMBER
      • SMEMBERS
      • SREM
      • STRLEN
      • ZRANGE
      • TSADD
      • TSCARD
      • TSGET
      • TSLASTN
      • TSRANGEBYTIME
      • TSREM
      • TSREVRANGEBYTIME
      • TTL
      • ZADD
      • ZCARD
      • ZRANGEBYSCORE
      • ZREM
      • ZREVRANGE
      • PUBSUB
      • PUBLISH
      • SUBSCRIBE
      • UNSUBSCRIBE
      • PSUBSCRIBE
      • PUNSUBSCRIBE
    • YSQL (Beta)
      • DDL Statements
        • CREATE DATABASE
        • CREATE TABLE
        • CREATE VIEW
        • DROP DATABASE
        • DROP TABLE
      • DML Statements
        • INSERT
        • SELECT
      • Datatypes
        • FLOAT
        • INTEGER
        • TEXT
      • Transactions
      • Roles and Permissions
      • Prepared Statements
      • Explain Statement
  • Admin Reference
    • yb-ctl
    • yb-docker-ctl
    • docker-compose
    • yb-master
    • yb-tserver
  • FAQs
    • Product
    • Architecture
    • Enterprise Edition
    • Cassandra Compatibility
> Explore Core Features > Transactional >

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.

  • macOS
  • Linux
  • Docker
  • Kubernetes

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

Kubernetes 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
Explore Cloud Native Features
Orchestration Readiness
Explore Core Features
Secondary Indexes
Have a technical question?
Slack StackOverflow
Found a bug or have an enchancement request?
GitHub
YugaByte
Contact Us
Copyright © 2017-2019 YugaByte, Inc. All rights reserved.