PostgreSQL MVCC source code implementation

PostgreSQL MVCC source code implementation

MVCC is no stranger to every DBA, that is, Multi-Version-Control ). Because the data has multiple versions, the read and write operations are separated to a certain extent, improving the database's ability to process queries per second (QPS ).

User-initiated common query requests (excluding select... For update statement) does not block DML transactions. At the Read Commit transaction isolation level, the query request only reads the data changes of the transactions submitted before the query request, and does not affect the data of the current version;

DML statements operate on the current version. Therefore, read/write splitting is achieved to improve database concurrency.

Different databases have different MVCC implementation methods. The Oracle and MySQL Innodb Storage engines use undo for implementation.

For PostgreSQL databases, there is no undo, so how does PG implement its own MVCC? What are the advantages and disadvantages?

PG uses the xmin, xmax, cmin, and cmax labels of copy tuple and tuple to implement multiple versions.

Xmin: when creating a record (tuple), the record will be updated each time it is updated.

Xmax: When tuple or lock is deleted, the record is deleted. If the record is not deleted, the value is 0.

Cmin and cmax: sequence value that identifies multiple statement commands in the same transaction. Used for version visibility judgment in the same transaction.

1. Let's take a look at the changes in xmin and xmax:

It can be seen that the xmin of the four records is the same, all of which are "390689", which indicates that they were created in the same transaction. In addition, all xmax values are "0", indicating that none of them are deleted. Both cmin and cmax are 1, indicating that the same command was created.

Next, let's update the record with id 1 to see what happens:

After the update, It is not submitted. open another window and query:

We can see that only xmin of records with ID 1 has not changed, and the other three values have changed. xmax has changed to "390691 ".

Then I commit the transaction and query it in the new window:

We can see that after the submission, the record ID is 1, xmin changes to "390691", xmin increases by 1, and xmax changes to 0.

From the above case, we can see from the surface that xmin has increased. But in fact, PostgreSQL does far more at the underlying layer. A new tuple version has been generated at the underlying layer. The xmin of the new tuple version is equal to the xmax of the old version.

The detailed internal will be discussed later.

2. Let's take a look at the cmin and cmax changes:

I initiate a transaction that contains two updates, one record with the update ID value of 2, and one record with the insert ID value of 3:

In the transaction "390694", the values of cmin and cmax increase sequentially. Currently, cmin and cmax are actually the same field.

The source code is defined as follows. CommandId is implemented using union, which is a combo command id.

Therefore, from the above example, the mvcc Implementation of PostgreSQL is relatively simple. You only need to compare the xmin, xmax, cmin, cmax in the tuple header with the current xid to obtain the tuple's visibility into the current query during the scan tuple.

Visibility judgment logic:

But it also brings about another problem: Without undo, it will lead to space growth. Therefore, PostgreSQL introduces the vacumm background process to regularly clean up the DEAD tuple.

I will write an article about vacumm Principles later.

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: