Transaction Isolation in Postgres (with cats)

Transactions are a key part of many modern databases, relational and non-relational systems alike. At a basic level, transactions allow us to treat a series of operations as a single unit. Transactions are so important because they provide guarantees that developers can use as assumptions when writing code. This means that you, the developer, don't need to worry about an entire set of concerns because the DB guarantees certain behaviors. This greatly simplifies the code and drastically improves its reliability.

What are these guarantees? You may already be familiar with ACID, which refers to atomicity, consistency, isolation, and durability. From these sets of guarantees, “isolation” tends to be the most confusing, which is a shame because it is also the one where a deeper understanding directly translates to making better design decisions and more secure SaaS.

I start by explaining what problem transaction isolation is even trying to solve. Then, I explain the standard isolation levels as they appear in the SQL92 standard and are still mostly used today. Then, we talk about the problems with SQL92 levels and how Postgres handles isolation and these problems today. This talk will illustrate isolation-level problems and solutions with many SQL examples involving my cats and my attempts to track their eating.

Transcript

1. Gwen Shapira, co-founder, Nile Date April 2024
2. 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
3. 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
4. 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;
5. 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
6. 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';
7. 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.
8. Serializable isolation is the “gold standard”. But why?
9. ANSI SQL92 isolation levels
10. 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;
11. Why would anyone ever want to do this?
12. 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;
13. In addition to inconsistent reports, this leads to the famous write-after-read problem
14. 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;
15. 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;
16. 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 );
17. 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 );
18. 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
19. 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;
20. Problems with SQL92 Standard
21. Some isolation levels no longer make much sense. Let’s take “Repeatable Reads”. Why would it allow for phantom rows?
22. Microsoft Research, 1995 - new anomalies
23. What Postgres did (9.2+):
24. How does Postgres do this?
25. 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
26. 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;
27. 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
28. 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 0 Luke 19
29. 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
30. 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
31. 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
32. This is how snapshot isolation isolates
33. 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()
34. What happens to old versions?
35. Performance implications
36. 1. Transaction take locks - explicit and implicit. Operations can block on locks.
37. 2. Repeatable read and serializable levels have extra checks and monitors for conflicts.
38. Repeatable Read / Serializable don’t take additional locks. But they can cause Serialization Errors
39. 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.
40. 3. In high concurrency systems, serialization errors lead to rollbacks and retries. Which can be a performance issue.
41. 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
42. Understanding transaction isolation levels leads to better reasoning about application behavior and better tradeoff decisions.
43. Follow us on: X (Twitter) twitter.com/niledatabase Discord discord.gg/8UuBB84tTy Linkedin https://www.linkedin.com/company/niledatabase/ twitter.com/gwenshap