What is Multi Version Concurrency Control (MVCC)

In this post, I will explain you very basic about Multi-version concurrency control and explain you to how MVCC works.

Guys, you can find lots of theory and research on MVCC but here I am going to explain this theory with easy examples.


What is Multi-version Concurrency Control ? :

Very simple, ” One record has multiple version “. 

E.g,

User A —-> inserted one record with ID 1. (stored this into one data page.)

User B —-> update record for ID 1.

Guys, what are you thinking now ?

One record is inserted and update by two different users.

First inserted, old version of the record changed by the new update statement and become a new version  of the record. But the new version of the record is separately added into data page.

Whatever different changes occur on the same record, MVCC stored different version for each change. This is not overwritten process, but this stored all different versions of records.

E.g,

You write one sentence in your book.

The Sentence is : ” I am good database developer “.

Now change this sentence.

The Sentence is : ” I am good database administrator “.

Now for this change, you can erase this sentence and change “developer” to “administrator”.

Other database engine also doing the same thing as you do in real life, but MVCC stored this two sentence with different versions.

Old Version : ” I am good database developer “

New Version : ” I am good database administrator “

By Default, User can only select new version of the data.

This is simple definition and example of MVCC.

Why MVCC ?:

Let’s first discuss concurrency, more than one user at the same time accessing the database or records for different purpose.

MVCC provides concurrent access to the database.

Let’s imagine, one user is reading a record and the second user is trying to update a same record.

Now this is possible that readers will see inconsistent or incomplete data. In term, this called as dirty read.

Else, You can also apply lock, in which all readers have to wait until the update is completed.

But this lock and dirty read is not a proper solution because lock can degrade performance and dirty read is not showing proper data.

In this situation MVCC is the best solution, where readers can see old committed version of data and once the update is finished, then the reader can see a new committed version of the data.

E.g.

The reader can read this sentence ” I am database developer. “

After update reader can read the new sentence ” I am database administrator. “


Drawbacks of MVCC :

The main drawback of MVCC requires more space to store different version of the data.

Yes, later you can remove old version of data by specifying cleaning command.

In the MVCC, the same data causes an update conflict because same version of the row can be updated by two different transactions.


Most MVCC database systems:

ORACLE

PostgreSQL

MySQL (only innodb engine)

You can also achieve MVCC in Microsoft SQL Server using ” Snapshot Isolation Level “.


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

4 Comments on "What is Multi Version Concurrency Control (MVCC)"

Notify of
avatar
Sort by:   newest | oldest | most voted
Mauro
Guest

Do you know how indexes are maintained when an update occurs? Does the index remain pointing to the old version of the row and this address points to the new version? Or the index is updated as part of the update process?

valtih1978
Guest
Wikipedia says “If transaction Ti wants to Write to object P, and there is also another transaction Tk happening to the same object, the Read Timestamp RTS(Ti) must precede the Read Timestamp RTS(Tk), i.e., RTS(Ti) < RTS(Tk), for the object Write Operation (WTS) to succeed." That is, only single user can update anything in the database. For if we have two transactions, none has RTS lower than the other, no matter which DB item we look at. None can write anything. This seems strange. Moreover, they violate this rule in their example when they show that long-running T2 reads values… Read more »
wpDiscuz