Key difference between PostgreSQL and MySQL (InnoDB) regarding insertion

Key difference between PostgreSQL and MySQL (InnoDB) regarding insertion

First of all, both PostgreSQL and MySQL (InnoDB) rely on MVCC, but the implementation is different.

PostgreSQL relies on the concept of multiple tuple versions and marks outdated tuples to be cleaned later by the vacuum process if they are no longer needed by any transaction. On the other hand, MySQL relies more on the concept of the undo log, which essentially copies the old tuple into this log and replaces it with the new tuple.

This difference has an impact on how data manipulation is handled in different forms. Today, I’ll focus on insertion.

To insert a tuple in PostgreSQL, a Row Exclusive lock is assigned to the newly inserted row. Row Exclusive locking means that it will prevent anyone else from modifying or even reading the data within that row.

However, it is somehow unnecessary to have a lock on the newly inserted row, considering that PostgreSQL uses MVCC. This means that the record won’t affect any other "cautious" transactions under the proper isolation level. (Perhaps you can consider phantom reads in this context).

Imagine we have two transactions, and let's say we have a table that contains a primary key — this PK should always be unique. I’ll refer to the first transaction as A and the second as B.

• Transaction A inserts a new row with id = 1.
• Transaction B inserts a new row with id = 2.
• Transaction A inserts a new row with id = 2.

Let’s pause for a second. Here’s what happens: Transaction A will go into a blocking state, waiting for a decision from transaction B. If B commits, transaction A will throw a duplicate key error. If B rolls back, transaction A will exit the blocking state and continue normally.

Now, let’s assume both transactions continue. Transaction A is now blocked. Fine. Transaction B has the command:

• Transaction B inserts a new row with id = 1.

A deadlock occurs at this point, but the transaction that caused the deadlock (B) will be rolled back, meaning B will not be granted the necessary resources. Transaction A will continue without issues.

How does MySQL handle insertion?

MySQL uses a different technique called insert intention locks, which work on the index structure. This differs from the exclusive lock used in PostgreSQL, which is stricter and operates on the tuple level. While exclusive locks ensure the integrity of the table by preventing index conflicts, they also block anyone who needs to read or update that tuple.

Insert intention locks are more lightweight because they operate on the index level, focusing on preventing concurrency issues that may cause duplicate index entries.

You can test this behaviour well by using the read uncommitted isolation level in both databases. In this case, the isolation level will prevent reading uncommitted data.

Thus, PostgreSQL prevents reading or modifying uncommitted insertions because of the exclusive lock, which acts as a guardian over the table.

Final thoughts:

I’m not claiming that one approach is better than the other. This is simply an observation. Business needs vary, and depending on your project, one of these approaches might suit you better.

Finally, There’s no "bad" or "good" solution in software engineering.