Anomalies: Snapshot Isolation

This post starts a short series about different anomalies you may run into using modern databases or other storage systems.

Snapshot Isolation to the rescue

Imagine a system that frequently deals with database locks and transactions that run much too long because of the locks being taken. Imagine that someone applies a magic fix, simply changing the isolation level to the snapshot isolation. Finally, this app is working, throwing an exception from time to time. Owners are happy, until they find, that somehow users are able to write more data than are allowed to write. The investigation starts.

What are you made of, Snapshot Isolation?

If you wonder what Snapshot Isolation means, the description is quite simple. Instead of having locks on rows and checking whether or not a row can be locked/updated,etc, every row is now versioned. To simplify, imagine that a date is added to every row when it’s modified somehow. Now, whenever a transaction starts, it has a date assigned, that creates a boundary for seeing newer records. Consider the following example:

  1. BEGIN TX1
  2. BEGIN TX2
  3. TX2: INSERT row1 INTO tableA
  5. TX1: SELECT * from tableA

the last statement won’t return row1 as it was committed after transaction TX1 started. Simple and easy, right? You can read only rows that were committed before you. What can go wrong then?

Write skew

Now imagine a blogging service, that allows only 5 posts per one user. Let’s consider a situation when a user has two employees entering posts for him/her. Additionally, let’s assume that there are already 4 posts.

  1. BEGIN TX1
  2. BEGIN TX2
  3. TX1: SELECT COUNT(*) FROM Posts returns 4
  4. TX2: SELECT COUNT(*) FROM Posts returns 4
  5. TX1: INSERT post5a INTO Posts
  6. TX2: INSERT post5b INTO Posts

As you can see, both transactions read the same number of posts: 4 and were able to add one more. Unfortunately, for the owners of the portal, now, their users know, that by issuing multiple requests at the same time, they can do much much more without paying for additional entries.

This error is called the write skew.


The first mitigation you might think about is a simple update on the number of posts already published. Once a conflicting write is found, the database engine will kill the transaction. Another one could be replacing a record with itself. This still, qualifies as a conflict, and again, will kill the transaction committed afterwards. Are there any other tools?

Yes they are, but they are not available in every database. There’s a special isolation level called Serializable Snapshot Isolation (SSI) that is less than 10 years old. It’s capable of automatically checking whether or not two transactions overlap in a way, that one could impact another. One of the databases that is capable of doing it, is PostgreSQL. Another one is the open source Spanner clone, called CockroachDB. Interestingly it defaults to SSI as it’s described in here.

Wrapping up

As always, don’t apply things automagically, especially, if you deal with isolation levels. If you select one, learn how does it work and what anomalies are possible. When thinking about Snapshot Isolation, consider databases that support you with Serializable Snapshot Isolation, which removes the burden of updating rows “just-in-case” and can actually prove correctness of your operations.

DevConf 2017

The first, 2017 edition of DevConf has ended. Both, on the social level and the content level it rocked. Also, this was the very first time I’ve ever given a presentation in English.

Top 3 talks

I haven’t seen all of the talks as I was preparing for mine. Frankly speaking, it’s quite hard to both enjoy a talk and be stressed before yours so I chose a third option and drank a few cups of delicious coffee (no stress, no attendance, just coffee). If I had to choose top 3 talks (in no particular order) these would be:

What is .NET Standard? by Adam Ralph

An interesting presentation showing the mess before .NET Standard and the beauty of the common interface to bind all the platforms together. Good jokes about graphs, some numbers and, the last but not least, insightful journeys into type-forwarding that enabled this whole thing to work.

Kudos to Adam.

Domain Driven Design: The Good Parts by Jimmy Bogard

A very interesting presentation of removing bad parts from DDD, making it focused on things that matter the most, which is…. (you better watch the presentation). It’s worth to add that it was beautifully and naturally storyfied with real projects Jimmy was involved in.

Kudos to Jimmy.

“Cargo Cults” in Building Modern Software Systems by Sebastian Gębski

This is the presentation where some people could feel offended. Or terrified. Or both. Sebastian dissected industry standards and mechanisms showing, how rotten it is on multiple levels. It’s a really heavy topic and before this presentation I didn’t know things like “Point of View” and others. A very eye opening presentation.

Kudos to Sebastian.