MySQL, Postgres Isolation Levels - Phenomena

mysql_dirtyreads.sql

----------
-- TRX.1 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
INSERT INTO messages (id, message, unopened_flag, cat_id) VALUES (1, 'I need help!', true, 50);

-- GOTO TRX.2
-- Phase.Two
UPDATE notifications SET unopened = unopened + 1 WHERE cat_id = 50;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT * FROM messages WHERE unopened_flag = true;
SELECT unopened FROM notifications WHERE cat_id = 50;

COMMIT;
-- GOTO TRX.1 > Phase.Two

mysql_init.sql

CREATE TABLE `messages` (
  `id` varchar(100) NOT NULL,
  `message` varchar(100) NOT NULL,
  `unopened_flag` tinyint(1) NOT NULL DEFAULT '1',
  `cat_id` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `notifications` (
  `cat_id` varchar(35) NOT NULL,
  `unopened` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `users` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` integer NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `doctors` (
  `user_id` varchar(100) NOT NULL,
  `on_call` tinyint(1) NOT NULL DEFAULT '0',
  `shift_id` varchar(35) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `wallets` (
  `id` varchar(100) NOT NULL,
  `user_id` varchar(100) NOT NULL,
  `balance` integer NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 2 Notifications, 2 Users, 2 Doctors, 2 Wallets
INSERT INTO notifications (cat_id,unopened) VALUES ('50',0), ('40',10);
INSERT INTO users (id,name,age) VALUES ('1','Alice',30), ('2','Bob',32);
INSERT INTO doctors (user_id,on_call,shift_id) VALUES ('1',1,126), ('2',1,126);
INSERT INTO wallets (id,user_id,balance) VALUES ('200','1',500), ('226','1',500);

mysql_lost_updates.sql

----------
-- TRX.1 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT unopened FROM notifications WHERE cat_id = 40;

-- GOTO TRX.2
-- Phase.Two
UPDATE notifications SET unopened = 3 WHERE cat_id = 40;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT unopened FROM notifications WHERE cat_id = 40;
UPDATE notifications SET unopened = 5 WHERE cat_id = 40;

COMMIT;
-- GOTO TRX.1 > Phase.Two

mysql_nonrepeatable_read.sql

----------
-- TRX.1 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT id, name, age from users WHERE id = 1;

-- GOTO TRX.2
-- Phase.Two
SELECT id, name, age from users WHERE id = 1;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
UPDATE users SET age = 34 WHERE id = 1;

COMMIT;
-- GOTO TRX.1 > Phase.Two

mysql_phantom_read.sql

----------
-- TRX.1 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT id, name, age from users WHERE age BETWEEN 30 AND 40;

-- GOTO TRX.2
-- Phase.Two
SELECT id, name, age from users WHERE age BETWEEN 30 AND 40;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
INSERT INTO users (id,name,age) VALUES ('3','Amir',34);

COMMIT;
-- GOTO TRX.1 > Phase.Two

mysql_read_skew.sql

----------
-- TRX.1 | (Transfer)
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT balance FROM wallets WHERE id = '200' AND user_id = '1';

-- GOTO TRX.2
-- Phase.Two
SELECT balance FROM wallets WHERE id = '226' AND user_id = '1';

COMMIT;

----------
-- TRX.2 | (Alice)
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
UPDATE wallets SET balance = balance + 100 WHERE id = '200' AND user_id = '1';
UPDATE wallets SET balance = balance - 100 WHERE id = '226' AND user_id = '1';

COMMIT;
-- GOTO TRX.1 > Phase.Two

mysql_write_skew.sql

----------
-- TRX.1 | (User 2)
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 126;

-- GOTO TRX.2
-- Phase.Two
UPDATE doctors SET on_call = false WHERE user_id = 2 AND shift_id = 126;

COMMIT;
-- GOTO TRX.2 > Phase.Two

----------
-- TRX.2 | (User 1)
----------
-- Phase.One
SET session transaction isolation level read uncommitted;
START TRANSACTION;
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 126;

-- GOTO TRX.1 > Phase.Two
-- Phase.Two
UPDATE doctors SET on_call = false WHERE user_id = 1 AND shift_id = 126;

COMMIT;

postgres_dirtyreads.sql

-- IN PG THIS BEHAVES DIFFERENTLY
----------
-- TRX.1 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO messages (id, message, unopened_flag, cat_id) VALUES (1, 'I need help!', true, 50);

-- GOTO TRX.2
-- Phase.Two
UPDATE notifications SET unopened = unopened + 1 WHERE cat_id = 50;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM messages WHERE unopened_flag = true;
SELECT unopened FROM notifications WHERE cat_id = 50;

COMMIT;
-- GOTO TRX.1 > Phase.Two

postgres_init.sql

CREATE TABLE messages (
	id varchar NOT NULL,
	message varchar NOT NULL,
	unopened_flag bool NOT NULL DEFAULT true,
	cat_id varchar NOT NULL
);

CREATE TABLE notifications (
	cat_id varchar NOT NULL,
	unopened INTEGER NOT NULL default 0
);

CREATE TABLE users (
	id varchar NOT NULL,
	name varchar NOT NULL,
	age integer NOT NULL
);

CREATE TABLE doctors (
	user_id varchar NOT NULL,
	on_call bool NOT NULL DEFAULT false,
	shift_id varchar NOT NULL
);

CREATE TABLE wallets (
	id varchar NOT NULL,
	user_id varchar NOT NULL,
	balance integer NOT NULL
);

-- 2 Notifications, 2 Users, 2 Doctors, 2 Wallets
INSERT INTO notifications (cat_id,unopened) VALUES ('50',0), ('40',10);
INSERT INTO users (id,name,age) VALUES ('1','Alice',30), ('2','Bob',32);
INSERT INTO doctors (user_id,on_call,shift_id) VALUES ('1',true,126), ('2',true,126);
INSERT INTO wallets (id,user_id,balance) VALUES ('200','1',500), ('226','1',500);

postgres_lost_updates.sql

----------
-- TRX.1 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT unopened FROM notifications WHERE cat_id = 40;

-- GOTO TRX.2
-- Phase.Two
UPDATE notifications SET unopened = 3 WHERE cat_id = 40;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT unopened FROM notifications WHERE cat_id = 40;
UPDATE notifications SET unopened = 5 WHERE cat_id = 40;

COMMIT;
-- GOTO TRX.1 > Phase.Two

postgres_nonrepeatable_read.sql

----------
-- TRX.1 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT id, name, age from users WHERE id = 1;

-- GOTO TRX.2
-- Phase.Two
SELECT id, name, age from users WHERE id = 1;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE users SET age = 34 WHERE id = 1;

COMMIT;
-- GOTO TRX.1 > Phase.Two

postgres_phantom_read.sql

----------
-- TRX.1 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT id, name, age from users WHERE age BETWEEN 30 AND 40;

-- GOTO TRX.2
-- Phase.Two
SELECT id, name, age from users WHERE age BETWEEN 30 AND 40;

COMMIT;

----------
-- TRX.2 |
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO users (id,name,age) VALUES ('3','Amir',34);

COMMIT;
-- GOTO TRX.1 > Phase.Two

postgres_read_skew.sql

----------
-- TRX.1 | (Transfer)
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM wallets WHERE id = '200' AND user_id = '1';

-- GOTO TRX.2
-- Phase.Two
SELECT balance FROM wallets WHERE id = '226' AND user_id = '1';

COMMIT;

----------
-- TRX.2 | (Alice)
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE wallets SET balance = balance + 100 WHERE id = '200' AND user_id = '1';
UPDATE wallets SET balance = balance - 100 WHERE id = '226' AND user_id = '1';

COMMIT;
-- GOTO TRX.1 > Phase.Two

postgres_write_skew.sql

----------
-- TRX.1 | (User 2)
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 126;

-- GOTO TRX.2
-- Phase.Two
UPDATE doctors SET on_call = false WHERE user_id = 2 AND shift_id = 126;

COMMIT;
-- GOTO TRX.2 > Phase.Two

----------
-- TRX.2 | (User 1)
----------
-- Phase.One
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT count(*) FROM doctors WHERE on_call = true AND shift_id = 126;

-- GOTO TRX.1 > Phase.Two
-- Phase.Two
UPDATE doctors SET on_call = false WHERE user_id = 1 AND shift_id = 126;

COMMIT;