Transaction Isolation in Postgres (with cats)

Slide 1

Slide 1 text

Gwen Shapira, co-founder, Nile Date April 2024

Slide 2

Slide 2 text

Nice to meet you! I’m Gwen Shapira - Co-founder of Nile: Serverless Postgres for Moden SaaS - Previously: Cloud Native Kafka lead @ Confluent - Previously: Data architect (Hadoop, MySQL, Oracle) - Apache Kafka PMC - Wrote books, tweet a lot @gwenshap 02

Slide 3

Slide 3 text

We’ll talk about: - Cats. - How to accurately track how much they eat - SQL 92 isolation levels - Postgres isolation levels and how they are better - How Postgres Isolation is implemented efficiently

Slide 4

Slide 4 text

Hello, transactions! BEGIN; UPDATE cat_meals SET dried_food_given = dried_food_given+100 WHERE cat_name = 'Luke' and date = '2020-01-01'; UPDATE food_inventory SET left_quantity = left_quantity - 100 WHERE food_name = 'dried_food'; COMMIT;

Slide 5

Slide 5 text

A C I D tomic - when the cats are fed, the inventory is updated onsistent - can’t feed non-existing cats or negative inventory solated - my husband and I can safely feed cats at the same time urable - even if the DB crashes, everything is up to date

Slide 6

Slide 6 text

What isolation guarantees BEGIN; UPDATE cat_meals SET dried_food_given = dried_food_given+100 WHERE cat_name = 'Luke' and currdate = '2020- 01-01'; UPDATE food_inventory SET left_quantity = left_quantity - 100 WHERE food_name = 'dried_food'; COMMIT; SELECT * FROM cat_meals WHERE currdate = '2020-01-01';

Slide 7

Slide 7 text

SQL92 committee started by defining an ideal When you have multiple transactions happening at the same time, the resulting state of the DB is one that is possible to achieve by running this set of transactions not concurrently, but sequentially.

Slide 8

Slide 8 text

Serializable isolation is the “gold standard”. But why?

Slide 9

Slide 9 text

ANSI SQL92 isolation levels

Slide 10

Slide 10 text

Read uncommitted => dirty reads BEGIN; INSERT INTO food_inventory (food_name, left_quantity) values ('bonitas', 245); -- stopped at a coffee shop and forgot the food there ROLLBACK; SELECT * FROM food_inventory;

Slide 11

Slide 11 text

Why would anyone ever want to do this?

Slide 12

Slide 12 text

Read committed => non-repeatable reads BEGIN; UPDATE cats SET weight = weight-2 WHERE name = 'Luke' and currdate = '2020-01- 02'; COMMIT; BEGIN; SELECT name, weight FROM cats WHERE currdate = current_date; -- a lot more reporting… SELECT sex, avg(weight) FROM cats WHERE currdate = current_date GROUP BY sex;

Slide 13

Slide 13 text

In addition to inconsistent reports, this leads to the famous write-after-read problem

Slide 14

Slide 14 text

Write after read problem BEGIN; UPDATE cat_meals SET dried_food_given = dried_food_given+100 WHERE cat_name = 'Luke' and currdate = '2020- 01-01'; COMMIT; BEGIN; IF (SELECT dried_food_given FROM cat_meals WHERE cat_name = 'Luke' and currdate = '2020- 01-01') < 240 THEN UPDATE cat_meals SET dried_food_given = dried_food_given + 20 WHERE cat_name = 'Luke' and currdate = '2020- 01-01'; END IF; COMMIT;

Slide 15

Slide 15 text

Better check: UPDATE cat_meals SET dried_food_given = dried_food_given + 20 WHERE cat_name = 'Luke' and currdate = '2020-01-01' and dried_food_given < 240;

Slide 16

Slide 16 text

But this can be tricky UPDATE cat_meals SET dried_food_given = dried_food_given + 20 WHERE currdate = '2020-01-01' and cat_name in ( SELECT cat_name FROM cat_meals WHERE dried_food_given < 240 );

Slide 17

Slide 17 text

