Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "PostgreSQL MVCC source code example analysis", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "PostgreSQL MVCC source code example analysis" this article.

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

A normal query request initiated by a user (excluding select … For update statement), which does not block DML transactions. At the Read Commit transaction isolation level, the query request reads only the data changes of the transactions that have been committed before the query request, and does not affect the current version of the data

The DML statement, on the other hand, operates on the current version. Therefore, it achieves the purpose of separating reading and writing, and improves the concurrency ability of the database.

Different databases have different methods to implement MVCC. Oracle and MySQL Innodb storage engines are similarly implemented using undo.

As far as PostgreSQL database is concerned, he doesn't have undo, so how does PG implement his own MVCC? What are the advantages and disadvantages?

PG implements multiple versions using tags such as copy tuple and tuple's xmin,xmax,cmin,cmax.

Xmin: when you create a record (tuple), the record is updated at this time, and each time the update is updated.

Xmax: when deleting tuple or lock, the record is at this time; if the record is not deleted, it is 0.

Cmin and cmax: primarily to identify the sequence values of multiple statement commands in the same transaction. Used to implement version visibility judgment in the same transaction.

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

As you can see from the figure above, the xmin of the four records is the same, all "390689", indicating that it was created in the same transaction. In addition, xmax are all "0", which means that none of them have been deleted. Cmin and cmax are both 1, indicating that they were created by the same command.

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

After update, it is not submitted. Open another window and query:

We see that for the record with an ID of 1, only xmin has not changed, and the other three values have changed, where xmax has become "390691".

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

We see that after submission, for a record with an ID of 1, xmin becomes "390691", xmin increases by 1, and xmax becomes 0.

From the above case, we can see from the surface that xmin has increased. But in fact, PostgreSQL does a lot more than that at the bottom. A new version of tuple has been generated at the underlying level, and the xmin of the new version of tuple is equal to the old version of xmax.

I will expand on the detailed internal later.

two。 Let's take a look at the changes in cmin and cmax:

I start a transaction that contains two update, a record with a update ID of 2, and a record with an insert ID of 3:

In transaction "390694", the values of cmin and cmax are incremented in turn. For now, cmin and cmax are actually the same field.

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

Therefore, judging from the above example, the mvcc implementation of PostgreSQL is relatively simple. You only need to compare the xmin,xmax,cmin,cmax in tuple header with the current xid to get the visibility of this tuple to the current query at the time of scan tuple.

Visibility judgment logic:

But it also brings another problem: in the absence of undo, it will lead to space growth. So PostgreSQL introduced the vacumm background process to clean up the DEAD tuple on a regular basis.

The above is all the contents of this article "sample Analysis of PostgreSQL MVCC Source Code". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.