-- Session A (not yet committed)
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 42;
-- balance is now 0 in Session A's memory, but not committed

-- ❌ Session B (READ UNCOMMITTED — Postgres does not actually implement this,
--    but MySQL does) reads Session A's uncommitted write
SELECT balance FROM accounts WHERE id = 42;
-- Returns: 0 — a value that may be rolled back in the next millisecond

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 42;  -- Returns: 1000

  -- Session B (commits between A's two reads)
  BEGIN;
  UPDATE accounts SET balance = 500 WHERE id = 42;
  COMMIT;

SELECT balance FROM accounts WHERE id = 42;  -- Returns: 500 ← different!
-- Session A saw two different values for the same row within one transaction
COMMIT;

-- Session A
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns: 5

  -- Session B inserts a new pending order and commits
  INSERT INTO orders (status) VALUES ('pending');
  COMMIT;

SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns: 6 ← phantom!
COMMIT;

-- ❌ Classic lost update: both sessions read balance = 1000
-- Session A: balance = balance - 100  → writes 900
-- Session B: balance = balance - 200  → writes 800 (overwrites A's 900)
-- Net result: 800. Correct result: 700. A's update is silently lost.

-- Session A                         | Session B
BEGIN;                               | BEGIN;
SELECT balance FROM accounts         | SELECT balance FROM accounts
  WHERE id = 42;  -- 1000           |   WHERE id = 42;  -- 1000
UPDATE accounts SET balance = 900   |
  WHERE id = 42;                     |
COMMIT;                              | UPDATE accounts SET balance = 800
                                     |   WHERE id = 42;
                                     | COMMIT;  -- A's change is gone

-- Business rule: at least one doctor must always be on-call.
-- Two doctors both request time-off simultaneously.

-- Session A (Dr. Smith's request)         | Session B (Dr. Jones's request)
BEGIN;                                     | BEGIN;
SELECT COUNT(*) FROM doctors               | SELECT COUNT(*) FROM doctors
  WHERE on_call = true;  -- 2             |   WHERE on_call = true;  -- 2
-- "2 on call, safe to take one off"      | -- "2 on call, safe to take one off"
UPDATE doctors SET on_call = false         |
  WHERE id = 'smith';                      |
COMMIT;                                    | UPDATE doctors SET on_call = false
                                           |   WHERE id = 'jones';
                                           | COMMIT;
-- Result: 0 doctors on call. Rule violated. Neither transaction saw the other's write.

-- Set isolation level per transaction (not globally)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... your multi-read business logic ...
COMMIT;

-- Or set the default for a session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- ✅ Prevents lost update: lock the row before reading it,
-- so any concurrent transaction trying to read-for-update on the same row blocks
-- until this transaction commits or rolls back.
BEGIN;
SELECT balance FROM accounts
  WHERE id = 42
  FOR UPDATE;  -- Acquires exclusive row lock
-- No other transaction can modify or lock this row until COMMIT

UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

-- Use FOR SHARE when you need to read a row and prevent it being deleted/updated,
-- but you are willing to allow other readers to also hold a share lock.
BEGIN;
SELECT * FROM products WHERE id = 99 FOR SHARE;
-- Other sessions can SELECT FOR SHARE — they cannot UPDATE or DELETE
COMMIT;

-- ❌ Deadlock: inconsistent lock acquisition order
-- Session A locks account 1, then tries to lock account 2
-- Session B locks account 2, then tries to lock account 1

-- Session A                          | Session B
BEGIN;                                | BEGIN;
SELECT * FROM accounts                | SELECT * FROM accounts
  WHERE id = 1 FOR UPDATE;           |   WHERE id = 2 FOR UPDATE;
-- ... processing ...                 | -- ... processing ...
SELECT * FROM accounts                | SELECT * FROM accounts
  WHERE id = 2 FOR UPDATE;  -- WAITS |   WHERE id = 1 FOR UPDATE;  -- WAITS
-- DEADLOCK DETECTED → one tx aborted

-- ✅ Structural prevention: always lock rows in the same order (ascending id)
-- Both sessions lock account 1 first, then account 2.
-- One will wait for the other — no cycle, no deadlock.
BEGIN;
SELECT * FROM accounts
  WHERE id IN (1, 2)
  ORDER BY id  -- Enforces consistent acquisition order
  FOR UPDATE;
COMMIT;

-- Schema: add a version column to any row that needs optimistic concurrency
ALTER TABLE orders ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- ❌ Naive update: no conflict detection — overwrites concurrent changes silently
UPDATE orders SET status = 'shipped' WHERE id = 42;

-- ✅ Optimistic update: include version in WHERE clause
-- If the row was modified by someone else since our read, version won't match
-- and rowsAffected = 0 — the application must detect this and retry
UPDATE orders
  SET status = 'shipped', version = version + 1
  WHERE id = 42
    AND version = 3;  -- Must match the version we read

-- Application checks rows affected:
-- rowsAffected = 1 → success
-- rowsAffected = 0 → conflict: re-read, re-compute, retry

-- Postgres SERIALIZABLE: SSI detects the write-skew cycle and aborts one transaction
-- Both doctor transactions (from §1.5) would be caught:
-- Postgres detects that A read doctors, B read doctors, A wrote, B wrote
-- → serialization cycle → one transaction aborted with:
-- ERROR: could not serialize access due to read/write dependencies among transactions

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors WHERE on_call = true;
UPDATE doctors SET on_call = false WHERE id = 'smith';
COMMIT;  -- May throw serialization error → application must retry

-- MySQL SERIALIZABLE: implicitly locks all read rows
-- The second doctor transaction blocks at the SELECT until the first commits
BEGIN;
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- Acquires shared locks
-- Concurrent sessions wanting to modify any on_call row are blocked here
COMMIT;

-- ❌ Copying Postgres SERIALIZABLE advice to MySQL:
-- "Use SERIALIZABLE, it will abort conflicting transactions"
-- In MySQL, it blocks — it does not abort. Throughput characteristics are completely different.

-- ❌ Copying MySQL SERIALIZABLE tuning (lock timeouts) to CockroachDB:
-- CockroachDB uses abort-and-retry, not blocking — lock timeout tuning is irrelevant.