As a follow-up to A Basic Guide to Transaction Isolation, this article will reproduce a handful of interesting tests described on the PostgreSQL SSI page. Only this time for CockroachDB.
Another great resource to illustrate the behaviour of serializable is Martin Kleppman's Hermitage project and the CockroachDB contribution. 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:
$ 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:
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.
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 is when two transactions overlap and one reads data that another is writing.
Schema setup:
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:
begin; set transaction isolation level serializable;
begin; set transaction isolation level serializable;
update marbles set color = 'black' where color = 'white';
update marbles set color = 'white' where color = 'black';
commit;
commit;
SQLSTATE: 40001
HINT: See: https://www.cockroachlabs.com/docs/v22.2/transaction-retry-error-reference.html
All the colours must match:
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:
+
| id | color |
+
| 1 | white |
| 2 | white |
| 3 | white |
| 4 | white |
| 5 | white |
| 6 | black |
| 7 | black |
| 8 | black |
| 9 | black |
| 10 | black |
+
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:
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):
begin; set transaction isolation level SERIALIZABLE ;
update marbles set color = 'yellow' where color = 'red';
begin; set transaction isolation level SERIALIZABLE ;
update marbles set color = 'blue' where color = 'yellow';
begin; set transaction isolation level SERIALIZABLE ;
update marbles set color = 'red' where color = 'blue';
commit;
(T2 unblocks - rows affected 6)
(T3 unblocks - rows affected 3)
commit;
commit;
The correct outcome (only yellow and red):
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:
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:
begin; set transaction isolation level serializable;
SELECT SUM(value) FROM tab WHERE id = 1;
INSERT INTO tab VALUES (2, 30);
begin; set transaction isolation level serializable;
SELECT SUM(value) FROM tab WHERE id = 2;
commit;
INSERT INTO tab VALUES (1, 330);
commit;
SELECT * from tab;
Yields:
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:
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:
begin; set transaction isolation level serializable ;
select type, balance from account where name = 'alice';
begin; set transaction isolation level serializable ;
select type, balance from account where name = 'alice';
update account set balance = balance - 900.00 where name = 'alice' and type = 'saving';
commit;
update account set balance = balance - 900.00 where name = 'alice' and type = 'checking';
commit;
Yields the following where the invariant holds:
select * from account;
name | type | balance
alice | checking | 500.00
alice | saving | -400.00
(2 rows)
Deposit Report
Setup schema (before every test run):
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:
begin; set transaction isolation level serializable ;
insert into receipt (deposit_no, payee, amount) values ( (select deposit_no from control), 'Young', 100.00 );
select * from receipt;
begin; set transaction isolation level serializable ;
select deposit_no from control;
update control set deposit_no = 2 where 1=1;
commit;
begin; set transaction isolation level serializable ;
select * from receipt where deposit_no = 1;
commit;
(T3 unblocks)
commit;
Yields:
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:
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:
begin; set transaction isolation level serializable ;
update rollover
set n = n + (select n from rollover where id = 2)
where id = 1;
begin; set transaction isolation level serializable ;
update rollover set n = n + 1 where id = 2;
commit;
begin; set transaction isolation level snapshot ;
select count(*) from rollover;
commit;
select n from rollover where id in (1,2);
Conclusion
This article showcased a few examples outlined in the PostgreSQL SSI description page. It highlights some runtime differences between PostgreSQL SSI and CockroachDB serializable.
