2.1 C
New York
Wednesday, February 1, 2023

How to Deploy Transaction Support on Cloudera Operational Database (COD)


What is Cloudera Operational Database (COD)

Cloudera Operational Database enables developers to quickly build future-proof applications that are architected to handle data evolution. It helps developers automate and simplify database management with capabilities like auto-scale, and is fully integrated with Cloudera Data Platform (CDP). For more information and to get started with COD, refer to our article Getting Started with Cloudera Data Platform Operational Database (COD).

Background

This blog is the second in the “Transaction Support in Cloudera Operational Database” blog series. 

How to use transactions on COD

To use transactions, you need to add the TRANSACTIONAL flag, “TRANSACTIONAL=true”, on a table-by-table basis when you create your table:

CREATE TABLE my_table (k BIGINT PRIMARY KEY, v VARCHAR) TRANSACTIONAL=true;

You can also alter an existing table to be transactional, but be careful because you cannot switch a transactional table back to being non-transactional.

ALTER TABLE my_other_table SET TRANSACTIONAL=true;

End-to-end example

To demonstrate the usage of transactions in COD, let us use an example of an “order entry” that happens at terminals in a warehouse. The warehouse has multiple items in the pipeline to be sold and customers place the orders continuously.

The following are the table structures and sample data used in this example.

Git link of the code sample:

https://github.com/cloudera/cod-examples/blob/main/phoenix-read-write/src/main/java/com/cloudera/cod/examples/sql/TransactionalClient.java

Item:

Create Syntax:

Sample data:

Customer:

Create Syntax:

Sample data:

Order:

Create Syntax:

Sample data:

OrderLineItem

Create Syntax:

Sample Data:

Let us now build a simple Java application to demonstrate the usage of the transactions and how COD handles the conflicts so that a consistent view of data exists in the database when concurrent clients try to update the same data sets at the same time with different transactions.

First, create tables using the above mentioned structure and populate the items and customer data.

Create tables:

Populate data:

Here is the simple code to create an order containing the order table entry, creating order line items and updating the quantity of items as a single transaction.

Now let us create multiple clients to simulate order creation from different terminals.

The following code creates multiple connections considering a client connection from different terminals.

You can use the above createOrder method to create order in the first terminal and commit the changes directly.

After the transaction is completed, the data exists in the item table.

Item:

Order:

Order line item:

How COD transaction detects data conflicts

The following example code snippets demonstrate how COD detects conflicts using the first commit, and throws an exception indicating that conflict is detected using the second commit.

The warehouse now contains three book items and an operator at the second terminal added the two items to the order, and did not complete the order. At the same time, another operator at the third terminal added one book item to an order and completed it. Now the available number of book items is one. So when the operator from the second terminal tries to complete the order, it fails because one book item in the order is already taken by the operator at the third terminal.

COD throws an exception while committing the second terminal transaction indicating the conflict because the second and third terminal are lying on the same item, and when the third terminal is committed the quantity is inconsistent with the second terminal.

Refer to the demo video for more information:

Summary:

In this blog post, we’ve demonstrated how to use transactions with an example that covers an end-to-end flow. For more details on transaction support on COD, see COD Transaction Support blog.

So, are you ready to try out COD transactions support?  Here’s your first step on creating a database using COD.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles