cockroachdb/cockroach-transaction-demo

Demonstrate best practices in using transactions in a Spring Boot application

CockroachDB Transaction Best Practices

This repository contains a sample application that illustrates some best practices regarding transaction handling with CockroachDB.

The project included uses Spring Boot + Spring Data JPA.

BTW, if you’re new to CockroachDB and how it works, be sure to check out The Raft protocol, the mechanism Cockroach uses to seamlessly replicate and manage your data in a truly distributed fashion.

Database Schema + Sample Data

-- drop tables
DROP TABLE IF EXISTS items;

-- re-create tables
CREATE TABLE items (item_id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
                    name STRING,
                    description STRING,
                    price DECIMAL NOT NULL,
                    quantity INT DEFAULT 0);

INSERT INTO items (name, description, quantity, price) VALUES ('foo', 'fang', 200, 0.0);

JPA Structure

The following Java class shows the JPA structure used in the app:

@Entity
@Table(name = "items")
class Item {

	@Id
	@GeneratedValue(strategy = GenerationType.UUID) //
	private UUID itemId;

	private String name;

	private String description;

	private int quantity;

	private double price;

    ...
}

To see the rest of the project, feel free to clone this repository and open it up inside your favorite IDE.

Read Committed Transaction

The following diagram illustrates a simple scenario that involves read committed transactions, and what can happen when you have competing transactions.

See READ_COMMITTED.md

Serializable Transaction

The following diagram illustrates a simple scenario that involves serializable transactions, and how competing transactions can translate into an exception, which can be handled.

See SERIALIZABLE.md

SELECT FOR UPDATE scenario

SELECT FOR UPDATE provides the means to reduce retry errors from occurring.

See SELECT_FOR_UPDATE.adoc

To run this application

For this app to work, you must:

1. Type cockroach start-single-node --insecure to launch CockroachDB.

2. In another shell, type cockroach sql --insecure to creation a SQL session.

3. Type drop database if exists kwikshoppr cascade; create database kwikshoppr; to create the database for this app.

4. In the SQL session, type create database kwikshoppr; to create the database for this app.

5. In the SQL session, type SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = 'true'; to enable READ_COMMITTED transactions.

If you need to restart, type drop database if exists kwikshoppr cascade; in the SQL session and then create the database again.

You can NOT run the application without the database existing, because the name of the database (kwikshoppr) is embedded inside application.properties as part of the JDBC connection string.