Introduction

Consider multiple writes are happening to your databases at the near same time. Now, imagine those writes are altering the same record sets or adding to the same table. Should it be allowed for those writes to execute in parallel or should they be sequential? Should certain cases prevent overwrites, while allowing parallelism other times?

Back in the ‘90-ies, a SQL standard was invented for the relational databases to address these problems (and many others) - SQL:1992. As a part of the initial specification, SQL:1992 standard defined four levels for transaction isolation:

1. read uncommitted
2. read committed
3. repeatable reads
4. serializable

Even today, when working with the relational databases, we use these different isolation levels to decide on how strict guaranties for writes and reads we need inside our transactions. Here is a simple breakdown on each of isolation types, coming from the least constrained level of isolation - Read Uncommitted and ending with the most strict constrained isolation lever - Serializable.

Read Uncommitted

• Prevents anomalies: none
• Subjected to anomaly: dirty reads, non-repeatable reads, phantom reads

Dirty reads anomaly happens when a database query reads records that haven’t been yet committed. Therefore, the read result is “dirty”. Let’s look at the following example:

The select result from Session 02 will indicate a new party has started, however that change hasn’t been saved due Session 01 transaction rollback (triggered by error occurrence). That means that Session 02 will handle wrong party data that are now faulted.

Usage: when it comes to write intensive operation Read Uncommitted can be a valid choice in which the record result is not much deviant from the actual value. Metrics and reports usually don’t insist on precise real-time results. Similarly, application cases for Likes or Text Messages.

Read Committed

• Prevents anomalies: dirty reads
• Subjected to anomaly: non-repeatable reads, phantom reads

Non-repeatable reads anomaly happens when in a single session we have the same select multiple times, but their results differ based on recent committed transactions in other sessions. We can demonstrate it in the following example:

Because write is performed after read, two results in Session 02 will contain different number of guests. When we implement such scenarios, they will usually be contained in a single transaction and therefore won’t present any risk. For example, we can incorporate read and write in a single command:

BEGIN;
  UPDATE party
  SET guests_number = guests_number + 1
  where id = 101 and guests_number < MAX_GUESTS
COMMIT;

In Postgres, Oracle and MSSQL (and many other SQL servers) default isolation level is Read Committed, since it provides sufficient constraints for the most implementation scenarios.

Repeatable Reads

• Prevents anomalies: dirty reads, non-repeatable reads
• Subjected to anomaly: phantom reads (except PostgreSQL that prevents it)

Phantom reads anomaly is related to INSERT statement. Two selects in a single session can differ in data results due to newly inserted records in recently committed transactions.

It worths noting, that since SQL:1992 things have improved and most of today’s databases uses Multi-version concurrency control (MVCC). Simply put, this allows concurrent writes and reads to operate without blocking each other. Concurrent sessions work with relevant snapshot from the recently committed transactions, and in cases like previous sample where two transactions’ sessions runs in parallel, each would use the snapshot version at the time of their start.

Since each Session is dealing with its snapshot, at the point of the first SELECT, the uncommitted change guest_number = 500 won’t be visible in that SELECT result. Once the Session 01 is committed a new snapshot is created which will be used by any new transactions. The Session 02, however, remains working with the old version of the snapshot, and here is where things become interesting:

• in case of MySQL, the second SELECT statement won’t see any updated changes, but it will see the new inserted row. The second select result will have an extra row with ID 102 compared to the first select result. These records, we like to call phantoms.
• in case of PostgreSQL, the second SELECT statement won’t see any updated changes or the new inserted row. Meaning, the both select results will be identical!

This is due to differences in MVCC implementations. In MySQL, snapshot doesn’t track range changes, so we end up with phantom reads, but in PostgreSQL snapshots are more restrictive.

To prevent phantom reads in databases we can use Serializable isolation level or use read locks on select statement like LOCK IN SHARE MODE or FOR UPDATE:

SELECT * FROM party WHERE status = 'started' LOCK IN SHARE MODE;
SELECT * FROM party WHERE status = 'started' FOR UPDATE;

It worths mentioning that in MySQL Repeatable Reads is the default isolation level.

Serializable

• Prevents anomalies: dirty reads, non-repeatable reads, phantom reads
• Subjected to anomaly: none (? maybeee :) )

In the most abstract level of understanding Serializable is usually considered as serial ordering of transactions. However, known databases work differently. Let’s observe following example:

Similar as in Repeatable Read example, with MVCC support, the snapshot version will be used in each transaction’s session. This time Session 02 is the first to commit the change and a new snapshot is created.

• in case of PostgreSQL, second UPDATE in Session 01, will produce conflict since MVCC with dependency tracking will detect this data has changed elsewhere. In case of the conflict, the transaction in Session 01 will be rolled back and the application logic has to handle retries.
• in case of MySQL, second UPDATE in Session 01 will wait for the lock to be released, which is done after the commit, and proceed with updating the changes without conflict. However, due to use of locks, deadlocks can occur between transactions.

It is important to mention that if the previous example was handled by Read Committed isolation level, no issues would occurred since second update would work with committed data. With this in mind, we can conclude that handling retries or waiting for locks within Serializable level can certainly impact application’s performance.

Conclusion

The described rollback on conflict scenario in PostgreSQL’s Serializable case is known as lost write anomaly. Ever since SQL:1992 was introduced, there have been many research that detected some more anomalies, such as: lost updates, dirty writes, write skews, read skews, fuzzy reads. So, some databases introduced other isolation levels while others introduced new mechanics in existing isolation levels to prevent them.

This post was heavily leveraged on the great and detailed blog by Gwen Shapira that I highly recommend to check. My modest contribution was to try make it more succinctly for a quick reference or a starting point.

References

• SQL:1992
• Transaction Isolation in Postgres, explained - by Gwen Shapira
• Multi-version concurrency control, Wikipedia
• Designing Data-Intensive Applications (Chapter 7) - by Martin Kleppmann