From e9b19b2ead23c68ba2fc34a2f71c7a263fbb184c Mon Sep 17 00:00:00 2001
From: Alexander Goussas <goussasalexander@gmail.com>
Date: Thu, 9 Jan 2025 23:36:10 -0500
Subject: [PATCH] add read committed example

---
 .gitignore         |  2 ++
 README.md          | 13 +++++++++++++
 docker-compose.yml | 11 +++++++++++
 read_commited.py   | 42 ++++++++++++++++++++++++++++++++++++++++++
 schema.sql         |  9 +++++++++
 5 files changed, 77 insertions(+)
 create mode 100644 .gitignore
 create mode 100644 README.md
 create mode 100644 docker-compose.yml
 create mode 100644 read_commited.py
 create mode 100644 schema.sql

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..033df5f
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+.venv
+__pycache__
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..732afb0
--- /dev/null
+++ b/README.md
@@ -0,0 +1,13 @@
+This repository contains examples of problems that might arise if developers are not aware
+of the different isolation levels in databases.
+
+We use PostgreSQL specifically here. Consult your vendor for details on their isolation levels.
+Relevant Postgres 17 docs on isolation levels: https://www.postgresql.org/docs/current/transaction-iso.html.
+
+## Read-commited
+
+- [Example](./read_commited.py)
+
+This level uses snapshots for each separate command. A query that acts based on previously read data
+might be using stale data if another transaction committed a mutation to values read by the
+previous select.
diff --git a/docker-compose.yml b/docker-compose.yml
new file mode 100644
index 0000000..98dcb6f
--- /dev/null
+++ b/docker-compose.yml
@@ -0,0 +1,11 @@
+services:
+  db:
+    image: postgres:17
+    environment:
+      POSTGRES_USER: postgres
+      POSTGRES_PASSWORD: postgres
+      POSTGRES_DB: postgres
+    volumes:
+      - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql
+    ports:
+      - "5432:5432"
diff --git a/read_commited.py b/read_commited.py
new file mode 100644
index 0000000..32a9f2d
--- /dev/null
+++ b/read_commited.py
@@ -0,0 +1,42 @@
+import records
+
+
+db = records.Database("postgresql://postgres:postgres@localhost/postgres")
+
+# Scenario: We want to transfer $10 from Bob to Alice
+# NOTE: Both users' balance is initially set to 10.
+
+c1, c2 = db.get_connection(), db.get_connection()
+t1, t2 = c1.transaction(), c2.transaction()
+
+# We read Bob's balance
+bob_balance = (
+    c1.query("select amount from balance where username = 'bob'").first().amount
+)
+
+# In another transaction, Bob's balance was set to 0 (maybe a transfer to another user).
+# This transaction commits.
+c2.query("update balance set amount = 0 where username = 'bob'")
+t2.commit()
+
+# We use the value we read before for the test.
+if bob_balance >= 10:
+    # And update the balances accordingly if the condition holds.
+    c1.query("update balance set amount = amount - 10 where username = 'bob'")
+    c1.query("update balance set amount = amount + 10 where username = 'alice'")
+    t1.commit()
+
+bob_balance = (
+    c1.query("select amount from balance where username = 'bob'").first().amount
+)
+
+alice_balance = (
+    c1.query("select amount from balance where username = 'alice'").first().amount
+)
+
+c1.close()
+c2.close()
+
+# What will the final balances be?
+print("Bob: " + str(bob_balance))
+print("Alice: " + str(alice_balance))
diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..dd005eb
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,9 @@
+create table balance (
+    username text not null,
+    amount   decimal not null default 0,
+
+    primary key (username)
+);
+
+insert into balance
+values ('bob', 10), ('alice', 10);
-- 
2.43.0