# Testing Serializable Isolation in CockroachDB

As a follow-up to [A Basic Guide to Transaction Isolation](https://blog.cloudneutral.se/a-basic-guide-to-transaction-isolation), this article will reproduce a handful of interesting tests described on the PostgreSQL [SSI](https://wiki.postgresql.org/wiki/SSI) page. Only this time for CockroachDB.

Another great resource to illustrate the behaviour of serializable is Martin Kleppman's [Hermitage](https://github.com/ept/hermitage) project and the CockroachDB [contribution](https://github.com/ept/hermitage/blob/master/cockroachdb.md). It goes through a rich set of anomalies ranging from dirty writes to write skew on disjoint and predicate reads.

# Examples

These tests were executed using the following CockroachDB version:

```bash
$ cockroach version
Build Tag:        v22.2.8
Build Time:       2023/04/17 13:22:08
Distribution:     CCL
Platform:         linux amd64 (x86_64-pc-linux-gnu)
Go Version:       go1.19.6
C Compiler:       gcc 6.5.0
Build Commit ID:  9a7c644e565b21d29db26a0a82524a00809d0a8c
Build Type:       release
```

First, create a test database:

```bash
cockroach sql --insecure --host=localhost -e "CREATE database test"
```

Next, start three separate shell windows representing transactions T1, T2 and T3. Whenever there's a "-- T1" comment for a SQL statement, run that statement in the designated console session.

```bash
cockroach sql --insecure --host=localhost --database test // for T1
cockroach sql --insecure --host=localhost --database test // for T2
cockroach sql --insecure --host=localhost --database test // for T3
```

## Black and White Marbles

This is a test for Write Skew (A5B), prevented by serializable. [Write Skew](https://www.cockroachlabs.com/blog/what-write-skew-looks-like/) is when two transactions overlap and one reads data that another is writing.

Schema setup:

```sql
create table if not exists marbles (
  id    bigint      not null primary key,
  color varchar(25) not null
);

delete from marbles where 1=1;

insert into marbles values
  (1,'black'),
  (2,'black'),
  (3,'black'),
  (4,'black'),
  (5,'black'),
  (6,'white'),
  (7,'white'),
  (8,'white'),
  (9,'white'),
  (10,'white');
```

Note: The `set transaction isolation level serializable` part is redundant for CockroachDB since it's the default (and only level supported).

Observe that CockroachDB serializable prevent this anomaly:

```sql
begin; set transaction isolation level serializable; -- T1
begin; set transaction isolation level serializable; -- T2
update marbles set color = 'black' where color = 'white'; -- T1
update marbles set color = 'white' where color = 'black'; -- T2
commit; -- T1. First commit wins.
commit; -- T2. ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh due to a conflict: committed value on key /Table/137/1/6/0): "sql txn" meta={id=f8ee6d8c key=/Table/137/1/1/0 pri=0.00066739 epo=0 ts=1682691951.358336984,2 min=1682691934.561490359,0 seq=5} lock=true stat=PENDING rts=1682691934.561490359,0 wto=false gul=1682691935.061490359,0
SQLSTATE: 40001
HINT: See: https://www.cockroachlabs.com/docs/v22.2/transaction-retry-error-reference.html#retry_serializable
```

All the colours must match:

```sql
SELECT * from marbles order by id;
  id | color
-----+--------
   1 | black
   2 | black
   3 | black
   4 | black
   5 | black
   6 | black
   7 | black
   8 | black
   9 | black
  10 | black
(10 rows)
```

If you would run in SNAPSHOT (which CockroachDB doesn't provide) then it would not prevent write skew and look like this instead:

```sql
+----+-------+
| id | color |
+----+-------+
|  1 | white |
|  2 | white |
|  3 | white |
|  4 | white |
|  5 | white |
|  6 | black |
|  7 | black |
|  8 | black |
|  9 | black |
| 10 | black |
+----+-------+
-- (10 rows)
```

The colours have been flipped due to write skew, which is expected under concurrent execution with snapshot isolation (or read committed).

## Red, Green and Blue Marbles

This example is similar to the previous one, only this time involving three transactions.

Setup schema:

```sql
create table if not exist marbles (
  id    bigint      not null primary key,
  color varchar(25) not null
);

delete from marbles where 1=1;
insert into marbles (id,color) values (1,'red');
insert into marbles (id,color) values (2,'red');
insert into marbles (id,color) values (3,'red');
insert into marbles (id,color) values (4,'yellow');
insert into marbles (id,color) values (5,'yellow');
insert into marbles (id,color) values (6,'yellow');
insert into marbles (id,color) values (7,'blue');
insert into marbles (id,color) values (8,'blue');
insert into marbles (id,color) values (9,'blue');
```

Again, CockrochDB `SERIALIZABLE` isolation prevents Write Skew (A5B):

```sql
begin; set transaction isolation level SERIALIZABLE ; -- T1
update marbles set color = 'yellow' where color = 'red'; -- T1

begin; set transaction isolation level SERIALIZABLE ; -- T2
update marbles set color = 'blue' where color = 'yellow'; -- T2. Blocks on T1 intents.

begin; set transaction isolation level SERIALIZABLE ; -- T3
update marbles set color = 'red' where color = 'blue'; -- T3. Blocks.

commit; --T1
(T2 unblocks - rows affected 6)
(T3 unblocks - rows affected 3)
commit; -- T3
commit; -- T2. ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh due to a conflict
```

The correct outcome (only yellow and red):

```sql
select * from marbles;
+----+--------+
| id | color  |
+----+--------+
|  1 | yellow |
|  2 | yellow |
|  3 | yellow |
|  4 | yellow |
|  5 | yellow |
|  6 | yellow |
|  7 | red    |
|  8 | red    |
|  9 | red    |
+----+--------+
(9 rows)
```

## Intersecting Data

Two concurrent transactions read data, and each uses it to update the range read by the other.

Setup schema:

```sql
create table if not exists tab (
  id bigint not null,
  value bigint not null
);

delete from tab where 1=1;

INSERT INTO tab VALUES
(1, 10), (1, 20), (2, 100), (2, 200);
```

Observe CockroachDB guarantees a serial execution:

```sql
begin; set transaction isolation level serializable; -- T1
SELECT SUM(value) FROM tab WHERE id = 1; -- T1
INSERT INTO tab VALUES (2, 30); -- T1

begin; set transaction isolation level serializable; -- T2
SELECT SUM(value) FROM tab WHERE id = 2; -- T2 (blocks)

commit; -- T1 (unblocks T2)

INSERT INTO tab VALUES (1, 330); -- T2

commit; -- T2

SELECT * from tab;
```

Yields:

```sql
SELECT * from tab;
  id | value
-----+--------
   1 |    10
   1 |    20
   2 |   100
   2 |   200
   2 |    30
   1 |   330
(6 rows)
```

## Overdraft Protection

Here we will protect the invariant that the total of all accounts must exceed the amount requested.

Schema setup:

```sql
create table if not exists account
  (
    name VARCHAR(25) not null,
    type VARCHAR(25) not null,
    balance NUMERIC(19, 2) not null,
    
    primary key (name, type)
  );
  
delete from account where 1=1;

insert into account values
  ('alice','saving', 500),
  ('alice','checking', 500);
```

Let's try to play the bank under serializable isolation:

```sql
begin; set transaction isolation level serializable ; -- T1
select type, balance from account where name = 'alice'; -- T1

begin; set transaction isolation level serializable ; -- T2
select type, balance from account where name = 'alice'; -- T2

update account set balance = balance - 900.00 where name = 'alice' and type = 'saving'; -- T1
commit; -- T1

update account set balance = balance - 900.00 where name = 'alice' and type = 'checking'; -- T2
commit; -- T2 ERROR: restart transaction: TransactionRetryWithProtoRefreshError: TransactionRetryError: retry txn (RETRY_SERIALIZABLE - failed preemptive refresh due to a conflict:
```

Yields the following where the invariant holds:

```sql
select * from account;
  name  |   type   | balance
--------+----------+----------
  alice | checking |  500.00
  alice | saving   | -400.00
(2 rows)
```

## Deposit Report

Setup schema (before every test run):

```sql
create table if not exists control
  (
    deposit_no int not null
  );
create table if not exists receipt
  (
    receipt_no bigint NOT NULL PRIMARY KEY DEFAULT unique_rowid(),
    deposit_no int not null,
    payee text not null,
    amount numeric(19,2) not null
  );

DELETE from control where 1=1;
DELETE from receipt where 1=1;
insert into control values (1);

insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Crosby', 100.00);
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Stills', 200.00);
insert into receipt
  (deposit_no, payee, amount)
  values ((select deposit_no from control), 'Nash', 300.00);
```

Test sequence:

```sql
begin; set transaction isolation level serializable ; -- T1
insert into receipt (deposit_no, payee, amount) values ( (select deposit_no from control), 'Young', 100.00 ); -- T1
select * from receipt; -- T1

begin; set transaction isolation level serializable ; -- T2   
select deposit_no from control; -- T2
update control set deposit_no = 2 where 1=1; -- T2
commit; -- T2

begin; set transaction isolation level serializable ; -- T3   
select * from receipt where deposit_no = 1; -- T3. Blocks on T1
commit; -- T1
(T3 unblocks)
commit; -- T3
```

Yields:

```sql
select * from receipt;
      receipt_no     | deposit_no | payee  | amount
---------------------+------------+--------+---------
  860561294810873858 |          1 | Crosby | 100.00
  860561295115354114 |          1 | Stills | 200.00
  860561295382970370 |          1 | Nash   | 300.00
  860561358736326657 |          1 | Young  | 100.00
(4 rows)
```

## Rollover

This example was created to show that PostgreSQL can roll back read-only transactions to prevent serialization conflicts. It won't happen in CockroachDB.

Schema setup:

```sql
create table if not exists rollover (
  id int primary key, 
  n int not null
  );
delete from rollover where 1=1;
insert into rollover values (1,100), (2,10);
```

Financial transaction under serializable isolation:

```sql
begin; set transaction isolation level serializable ; -- T1
update rollover
  set n = n + (select n from rollover where id = 2)
  where id = 1; -- T1
  
begin; set transaction isolation level serializable ; -- T2
update rollover set n = n + 1 where id = 2; -- T2 - blocks on T1
commit; --T2 

begin; set transaction isolation level snapshot ; -- T3
select count(*) from rollover; -- T3 - blocks on T1         
commit; -- T1

select n from rollover where id in (1,2); -- T3
```

# Conclusion

This article showcased a few examples outlined in the PostgreSQL [SSI](https://wiki.postgresql.org/wiki/SSI#Deposit_Report) description page. It highlights some runtime differences between PostgreSQL SSI and CockroachDB serializable.