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.