Chaos

Chaos
How it works
How to use
- Further Resources
Terms of Use
Building and Running
Workloads

Chaos

Chaos is a small application for comparing the semantics of read-committed (RC) and serializable (1SR) isolation levels in CockroachDB, PostgreSQL, MySQL and Oracle in regards to read/write transaction anomalies or phenomenas.

Specifically by observing the application impact on:

Number of retries and time spent in retrying
- Frequency of concurrency errors and transient SQL exceptions (code 40001 or 40P01)
Performance when using:
- Weaker vs stronger isolation
- Pessimistic locking using FOR UPDATE or FOR SHARE
- Optimistic locking using compare-and-set (CAS)
Correctness when exposed to phenomenas:
- P2 non-repeatable / fuzzy read
- P3 phantom read
- P4 lost update
- A5A read skew
- A5B write skew

Isolation levels are characterised by the phenomenas they either allow or prevent. The SQL standard covers four isolation levels and three read phenomenas:

Isolation levels:

Read Uncommitted
Read Committed
Repeatable Read
Serializable

Phenomenas:

Dirty Read
Non-repeatable Read
Phantom Read

Subsequent research in A Critique of ANSI SQL Isolation Levels (Berenson et al, 1995), also formalized in Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions (Adya, 1999)
has identified additional isolation levels (namely SNAPSHOT in MVCC databases) and phenomenas with pretty scientific acronyms.

The moral of the story is that you need to be aware of how your application invariants are exposed to these different anomalies in weaker isolation levels. That is where this project comes in, to select a few of the common ones and showcase how they manifest in a simple use case and what counter-measures can be put in to avoid accidentally breaching business rules.

How it works

Transaction anomalies are visualized by producing pathological workload contention by concurrently updating an overlapping set of keys in a conflicting order and also by using aggregate functions in queries. It follows the typical read-modify-write cycle which is a magnet for contention in modern business apps.

The purpose is to observe the application impact on correctness and performance, while running workloads and playing around with isolation levels and different locking strategies.

The workloads are simple and centered around a single account table simulating bank accounts (more below). In the end of each run, consistency checks are performed to verify that the accounts remain in a expected correct final state and that there's not a breach of any invariants (like reading phantom values).

Inconsistent outcomes will be the result when running in weaker isolation levels than 1SR and not using any locking strategy. The tradeoff with weaker isolation is that you risk incorrect results unless taking certain measures application-side like using optimistic or pessimistic locking. Locking can work in most cases when you read a value with the intent to write based on that read. Incorrect results can mean data loss, wrong numbers in financial reports and other hard to find errors.

How to use

This project visualizes five common anomalies using simple, fixed type of workloads (one for each anomaly type). You can reproduce all anomalies manually also by using SQL shells in lock steps without any high concurrency. In the end its about the causal order of concurrent transactions, not at which rate they are executed.

Further Resources

Terms of Use

This tool is not supported by Cockroach Labs. Use of this tool is entirely at your own risk and Cockroach Labs makes no guarantees or warranties about its operation.

See MIT for terms and conditions.

Building and Running

Prerequisites

Building

Java 21+ JDK
- https://openjdk.org/projects/jdk/21/
- https://www.oracle.com/java/technologies/downloads/#java21
Maven 3+ (optional, embedded wrapper available)
- https://maven.apache.org/

Running

Java 21+ JRE
CockroachDB 24.1 or later
- https://www.cockroachlabs.com/docs/releases/
PostgreSQL 9+ (optional)
Oracle 23ai Free (optional)
MySQL 9.x (optional)

Install the JDK

Ubuntu:

sudo apt-get install openjdk-21-jdk

MacOS (using sdkman):

curl -s "https://get.sdkman.io" | bash
sdk list java
sdk install java 21.0 (pick version)

Database Setup

CockroachDB Setup

See https://www.cockroachlabs.com/docs/v24.2/start-a-local-cluster for setup instructions.

Ensure that you are using CockroachDB 23.2 or later and enable RC with:

cockroach sql --insecure -e "SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = 'true'"

Create the database:

cockroach sql --insecure -e "create database chaos"

PostgreSQL Setup

