Database Intermediate Series: SQL Isolation Levels Internals

In our last post, we talked about Database Isolation Levels and how different Isolation Levels allow us to balance the trade-offs between consistency, performance, and concurrency.

Now, let’s examine how databases can provide different isolation levels that allow us to operate with a high degree of concurrency and consider how these would be implemented internally. But, lets cover a core concept which will help us understand the internal implementation first -

MVCC(Multi-Version Concurrency Control)

MVCC is a concurrency control method that provides each user connected to the database with a “snapshot” of the database at a specific point in time.

MVCC operates by keeping multiple versions of data items within the database. These versions are created through transactions, which are attempting to modify data.

Advantages of MVCC:

1. Read operations do not block write operations, and vice versa. This means the database can handle a high volume of transactions without significant concurrency-related performance degradation. If you did not have MVCC, you would have to use locks to ensure read and write don’t conflict.
2. Since different transactions operate on their versions, the overhead of managing locks is significantly reduced.