DROP TABLE account;
CREATE TABLE account (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) UNIQUE,
  balance NUMERIC(18, 4)
);

----
-- READ COMMITTED
----
-- LOST UPDATE
----
DELETE FROM account;
INSERT INTO account ("name", balance) VALUES ('Murlak', 1500.0);

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;

-- A. Step 1.
UPDATE account
  SET balance = balance + 1000.0
  WHERE name = 'Murlak';

COMMIT;
 
SELECT * FROM account;

----
-- NON-REPEATABLE READ
----
DELETE FROM account;
INSERT INTO account ("name", balance) VALUES ('Murlak', 1500.0);

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;

-- A. Step 1
UPDATE account
  SET balance = balance * 1.05
  WHERE balance <= 1000.0;
 
 -- A. Step 2
UPDATE account
  SET balance = balance * 1.1
  WHERE balance > 1000.0;
 
COMMIT;

----
-- PHANTOM READ 👻
----
DELETE FROM account;
INSERT INTO account ("name", balance) VALUES ('Murlak', 1500.0);

CREATE OR REPLACE PROCEDURE 
    assert_all_above_1000() 
AS $$
BEGIN
	IF (
      -- A. Step 1.
	  SELECT MIN(balance) 
	      FROM account
	) < 1000 THEN
      RAISE EXCEPTION 'Cannot perform operation';
    END IF;
END;
$$ LANGUAGE plpgsql;

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;

CALL assert_all_above_1000();

-- A. Step 2.
UPDATE account
  SET balance = balance - 1000.0;

COMMIT;

----
-- REPEATABLE READ
----
-- FIX - LOST UPDATE
----
DELETE FROM account;
INSERT INTO account ("name", balance) VALUES ('Murlak', 1500.0);

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;

-- A. Step 1.
UPDATE account
  SET balance = balance + 1000.0
  WHERE name = 'Murlak';

COMMIT;
 
SELECT * FROM account;

----
-- FIX - NON-REPEATABLE READ
----
DELETE FROM account;
INSERT INTO account ("name", balance) VALUES ('Murlak', 1500.0);

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;

-- A. Step 1
UPDATE account
  SET balance = balance * 1.05
  WHERE balance <= 1000.0;
 
 -- A. Step 2
UPDATE account
  SET balance = balance * 1.1
  WHERE balance > 1000.0;
 
COMMIT;

----
-- FIX - PHANTOM READ 🚫👻
----
DELETE FROM account;
INSERT INTO account ("name", balance) VALUES ('Murlak', 1500.0);

CREATE OR REPLACE PROCEDURE 
    assert_all_above_1000() 
AS $$
BEGIN
	IF (
      -- A. Step 1.
	  SELECT MIN(balance) 
	      FROM account
	) < 1000 THEN
      RAISE EXCEPTION 'Cannot perform operation';
    END IF;
END;
$$ LANGUAGE plpgsql;

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;

CALL assert_all_above_1000();

-- A. Step 2.
UPDATE account
  SET balance = balance - 1000.0;

COMMIT;

----
-- SERIALIZATION ANOMALY
----
DELETE FROM account;
INSERT INTO account ("name", balance)
  VALUES ('Marcin Peczarski', 100.0),
         ('Marcin Engel', 200.0),
         ('Krzysztof Ciebiera', 3000.0),
         ('Krzysztof Stencel', 5000.0);
         
BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;

INSERT INTO account ("name", balance)
SELECT 'Krzysztof Diks', SUM(balance)
  FROM account
  WHERE "name" LIKE 'Marcin %';

COMMIT;

SELECT * FROM account;

----
-- SERIALIZABLE
---
-- FIX - SERIALIZATION ANOMALY
---
DELETE FROM account;
INSERT INTO account ("name", balance)
  VALUES ('Marcin Peczarski', 100.0),
         ('Marcin Engel', 200.0),
         ('Krzysztof Ciebiera', 3000.0),
         ('Krzysztof Stencel', 5000.0);

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;

INSERT INTO account ("name", balance)
SELECT 'Krzysztof Diks', SUM(balance)
  FROM account
  WHERE "name" LIKE 'Marcin %';

COMMIT;

SELECT * FROM account;

----
-- DEADLOCK
----
DELETE FROM account;
INSERT INTO account ("name", balance) 
  VALUES ('Murlak', 1500.0),
         ('Stencel', 500.0);
        
BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;

-- A. Step 1.
UPDATE account
  SET balance = balance - 500.0
  WHERE "name" = 'Murlak';

-- A. Step 2.
UPDATE account
  SET balance = balance + 500.0
  WHERE "name" = 'Stencel';
 
COMMIT;

SELECT * FROM account;

----
-- SERIALIZABLE works unlike in Oracle
----

DELETE FROM account;

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM account;

INSERT INTO account ("name", balance) VALUES ('Alice', 300);

SELECT * FROM account;

COMMIT;

----
ALTER TABLE account
DROP CONSTRAINT account_name_key;
INSERT INTO account ("name", balance)
VALUES ('Alice', 300),
       ('Alice', 300),
       ('Alice', 300),
       ('Bob', 300);

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;

UPDATE account SET "name" = 'Alice' WHERE "name" = 'Bob';

COMMIT;

SELECT * FROM account;