Databases: Differences between different database variants

Introduction #

While I was working on a solution for a database migration, I learned that the same solutions may not work on different databases. It led me down this rabbit hole where I started to discover the differences between different SQL implementations (e.g: MySQL, PostgreSQL, etc.)

In this blog post, I will be sharing what I have discovered during my research, and it will mainly focus on isolation and the behavior affecting isolation.

Intro to Relational Databases #

Before we dive into the differences, let us go through the main selling points of Relational Databases. Most SQL systems implements ACID to ensure that the database remains consistent and correct.

ACID stands for:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Let us go through what they mean.

Atomicity #

Atomicity means that any transactions within a database must either

1. All be executed
2. None of them are executed

Implementations #

1. Undo : Undo logs store the information required to roll back the action.
2. Undo Segments: Undo segments are physical storage areas that contain information to rollback transactions, crash recovery, etc.
3. MVCC Snapshots: Snapshots are taken before an action is completed, these snapshots can be used to roll back the current transaction.

Consistency #

Consistency refers to the state transitions within the database. This guarantee ensures that the database is always in a consistent state.

However, the rules for a consistent state is still defined by the user. It is still key for the user to come up with valid constraints.

Isolation #

Isolation controls which effects of concurrent transactions are visible to a transaction.

The different levels of isolation aims to solve these mains issues

1. Dirty read: reading uncommitted data written by another transaction (which may later be rolled back).
2. Non-Repeatable read: Reading the same data 2 times but getting different values.
3. Phantom read: Doing the same query twice and getting a different number of rows.

The SQL standard describes four levels (from weakest to strongest):

• READ UNCOMMITTED — may see uncommitted (dirty) changes.
• READ COMMITTED — prevents dirty reads, but non-repeatable reads and phantom reads are still possible.
• REPEATABLE READ — prevents dirty and non-repeatable reads by providing a stable snapshot for each transaction, but the standard allows phantom reads in some implementations.
• SERIALIZABLE — provides the strongest guarantee: transactions behave as if executed serially; dirty, non-repeatable and phantom reads are prevented.

Each of this isolation aims to solve 1 of the problems. However, for each level we go up to solve the problem, the lower the performance and the number of supported concurrent writes.

Implementations #

There are a few main approaches to Isolation.

1. Multi Version Concurrency Control (MVCC) / Snapshot Isolation (SSI)
2. Pessimistic Lock
3. Optimistic Lock

Multi Version Concurrency Control (MVCC) / Snapshot Isolation (SSI) #

Every tuple has a version number, the tuple with the greatest version number can have a read operation done simultaneously. For writes, it is only done on a copy of the data. While writes are happening (before committing), all read users will still see the previous version Upon completion of writing, the version tuple is bumped up and is used for future reads / copy for future writes.

As we can see from the diagram above, there is a snapshot for each read. For each read, if it touches the same table multiple times, the read for each table will be the same.

Pessimistic Lock #

A Pessimistic lock is a method of locking where we assume that data conflicts / inconsistencies are likely. Under this scheme, we always acquire the locks upfront before performing any read / write operations. The same operation from the above will look something like this.

During each of the steps, each user has exclusive access to the database to do the reading / writing. In this implementation, we guarantee that the data the reader/writer is accessing is the latest version. The tradeoff is that it will reduce the number of concurrency.

The locking mechanism can also be split into row level locks and table level locks.

1. For row level locks, readers that read from the same table multiple times may have more/less resulting rows depending on the concurrent writer transaction
2. For table level locks, concurrency is further reduced, only 1 reader/writer can access the table at any 1 time.

Optimistic Lock #

An Optimistic lock is a method of locking where we assume that the data does not conflict. Under this scheme, we do not acquire the lock and perform the read/write operation first. Before we start the read/write, we take note of the updated time of the row. Before we complete the write commit, we check the updated at time is updated before.

If the updated time has been changed, we discard the current operation and retry with a new operation. Otherwise, we will commit the operation.

Durability #

Durability ensures that committed data will survive in the event of power loss. There are 2 implementations when it comes with durability

Implementations #

Database behavior #

There are many different implementations of databases that we can use. Let us explore the different types of database implementations.

In this blog post, we will explore the differences between these implementations:

1. MySQL
2. PostgreSQL

MySQL #

