Distributed transactions

The best way to understand distributed transactions in YugabyteDB is through examples.

To learn about how YugabyteDB handles failures during transactions, see High availability of transactions.

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

Create a table

Create a keyspace, as follows:

ycqlsh> CREATE KEYSPACE banking;

The YCQL table should be created with the transactions property enabled. The statement should be similar to the following:

ycqlsh> 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 by running the following query:

ycqlsh> 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)

Insert sample data

Populate the table with sample data, as follows:

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);

Execute the following statement to retrieve the balances for John and Smith:

ycqlsh> 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, as follows:

ycqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
 johns_balance
---------------
          1100

Check Smith's balance, as follows:

ycqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
 smiths_balance
----------------
           2050

Execute a transaction

Suppose John transfers $200 from his savings account to his checking account. This has to be a transactional operation, as per the following:

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;

Execute the following statement to select the value of John's account:

ycqlsh> select * from banking.accounts where account_name='John';
 account_name | account_type | balance
--------------+--------------+---------
         John |     checking |     300
         John |      savings |     800

Execute the following statement to check John's balance:

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

The following output demonstrates that the total balance is the same $1100 as before:

 johns_balance
---------------
          1100

Further, the checking and savings account balances for John should have been written at the same write timestamp, as per the following:

ycqlsh> 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 suppose John transfers the $200 from his checking account to Smith's checking account. Run 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;

Verify

Execute the following query to verify that the transfer was made as intended and that the time at which the two accounts were updated is identical:

ycqlsh> 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

Execute the following query to verify that the net balance for John has decreased by $200 which that of Smith has increased by $200:

ycqlsh> SELECT SUM(balance) as Johns_balance FROM banking.accounts WHERE account_name='John';
 johns_balance
---------------
           900

Check Smith's balance, as follows:

ycqlsh> SELECT SUM(balance) as smiths_balance FROM banking.accounts WHERE account_name='Smith';
 smiths_balance
----------------
           2250