Hermitage: Testing transaction isolation levels

Hermitage is an attempt to nail down precisely what different database systems actually mean with their isolation levels. It's a suite of tests that simulates various concurrency issues — some common, some more obscure — and documents how different databases handle those situations.

…

The SQL standard tried to define four isolation levels (read uncommitted, read committed, repeatable read and serializable), but its definition is flawed.

– https://github.com/ept/hermitage

… let alone any expectation of consistency in DBMS vendors' definitions.

Hermitage's collection of precise literature definitions of isolation levels and anomalies that happen in systems is a handy reference.

The project also provides examples of manually testing each anomaly on various DBMSs, like a lightweight Jepsen run by hand. It's fascinating how clearly a few simple queries run by hand can illustrate a DBMS's ability (or not) to implement complex behavior.

Most of the examples have to be literally run by hand. The FoundationDB examples, however, use a simple bash script to test across two processes with tmux, which I reproduce here for quick reference. (It's also kind of funny, in a wow sense, that FDB is the only DBMS in the project that only implements the strongest level, serializability).

Here's the setup and prompt driver support:

tmux kill-session -t SQL || true
tmux new-session -d -n SQL -s SQL "fdbsqlcli test | tee /tmp/SQL0.out"
tmux split-window -h -t SQL "fdbsqlcli test | tee /tmp/SQL1.out"

count_prompts()
{
  grep "=>" /tmp/SQL$1.out | wc -l
}

wait_for_prompts()
{
  until [ $2 -ne $(count_prompts $1) ]; do
    echo -n '.'
  done
}

tell()
{
  BEFORE=$(count_prompts $1)
  tmux send-keys -t $1 "$2\\;" c-M
  wait_for_prompts $1 $BEFORE
}

And here's one example test:

echo "Running g0 test."
tell 0 "begin"
tell 1 "begin"
tell 0 "update test set value = 11 where id = 1"
tell 1 "update test set value = 12 where id = 1"
tell 0 "update test set value = 21 where id = 2"
tell 0 "commit"
tell 0 "select * from test"
tell 1 "update test set value = 22 where id = 2"
tell 1 "commit" # Rejected with ERROR: FoundationDB commit aborted: 1020 - not_committed
tell 0 "select * from test" # Shows 1 => 11, 2 => 21
tell 1 "select * from test" # Shows 1 => 11, 2 => 21