MySQL is an open source relational database management system. It is flexible as we can plug in and change the engine depending on our needs.

The default engine for MySQL is InnoDB for ACID workflows. Other engines that can be plugged into MySQL mainly consider different tradeoffs between:

1. ACID Properties
2. Speed
3. Other special features

If I do not want transactions but want the database to go very fast, we can go with the MyISam. However, the trade-off for this engine is that it does not support transactions.

Note: This is not recommended for important data.

MySQL with InnoDB uses MVCC to enforce the isolation level by default. However, we can also specify the required levels of isolation using the SET TRANSACTION syntax

-- Sets the transaction to use the SERIALIZABLE isolation level to prevent phantom reads.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

While Data Manipulation Languages (DMLs) are supported by transactions, InnoDB does not support wrapping Data Definition Languages (DDLs) within transactions.

Let’s go through what happens during a transaction in each of the cases.

-- This example is adapted from the MySQL website.
BEGIN TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1; -- Data is selected based on the MVCC Snapshot
UPDATE table2 SET summary=@A WHERE type=1;       -- Table 2 is updated but the information is not commited
COMMIT;                                          -- Data is only committed here

In the DML case above, the data modified during the transaction is only committed at the end of the transaction when the COMMIT command is invoked. There are no hidden commits within the transaction,

BEGIN TRANSACTION;
ALTER TABLE user_info ADD email text; -- DDL is autocommitted by default.
ROLLBACK;                             -- Does nothing

In the DDL case above, the ALTER TABLE statement is auto-committed and cannot be rolled back. This also applies to any other DDLs.

For any solutions that executing any DDLs within transactions and allowing rollback, MySQL with InnoDB will not be able to support it. For such cases, please use PostgreSQL instead.

There are other keywords that cannot be rolled back. You can find the full list here (implicitly committed) and here (cannot be rolled back)

If there are multiple conflicting transactions at the same time resulting in a deadlock, one of them will be rolled back with an error and the other will continue execution.

PostgreSQL #

PostgreSQL is another open source relational database management system. However, unlike MySQL, it does not have pluggable engines. Instead of pluggable engines, it has extensions. Extensions modify the behavior of PostgreSQL, you can find the full list of the extensions here.

The default isolation level in PostgreSQL is Read Committed.

On higher levels of isolation, PostgreSQL makes use of Multi-version Concurrency Control (MVCC) with Write ahead logs (WAL) for isolation. This means that each query hitting the database see a particular version of the data and ensures repeatable reads and phantom reads (the repeatable read isolation level in PostgreSQL prevents phantom read)

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... perform operations ...
COMMIT;

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... perform operations ...
COMMIT;

Similar to MySQL, we can specify the transaction level that we want when starting a transaction similar to the MySQL example above. There is also a command to specify the default isolation level.

PostgreSQL will also error if there are multiple conflicting transactions at the same isolation level when performing at Repeatable Read level and above.

ERROR: could not serialize access due to concurrent update

Do note that for repeatable read level of isolation, be prepared to retry as PostgreSQL will return an error when it encounters a concurrent update.

ERROR: could not serialize access due to read/write dependencies among transactions

Similar to the repeated read level, if there is another conflicting serializable transaction, one of the transactions will fail with the error above. If there are such errors, we will have to retry at the application level which can incur higher write costs.

When using transactions within an isolation level, there are some additional considerations we can take to improve performance

1. Using READ ONLY keywords when possible.
2. Using a connection pool to reduce the number of transactions with transactions
3. Use as little statements in the transaction as possible.
4. Tune other PostgreSQL settings based on the requirements and data access pattern.

Most DDLs within PostgreSQL can be wrapped in transactions and can be rolled back. However, do take note of some edge cases which does not support this behavior like CREATE INDEX (CONCURRENTLY). Please check if your PostgreSQL statements support rollback before running it in production.

MySQL vs PostgreSQL #

This chart shows the difference between MySQL and PostgreSQL

Conclusion #

There are a lot of differences between different variants of SQL implementations. Depending on what you want to do, there may be some caveats that you need to take note of.

Useful Links #

1. MySQL MVCC
2. PostgreSQL SSI
3. PostgreSQL MVCC
4. What is MVCC
5. MySQL statements that cannot be rolled back
6. MySQL statements that cause implicit commit
7. MySQL transaction level isolation types