Install PSQL on MacOS using brew:

brew install postgresql

Starting:

brew services start postgresql@14

Create the database:

psql postgres
$ create database chaos;
$ quit

Stopping:

brew services stop postgresql@14

MySQL Setup

Install MySQL on MacOS using brew:

brew install mysql

Starting:

brew services start colima
brew services start mysql

Create the database:

mysql -u root
$ create database chaos;
$ quit

Stopping:

brew services stop mysql

Oracle Setup

Install Oracle 23 Free on MacOS using brew:

brew install colima
brew install docker
colima start --arch x86_64 --memory 4

Starting:

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=root gvenzl/oracle-free

Stopping:

docker ps -al
docker stop $(docker ps -a -q)
docker rm $(docker ps -a -q)

Building

Maven is used to build the project, bootstrapped by Tanuki Maven wrapper.

./mvnw clean install

Running

This command will start the shell from where you can start the different workloads:

java -jar target/chaos.jar
$ help

Prints help:

java -jar target/chaos.jar --help

Workloads

If you observe any anomalies in serializable isolation, please report an issue here.

Hint: If you don't observe any anomalies in read_committed isolation without locks, try to decrease the account --selection and/or increase --iterations.

Table schema used (CockroachDB version):

create table if not exists account
(
    id             int            not null default unordered_unique_rowid(),
    type           varchar(32)    not null,
    version        int            not null default 0,
    balance        numeric(19, 2) not null,
    name           varchar(128)   null,
    allow_negative integer        not null default 0,

    primary key (id, type)
);

Non-Repeatable Read (P2)

Also called a fuzzy read and described as:

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

Fuzzy reads are allowed in RC but prohibited in 1SR.

delete from account where 1=1;
insert into account (id, type, balance)
values (1, 'checking', 100.00);

begin; -- t1
set transaction isolation level read committed; -- t1
begin; -- t2
set transaction isolation level read committed; -- t2
SELECT balance FROM account WHERE id = 1 AND type = 'checking'; -- t1 100
UPDATE account SET balance = 50 WHERE id = 1 and type='checking'; -- t2
SELECT balance FROM account WHERE id = 1 AND type = 'checking'; -- t1 100
commit; -- t2
SELECT balance FROM account WHERE id = 1 AND type = 'checking'; -- t1 (if 50 then its a P2 violation !!)
commit; -- t1

This command should result in P2 anomalies:

p2 --isolationLevel read_committed

These commands will result in a correct outcome due to locking or 1SR:

p2 --isolationLevel read_committed --lockType for_share
p2 --isolationLevel read_committed --lockType for_update
p2 --isolationLevel serializable

Phantom Read (P3)

A phantom read means the predicate is unstable when repeating a read, also described as:

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Phantom reads are allowed in RC but prohibited in 1SR.

insert into account (id, type, balance)
values (1, 'a', 100.00),
       (1, 'b', 100.00),
       (1, 'c', 100.00);
begin; -- t1
set transaction isolation level read committed; -- t1
begin; -- t2
set transaction isolation level read committed; -- t2
select * from account where id = 1; -- t1 (3 rows)
select * from account where id = 1; -- t1 (3 rows)
insert into account (id, type, balance) values (1, 'd', 100.00); -- t2
commit; -- t2
select * from account where id = 1; -- t1 (must be 3 rows but if 4 then its a p3 violation !!)
commit; -- t1

These commands should result in P3 anomalies. Notice that using locks won't help:

p3 --isolationLevel read_committed
p3 --isolationLevel read_committed --lockType for_update
p3 --isolationLevel read_committed --lockType for_share

This command will result in a correct outcome:

p3 --isolationLevel serializable

Lost Update (P4)

A lost update is described as:

A lost update happens when one transaction overwrites the changes made by another transaction.

This anomaly is common in systems where concurrent modifications are possible, including financial systems, e-commerce systems or any scenario involving shared resources. In particular for a typical read-modify-write scenario where data is being read, modified by some business rule and changes are being written back. ORM frameworks usually follow this pattern more prevalently than other data access strategies.

To visualize P4, this workload concurrently executes the following statements using explicit transactions:

