The core idea behind MVCC is to allow readers and writers to operate concurrently without unnecessarily blocking each other, which is achieved by versioning rows. Readers can see a consistent snapshot of the data whilst writers create new versions of it, avoiding the need to block and wait. If you have used SQLite, you'll know that it's single writer, whereas Turso in its re-write of SQLite has supporting MVCC as a goal.

Isolation levels in MVCC are particularly fascinating due to a "mix 'n' max" approach you could take with them based on your business problems. Postgres for example defaults to read committed as its base isolation level unless explicitly specified. I was although surprised Postgres doesn't have READ UNCOMMITTED due to the pain of implementing it which is totally understandable.

Isolation levels

• READ UNCOMMITTED -> allows reading uncommitted changes made by another transaction
• READ COMMITTED -> only allows committed changes to be read. Subsequent statements within the same transaction can see changes committed by other transactions
• REPEATABLE READ -> re-reading rows gives the same view within the transaction even if another transaction modified it
• SERIALIZABLE -> guarantees that the outcome of concurrently executing transactions is equivalent to some serial execution of those transactions

What's interesting about READ COMMITTED is that it's totally a reasonable default since not many applications would care for a lot of the 'problems' that it can't save you from such as inconsistent snapshots across concurrently running transactions, thus it exposes a lot more performance than the REPEATABLE READ and SERIALISABLE levels.

Good defaults in production would be a must and tuned to your own needs, especially around lock timeouts. I haven't looked but I bet there's some good reads on how to measure this.

With defaults two competing transactions looking to update the same row will have lock contention, resulting in one winning and doing its work whilst the other halts once its update command was submitted and must wait for the row lock to be acquirable.

An interesting note here that I learnt from Postgres is that once the transaction can do its update, Postgres will re-check the predicate(s) to ensure that they aren't violated

Now let's say we have some lock timeouts configured based on our requirements, this is where things get interesting and what you'd want to pay special attention to.

row lock -> timeout if it can't be acquired in time

idle txn lock -> timeout if there is no movement in the txn

deadlock -> someone is timed out and loses allowing another to continue

The idle transaction lock is perhaps the most critical one (I think, I could be wrong) to grasp for your application because this is going to be what occurs when during a transaction the database must wait for something your application is doing e.g. making an API call to something that's taking longer than expected and COULD ALSO timeout

The other two locks are purely focused on how the database handles its own lock contention across competing transactions, which is also something that can occur at any isolation level (especially the deadlock one!!!!!!).

Read skew and write skew are another two cool problems that aren't exactly the fault of the database and require careful consideration around what you're doing with N row updates that could concurrently occur.

• Read skew: a transaction observes data from inconsistent points in time, potentially seeing a combination of values that never existed together in a consistent database state
• Write skew: two concurrent transactions independently make valid decisions based on their snapshots, but their combined writes violate a business constraint

If we think about what is going on with read skew, we're doing a SELECT (no exclusive lock on the rows) and then making our changes. The time between Txn A and Txn B's reads and the first write by Txn A can create an inconsistent view of the data from Txn B's perspective.

This is where you'd want to start bumping up the isolation level to REPEATABLE READ so that Txn B has a consistent snapshot regardless of when Txn A commits. It's perfectly reasonable to only set the isolation level for your transactions only that may be impacted by read skew.

Write skew is unescapable since it's a business level problem (at heart) that the database can only save you from at the highest isolation level. My example is very simple (A or B must be 1) and if anything, READ COMMITTED could theoretically stop it in a race condition sorta way if Txn A commits before Txn B's reads but I wouldn't gamble on that. REPEATABLE READ is going to be exposed, and SERIALISABLE will detect it through the dependency graph.

As mentioned before, the database doesn't know why it wasn't able to commit (hence the hint message about possible success on retry) but it saves you by chance if a mistake was about to be made.

There's a lot more that I haven't mentioned here (e.g. phantoms) but these are all good to know.

Postgres Specific - Write Amplification

To round this out, write amplification is an anomaly that is database dependent, and in the case of Postgres, it's a problem that has hurt companies at the highest level of scale.

To better understand, it helps to know that tuples in Postgres are stored differently to e.g. SQLite's management of them. Postgres tuples are retrieved via their ctid whereas SQLite (generally) uses a row id (I think MySQL's InnoDB does the same, but I haven't researched much on MySQL).

• ctid -> direct access to the tuple (main table and indexes)
• rowid -> indexes must traverse main table to find the location of the tuple

Because of MVCC, Postgres will create a whole new tuple that is the latest version, which everything that held the (now previous) ctid must be updated to point at the new ctid. SQLite on the other hand doesn't have this problem since the indexes are pointing at the rowid value which is stable (hence the tradeoff of needing to traverse the main table for the tuple location).

So now imagine you're in a situation where you have a table with 50 indexes, that means every update of an existing tuple is going to cause 50 indexes to update their reference vs SQLite only doing 1 (the main table). This is where the amplification occurs and whilst it's not an MVCC specific problem, it's an indirect consequence of how Postgres deals with tuples in relation to MVCC.