----
-- READ COMMITTED
----
-- LOST UPDATE
----

CREATE PROCEDURE 
  charge("name" VARCHAR(255)) AS $$
DECLARE 
	b NUMERIC(18, 4);
BEGIN
	-- B. Step 2.
	SELECT balance
	  INTO b
	  FROM account a
	  WHERE a."name" = $1;
	 
	IF b >= 1000.0 THEN
	  -- B. Step 3.
	  UPDATE account a
	    SET balance = b - 1000.0
	    WHERE a."name" = $1;
	END IF;
END;
$$ LANGUAGE plpgsql;

BEGIN TRANSACTION 
ISOLATION LEVEL READ COMMITTED;

-- B. Step 1.
SELECT * FROM account a
WHERE "name" = 'Murlak';

CALL charge('Murlak');

COMMIT;

----
-- NON-REPEATABLE READ
----

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;

-- B. Step 1.
UPDATE account
  SET balance = balance - 1000.0
  WHERE "name" = 'Murlak';

COMMIT;

----
-- PHANTOM READ 👻
----

BEGIN TRANSACTION 
ISOLATION LEVEL READ COMMITTED;

-- B. Step 1.
INSERT INTO account("name", balance)
  VALUES ('Stencel', 500);

COMMIT;

----
-- REPEATABLE READ
----
-- FIX - LOST UPDATE
----

CREATE PROCEDURE 
  charge("name" VARCHAR(255)) AS $$
DECLARE 
	b NUMERIC(18, 4);
BEGIN
	-- B. Step 2.
	SELECT balance
	  INTO b
	  FROM account a
	  WHERE a."name" = $1;
	 
	IF b >= 1000.0 THEN
	  -- B. Step 3.
	  UPDATE account a
	    SET balance = b - 1000.0
	    WHERE a."name" = $1;
	END IF;
END;
$$ LANGUAGE plpgsql;

BEGIN TRANSACTION 
ISOLATION LEVEL REPEATABLE READ;

-- B. Step 1.
SELECT * FROM account a
WHERE "name" = 'Murlak';

CALL charge('Murlak');

COMMIT;

----
-- FIX - NON-REPEATABLE READ
----

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;

-- B. Step 1.
UPDATE account
  SET balance = balance - 1000.0
  WHERE "name" = 'Murlak';

COMMIT;

----
-- FIX - PHANTOM READ 🚫👻
----

BEGIN TRANSACTION 
ISOLATION LEVEL REPEATABLE READ;

-- B. Step 1.
INSERT INTO account("name", balance)
  VALUES ('Stencel', 500);

COMMIT;

----
-- SERIALIZATION ANOMALY
----

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;

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

----
-- SERIALIZABLE
---
-- FIX - SERIALIZATION ANOMALY
---

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;

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

----
-- DEADLOCK
----

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;

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

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

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM account;

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

SELECT * FROM account;

COMMIT;

----

DELETE FROM account;

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;

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

COMMIT;