MVCC

MVCC (Multi-Version Concurrency Control) is a database feature. It lets many transactions read and write the same data at once. They don't block each other.

Different from locking

MVCC just stores multiple versions of the same object. It then allows parallel reads based on when the read started. By contrast, locking is only about safe parallel writes.

Also known as repeatable read and snapshot isolation

• Repeatable Read - You can run the same query many times in a transaction. The value stays the same. This holds even if a parallel transaction changed it.
• snapshot isolation - Each transaction sees its own snapshot of the database state.

How MVCC works

1. Every database keeps the correct order of transactions it runs. This is part of the database metadata.
2. Every transaction then also gets a transaction ID.
3. With this ID, the engine knows which transactions are visible. It also knows which ones aren't.
4. A transaction may have a lower ID than the current one. But if it isn't yet committed, its changes stay hidden from the current transaction.

Data written to files before commit

The transaction's data is written to the files before the commit. The main history stays in the WAL or REDO log files. The engine uses this to decide if the data is visible to a transaction.

transactions exist always

You may not write 'BEGIN TRANSACTION' in your SQL. Still, a transaction is always created to run the query.

How indexes work?

With MVCC, data has many versions. The index needs special care. Some databases let one index point to many locations. The index must then be queried again.

Some databases just create many index tree roots, one per version.