BEGIN; -- T1 .. TN 
SELECT balance from account where id=1;
SELECT balance from account where id=2;
UPDATE account set balance=? where id = 1; -- balance @1 + 5
UPDATE account set balance=? where id = 2; -- balance @2 - 5
COMMIT;

The interleaving of the concurrent transactions will at some point result in a conflicting order of operations causing the database to rollback transactions with a transient, retryable errors (40001 code) when running under 1SR.

When running under RC however, the result in a P4 lost update (expected) instead unless some locking strategy is applied (with --lockType). In other words, this type of workload is completely unsafe in RC unless using either:

Optimistic "locks" through a CAS operation (version increments)
Pessimistic SELECT .. FOR UPDATE locks at read time

Assume the following schema and initial data:

create table account (
  id       int            not null,
  balance  numeric(19, 2) not null,
  primary key (id)
);

-- Run between each test
delete from account where 1=1;
insert into account (id, balance) values (1, 100.00), (2,  200.00);

CockroachDB "serializable" prevents Lost Update (P4):

begin; set transaction isolation level serializable; -- T1
begin; set transaction isolation level serializable; -- T2
select * from account where id = 1; -- T1
select * from account where id = 1; -- T2
update account set balance = 11 where id = 1; -- T1
update account set balance = 22 where id = 1; -- T2, BLOCKS on T1
commit; -- T1. T2 now prints out "ERROR: restart transaction: TransactionRetryWithProtoRefreshError: WriteTooOldError"
abort;  -- T2. There's nothing else we can do, this transaction has failed

CockroachDB "read committed" with SFU also prevents Lost Update (P4):

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from account where id = 1 FOR UPDATE; -- T1
select * from account where id = 1 FOR UPDATE; -- T2, BLOCKS
update account set balance = 11 where id = 1; -- T1
commit; -- T1. This unblocks T2, which reads T1's update (11)
update account set balance = 22 where id = 1; -- T2
commit; -- T2

CockroachDB "read committed" without SFU permits Lost Update (P4), which unless intentional, may cause a (ノಠ益ಠ)ノ彡┻━┻ reaction later:

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from account where id = 1; -- T1
select * from account where id = 1; -- T2
update account set balance = 11 where id = 1; -- T1
commit; -- T1
update account set balance = 22 where id = 1; -- T2, overwriting T1's update from 100 to 11 which is "lost" !
commit; -- T2

The level of contention can be adjusted by increasing the number of account tuples, number of concurrent executors, or by reducing the selection of account IDs involved in the interleaving.

This allows for creating a high number of accounts spanning many ranges (CockroachDB term) while still being able to cause contention.

This should result in P4 anomalies:

p4 --isolationLevel read_committed

These will result in a correct outcome:

p4 --isolationLevel read_committed --lockType for_update
p4 --isolationLevel serializable

Read Skew (A5a)

Read skew is that with two different queries, a transaction reads inconsistent data because between the 1st and 2nd queries, other transactions insert, update or delete data and commit.

This workload is similar to write skew where the account balances are read in separate statements and the sum is expected to remain constant. Under RC without locks, it's allowed to read values committed by other concurrent transactions and thereby observe deviations (this can be avoided with FOR SHARE locks). Under 1SR, this is prohibited and results in a transient rollback error.

Read skew example:

delete from account where 1=1;
insert into account (id, type, balance)
values (1, 'a', 100.00),
(2, 'b', 200.00);

begin; set transaction isolation level read committed; -- T1
begin; set transaction isolation level read committed; -- T2
select * from account where id = 1 and type = 'a'; -- T1. Shows 1 => 100
select * from account where id = 1 and type = 'a'; -- T2. Shows 1 => 100
select * from account where id = 2 and type = 'b'; -- T2. Shows 2 => 200
update account set balance = 12 where id = 1 and type = 'a'; -- T2
update account set balance = 18 where id = 2 and type = 'b'; -- T2
commit; -- T2
select * from account where id = 2 and type = 'b'; -- T1. Shows 2 => 18 which is A5a/read skew !!
commit; -- T1

This should result in A5B anomalies:

a5a --isolationLevel read_committed

