# Snapshot Isolation vs Serializability

DevFeed: [Snapshot Isolation vs Serializability](<https://devfeed.tech/articles/snapshot-isolation-vs-serializability-12567.md>)

Original publisher: [Read original article](<http://brooker.co.za/blog/2024/12/17/occ-and-isolation.html>)

Author: Marc Brooker

Published: 2024-12-17T00:00:00Z

Content type: article

Language: en

Sources: [Marc Brooker's Blog](<https://devfeed.tech/sources/marc-brooker-s-blog.md>), [Marc Brooker's Blog](<https://devfeed.tech/sources/marc-brooker-s-blog-2.md>)

Topics: [DSQL](<https://devfeed.tech/topics/dsql.md>), [Transactions](<https://devfeed.tech/topics/transactions.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [systems](<https://devfeed.tech/topics/systems.md>)

Tags: [database](<https://devfeed.tech/tags/database.md>), [dsql](<https://devfeed.tech/tags/dsql.md>), [fundamentals](<https://devfeed.tech/tags/fundamentals.md>), [internals](<https://devfeed.tech/tags/internals.md>), [sql](<https://devfeed.tech/tags/sql.md>), [transactions](<https://devfeed.tech/tags/transactions.md>)

## AI overview

This article examines snapshot isolation and serializability in database systems, using read-write and write-write conflicts to explain transaction behavior. It focuses on write skew, read sets, write sets, and the trade-offs among SQL isolation levels, with Aurora DSQL providing the motivating context.

## Source excerpt

Snapshot Isolation vs Serializability Getting into some fundamentals. In my re:Invent talk on the internals of Aurora DSQL I mentioned that I think snapshot isolation is a sweet spot in the database isolation spectrum for most kinds of applications. Today, I want to dive in a little deeper into why I think that, and some of the trade-offs of going stronger and weaker. This post is going to be a little deeper than the last few. If you're not deeply familiar with SQL's isolation levels, I recommend checking out Crooks et al's Seeing is Believing: A Client-Centric Specification of Database Isolation, Berenson et al's A Critique of ANSI SQL Isolation Levels, or Adya et al's Generalized Isolation Level Definitions. Specifically, I'm going to talk about one very specific mental model of transaction isolation: read-write conflicts, and write-write conflicts. Let's start our journey with a transaction, T1: BEGIN; SELECT amnt FROM food WHERE id = 1 OR id = 2; UPDATE food SET amnt = amnt - 1 WHERE id = 1; COMMIT; There are a few things to notice about this transaction, when run in a database system that offers interactive (i.e. back-and-forth with the client) transactions: It directly reads two rows from the database, the rows 1 and 2 from the table food. It directly writes one row in the database, the rows 1 from the table food. It starts and ends at different times: starting during BEGIN and ending during COMMIT. Depending on the way that database is implemented, it also likely reads other data (e.g. the system catalog which tells it which tables exist), and may write other data (e.g. a secondary index on the amnt column of food). The forth point here is critical in real systems, but let's ignore it for now and focus only on the first three2. Before we do that, let's introduce a second transaction, the first one's parallel universe clone, T2. We'll also assume these are the only transactions running at this time. BEGIN; SELECT amnt FROM food WHERE id = 1 OR id = 2; UPDATE f