Understanding Serializable Snapshot Isolation in Databases

Sep 16, 2023 · 6 min read

Hey everyone, it’s alanturrr1703 back again! 😄 In today’s blog, we’re diving into Serializable Snapshot Isolation (SSI)—a powerful and advanced database isolation level that offers the benefits of both Snapshot Isolation (SI) and Serializability.

If you’ve been following along with my blogs about isolation levels and concurrency control, SSI is the perfect next step in understanding how modern databases handle transactions efficiently and safely. Let’s jump right in!

What is Snapshot Isolation?

Before we get to Serializable Snapshot Isolation (SSI), let’s quickly revisit Snapshot Isolation (SI).

In Snapshot Isolation, each transaction works on a consistent snapshot of the database as it was at the moment the transaction began. This means that during a transaction, you won’t see any changes made by other concurrent transactions. You work on the data as if no other transactions are happening. However, once your transaction is done, all your changes will be visible to other transactions.

Snapshot Isolation solves problems like dirty reads and non-repeatable reads, but it isn’t perfect. It can still suffer from issues like write skew, where two transactions make conflicting updates based on the same data.

What is Serializable Snapshot Isolation (SSI)?

Serializable Snapshot Isolation (SSI) takes Snapshot Isolation and makes it even stronger by ensuring serializability—the gold standard of database isolation.

With Serializability, transactions behave as if they were executed one by one in some serial order, even though they might actually be running concurrently. SSI achieves this while allowing high concurrency like Snapshot Isolation, but it avoids dangerous anomalies like write skew that can still occur in plain Snapshot Isolation.

How It Works:

Just like in Snapshot Isolation, each transaction operates on a snapshot of the database.
However, Serializable Snapshot Isolation introduces additional checks to ensure that if two transactions would cause an inconsistency by running concurrently, one of them will be rolled back. This ensures the final result of the transactions is the same as if they had run in serial order.

Example of Serializable Snapshot Isolation

Let’s revisit our classic banking example:

Transaction 1: Transfers ₹500 from Account A to Account B.
Transaction 2: Transfers ₹300 from Account A to Account C.

Both transactions start at roughly the same time, reading the balances of Account A. Under regular Snapshot Isolation, both transactions might proceed based on outdated information, potentially leaving Account A with an inconsistent balance. However, with SSI, the database checks for conflicts, and if it detects that both transactions are modifying the same data (Account A’s balance), it will abort one of them, ensuring serializability.

This way, the final outcome is the same as if the transactions had run one after the other, maintaining consistency in the database.

Key Benefits of Serializable Snapshot Isolation

1. Eliminates Write Skew

In standard Snapshot Isolation, write skew can occur when two transactions read the same data and make decisions that conflict with each other. For example, both transactions might see enough funds in an account and decide to withdraw money, leaving the account overdrawn once both complete.

In SSI, write skew is prevented by checking for conflicts and aborting one of the transactions if necessary.

2. Highly Concurrency-Friendly

SSI allows multiple transactions to run concurrently without requiring them to lock resources as strictly as Strict Two-Phase Locking (2PL) would. This leads to better performance while still ensuring serializable outcomes.

3. Avoids Phantom Reads

Phantom reads occur when a transaction reads a set of rows that satisfy a condition, but another transaction inserts or deletes rows that change the result of the original query. SSI ensures that such anomalies don’t occur by detecting and resolving conflicts, making it as safe as the serializable isolation level.

4. Efficiency over Strict Serial Execution

While serializability is traditionally guaranteed by strict locking, which can slow down performance, SSI provides a smarter approach. It allows transactions to proceed concurrently but steps in to roll back conflicting transactions only when necessary. This way, SSI provides serializable isolation without the heavy performance penalty of strict serial execution.

How SSI Differs from Other Isolation Levels

Isolation Level	Description	Example Scenario
Read Committed	Only sees committed data, but allows non-repeatable and phantom reads	Reads may see different data during a transaction
Snapshot Isolation	Each transaction operates on a snapshot of the data but may suffer from write skew	Prevents dirty reads but allows write skew
Serializable	The strictest isolation level, simulates transactions running one by one	Highest data integrity but can be slow due to locking
Serializable Snapshot Isolation (SSI)	Combines the benefits of Snapshot Isolation and Serializability	High concurrency with no write skew or phantom reads

SSI vs Traditional Serializable Isolation

Traditional Serializable Isolation often uses strict locking (e.g., Two-Phase Locking) to ensure transactions behave as if they were executed in serial order. However, locking can cause performance bottlenecks, especially in high-concurrency environments, because transactions must wait for locks to be released.

SSI avoids this by allowing transactions to run concurrently while still providing serializable results through conflict detection. This makes it more efficient for write-heavy or read-heavy systems where a high degree of concurrency is needed.

How SSI Works Behind the Scenes

The core mechanism that makes SSI possible is conflict detection. Here’s how it works:

Tracking Dependencies: While transactions run, the database tracks dependencies between them. If two transactions are accessing the same data and making changes that could lead to a conflict, the system notes this.
Conflict Detection: If the database detects a conflict (e.g., both transactions are trying to write to the same row), it aborts one of the transactions. This rollback ensures that the transactions remain serializable.
Retry Mechanism: The transaction that is aborted is typically retried automatically. This means the user doesn’t have to handle the rollback manually; the database ensures that the transaction is re-executed until it completes successfully without conflicts.

When to Use Serializable Snapshot Isolation?

While SSI offers both strong consistency and concurrency, it’s most useful in scenarios where you need high reliability without sacrificing performance. Here are a few use cases:

Financial Systems: Any system handling critical financial data, such as banking or stock trading, benefits from SSI. It ensures no inconsistent updates or withdrawals happen simultaneously.
Inventory Systems: In systems managing inventory levels, concurrent transactions updating stock can lead to over-sales or stock depletion. SSI prevents conflicting updates.
High-Concurrency Applications: If your application has many users or transactions happening simultaneously, and you need both performance and consistency, SSI is ideal. It gives the same level of data safety as Serializable Isolation but with better concurrency.

Wrapping It Up

Serializable Snapshot Isolation (SSI) is a powerful isolation level that combines the performance benefits of Snapshot Isolation with the strong guarantees of serializability. By detecting and resolving conflicts during concurrent transactions, it ensures that the database behaves as if transactions were executed one by one, without the performance bottlenecks of traditional serial execution.

SSI strikes the perfect balance between consistency and concurrency, making it a go-to solution for many modern high-performance databases.

I hope this post helped clarify the magic behind SSI! Until next time, keep exploring the world of databases. 🚀