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