What is the Multi-Version Concurrency Control technique in PostgreSQL?

3

What is Multi-Version Concurrency Control (MVCC) in PostgreSQL and how is it working?

    
asked by anonymous 27.03.2017 / 15:42

1 answer

6

It is a technique where nothing is written above. Every change in the data is created a new instance of it in another location and is updating the references to the new location. This way you do not need locking when typing, all readings can access the old data without problems because it is guaranteed that it is not being changed, other writes do not affect what this transaction is doing.

MVCC provides insulation and facilitates consistency and atomicity of ACID and in some cases can facilitate durability . It is a simple and efficient mechanism if well implemented. Each transaction is fully independent and free of side effects.

Obviously you need some form of garbage collector to reuse the pages of data and indexes that are no longer being used by some transaction.

In other words, every database object is immutable , it's like the string of many languages, you can create a new one, but you can not tinker with the existing one, the new one can be referenced by the same location as before, looking like it's the same string .

The technique does not eliminate all management needs. If two transactions are occurring simultaneously there is a case where only the old data is desired, but there is a situation in which the data already modified in the other transaction must be seen, so it has a configuration that allows access to the new data.

To give more detail would need to explain all database operation, page separation, binary tree what will not fit here.

Wikipedia .

Presenter of the maintainer .

    
27.03.2017 / 15:57