These will result in a correct outcome:

a5a --isolationLevel read_committed --lockType compare_and_set
a5a --isolationLevel read_committed --lockType for_update
a5a --isolationLevel read_committed --lockType for_share
a5a --isolationLevel serializable

Write Skew (A5b)

Write skew is a phenomenon where two writes are allowed to the same column(s) in a table by two different writers (who have previously read the columns they are updating), resulting in the column having data that is a mix of the two transactions.

The subtle anomaly called write skew is prevented in 1SR but allowed in RC. It is also allowed in snapshot isolation (SI) that Oracle labels as serializable by historical reasons.

In this workload, accounts are organized in tuples where the same surrogate id is shared by a checking and credit account. The composite primary key is id,type. The business rule (invariant) is that the account balances can be negative or positive as long as the sum of both accounts is >= 0.

id	type	balance
1	checking	-5.00
1	credit	10.00
Σ	-	+5.00 (ok)

Write skew can happen if T1 and T2 reads the total balance (which is >0 ) and independently writes a new balance to different rows.

Preset:

insert into account (id, type, balance) values(1, 'checking', -5.00);
insert into account (id, type, balance) values(1, 'credit', 10.00);

Assume transaction T1 and T2:

T1	T2
begin;	begin;
set transaction isolation level read committed;	set transaction isolation level read committed;
	select sum(balance) from account where id=1; -- 5.00
select sum(balance) from account where id=1; -- 5.00
update account set balance=balance-5 where id=1 and type='credit'; -- ok sum is 0
	update account set balance=balance-5 where id=1 and type='checking'; -- ok sum is 0
commit; --ok
	commit; -- ok (not allowed in 1SR)

Both transactions are "correct" in isolation, but when put together the total sum is -5.00 since they were both allowed to commit, which is wrong.

In summary, this workload concurrently executes the following statements (pseduo-code):

BEGIN; 
SELECT sum(balance) total from account where id=<random from selection>; -- expect 2 rows
if (total - random_amount > 0) -- app rule check
    (either)
        UPDATE account set balance=balance-? where id = 1 and type='checking'; 
    (or)
        UPDATE account set balance=balance-? where id = 2 and type='credit'; 
endif
COMMIT;

This workload is therefore unsafe in RC unless using optimistic "locking" through a CAS operation with version increments. Notice that pessimistic locks can't be used here due to the aggregate sum function.

This command should result in A5B anomalies:

a5b --isolationLevel read_committed

These commands will result in a correct outcome:

a5b --isolationLevel read_committed --lockType compare_and_set
a5b --isolationLevel serializable

This command should result in a correct outcome:

java -jar target/chaos.jar --isolation 1sr --selection 20 write_skew

"When in doubt, use serializable"

Name		Name	Last commit message	Last commit date
Latest commit History 6 Commits
.github/workflows		.github/workflows
.mvn/wrapper		.mvn/wrapper
config		config
src/main		src/main
.gitignore		.gitignore
LICENSE.txt		LICENSE.txt
README.md		README.md
logo.png		logo.png
mvnw		mvnw
pom.xml		pom.xml
run.sh		run.sh

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Repository files navigation

Chaos

How it works

How to use

Further Resources

Terms of Use

Building and Running

Prerequisites

Building

Running

Install the JDK

Database Setup

CockroachDB Setup

PostgreSQL Setup

MySQL Setup

Oracle Setup

Building

Running

Workloads

Non-Repeatable Read (P2)

Phantom Read (P3)

Lost Update (P4)

Read Skew (A5a)

Write Skew (A5b)

About

Uh oh!

Releases

Packages

Uh oh!

Languages

License

kai-niemi/chaos

Folders and files

Latest commit

History

Repository files navigation

Chaos

How it works

How to use

Further Resources

Terms of Use

Building and Running

Prerequisites

Building

Running

Install the JDK

Database Setup

CockroachDB Setup

PostgreSQL Setup

MySQL Setup

Oracle Setup

Building

Running

Workloads

Non-Repeatable Read (P2)

Phantom Read (P3)

Lost Update (P4)

Read Skew (A5a)

Write Skew (A5b)

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Languages

Packages