We can fix it UPDATE cat_meals SET dried_food_given = dried_food_given + 20 WHERE currdate = '2020-01-01' and cat_name in ( SELECT cat_name FROM cat_meals WHERE dried_food_given < 240 FOR UPDATE -- making sure no one else can feed cats at the same time );

Slide 18

Slide 18 text

Job queue on Postgres: BEGIN; SELECT job FROM job_queue FOR UPDATE SKIP LOCKED LIMIT 1 -- do stuff for the job DELETE job from job_queue COMMIT

Slide 19

Slide 19 text

Repeatable Reads => Phantom Reads BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats;

Slide 20

Slide 20 text

Problems with SQL92 Standard

Slide 21

Slide 21 text

Some isolation levels no longer make much sense. Let’s take “Repeatable Reads”. Why would it allow for phantom rows?

Slide 22

Slide 22 text

Microsoft Research, 1995 - new anomalies

Slide 23

Slide 23 text

What Postgres did (9.2+):

Slide 24

Slide 24 text

How does Postgres do this?

Slide 25

Slide 25 text

MVCC implementation ● Sequential transaction ID / XID assigned to each transaction ● Store multiple versions of same logical row ● Each version has ID of transactions that created and deleted it. ● Rules about what each transaction is / is not allowed to see

Slide 26

Slide 26 text

Follow along as these transaction execute In repeatable read / serializable mode BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats;

Slide 27

Slide 27 text

BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats; Take snapshot: select * from pg_current_snapshot() -- Lowest active: 190

Slide 28

Slide 28 text

Slide 29

Slide 29 text

BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats; t_xmin t_xmax name weight 125 201 Luke 19 201 0 Luke 24 Assigned TXID 201

Slide 30

Slide 30 text

BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats; t_xmin t_xmax name weight 125 201 Luke 19 201 0 Luke 24 201 0 Leia 10

Slide 31

Slide 31 text

BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats; t_xmin t_xmax name weight 125 201 Luke 19 201 0 Luke 24 201 0 Leia 10 Only sees version 190 and below

Slide 32

Slide 32 text

This is how snapshot isolation isolates

Slide 33

Slide 33 text

Read committed BEGIN; UPDATE cats SET weight = 24 WHERE name = 'Luke'; INSERT INTO cats (name, weight) VALUES ('Leia', 10); COMMIT; BEGIN; SELECT * FROM cats; -- a lot more reporting… SELECT * FROM cats; Take snapshot: select * from pg_current_snapshot() Take snapshot: select * from pg_current_snapshot()

Slide 34

Slide 34 text

What happens to old versions?

Slide 35

Slide 35 text

Performance implications

Slide 36

Slide 36 text

1. Transaction take locks - explicit and implicit. Operations can block on locks.

Slide 37

Slide 37 text

2. Repeatable read and serializable levels have extra checks and monitors for conflicts.

Slide 38

Slide 38 text

Repeatable Read / Serializable don’t take additional locks. But they can cause Serialization Errors

Slide 39

Slide 39 text

Serialization error BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM cats; UPDATE cats SET weight = weight+3 WHERE name = 'Luke'; COMMIT; BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM cats; UPDATE cats SET weight = 24 WHERE name = 'Luke'; COMMIT; Attempt to modify a value with new version we can’t see. Leading to “lost write” anomaly.

Slide 40

Slide 40 text

3. In high concurrency systems, serialization errors lead to rollbacks and retries. Which can be a performance issue.

Slide 41

Slide 41 text

Testing - postgres/src/test/isolation - Each test defines sessions and series of transaction steps - Specify interleaving to test (or it will automatically try them all) - Compare to expected results

Slide 42

Slide 42 text

Understanding transaction isolation levels leads to better reasoning about application behavior and better tradeoff decisions.

Slide 43

Slide 43 text

Follow us on: X (Twitter) twitter.com/niledatabase Discord discord.gg/8UuBB84tTy Linkedin https://www.linkedin.com/company/niledatabase/ twitter.com/gwenshap