Isolation Levels

Introduction

Databases generally have several transactions in-flight at the same time, however, these transactions aren’t aware of each other and the database must ensure that one transaction doesn’t step on another transaction. The ability of a database to execute a transaction as if there’s no other transaction executing at the same time is referred to as database isolation.

Unfortunately, there’s no standard definition of the various named isolation levels. Different vendors implement isolation levels differently and the guarantees made for a particular isolation level may not be consistent across different brands of databases. Database vendors generally follow and implement the isolation levels defined in the SQL standard, which is based on System R’s 1975 definition of isolation levels. The SQL standard’s definition of isolation levels is not implementation independent and some literature go so far as to call it imprecise and ambiguous.

In general isolation levels aim to prevent writes and reads of concurrent aborted or temporary transactions while trading off isolation guarantees for improved performance. In the context of distributed systems, isolation levels become more complicated, for instance the serializable isolation level takes on different variations such as one copy-serializability (1SR), strict serializability (strict 1SR), update serializability etc. For this lesson, we’ll focus primarily on database isolation levels.

Weak Isolation

Two transactions can step over each other when both of them work on the same data, e.g. one transaction reads a row while another updates the same row at the same time. If transactions act upon disjoint sets of data, we can safely run them in parallel.