Shulou(Shulou.com)06/01 Report--

There are two concepts of transaction number in PG, one is the transaction number transaction id in the usual sense. Such as xmin,xmax in tuple. Another meaning is the virtual transaction ID, or virtual transaction ID. So these two have

What's the difference?

1.Transaction Id

It is used to identify the order of transactions, similar to Oracle's LSN (Logical Sequence Number). But the transaction number in PG can be wrap, that is, it can be reused.

PG defines the transaction ID as 32 bits long. Equivalent to 4 billion. Because it is reused, it connects end to end to form a ring. That is to say, for any transaction ID, there is a 2 billion transaction ID that is more old than itself.

There are 2 billion transactions than their own new. There are three other transactions ID that have special meaning: "0" for invalid transaction number, "1" for bootstrap transaction number, "2" for frozen transaction, and "3" for smallest user transaction ID.

In addition, 1 "and" 2 "are also valid. Frozen transaction id is more old than any transaction. PG is used to solve the transaction number wrap problem, in the case of transaction number recycling, it is possible that the new transaction number is smaller than the old transaction number.

So set the old transaction number to "2", which means frozen transaction. The action of frozen transaction id is initiated by vacuum. Please give a specific introduction to my other article, "PostgreSQL vacuum principle-vacuum revelation".

The generation of transaction id is protected by lwlock "XidGenLock" and stored in the ShmemVariableCache shared memory segment.

The definition of transaction id source code is as follows:

Transaction number type definition:

2.Virtual Transaction Id

Also known as virtual transaction ID. Virtual transaction id consists of two parts, backend process ID and local transaction id.

The backend process ID number is not the process ID of the operating system, but the ID used to identify the process sequence number in PG. Local transansaction id is also represented by a 32-bit length. The difference from the transaction id mentioned above can be seen by the name: it is the difference between local and non-local.

In other words, this local transaction id is unique to each backend process. The transaction id mentioned in the first part above is global, that is, the entire PG cluster level.

The first red circle in the figure is the global transaction id. And the content in the second lap is virtual transaction id. The backend id number and local transaction id are separated by a "/" symbol.

The front part is the backend id number, and the back part is local transaction id. Part of the third red circle is the system process number. It is obvious here that the backend id number in virtual transaction id is different from the pid in the third red circle.

Pid is the process number of the operating system. Virtual transaction id can only be divided into valid and invalid. "0" is expressed as invalid, and the rest is valid. In addition, virtual transaction id is reused after the database is restarted, but grows sequentially under the same backend id.

Virtual transaction id is held by ExclusiveLock, because there is no contention in the private space of a process. At this point, like transaction id, transaction id is globally exclusive, so it is also ExclusiveLock.

From the mode column in the figure above, we can see clearly.

The definition is as follows:

3. Summary

Why did PG create such two transaction id? what is the purpose and meaning?

We know that statements like SELECT do not change the database, while DML statements affect the state of the database. So that's why it's treated differently.

Transaction id belongs to permanent id, that is, permanent ID. Its meaning refers to the sequence of changes to the database, which makes the database change from one state to another, and the state change is persistent, recoverable, and consistent.

This is also in line with the requirements of the database theory ACID.

The query, in fact, does not need this kind of permanent transaction ID, just need to deal with MVCC, lock acquisition and release, so virtual transaction id is enough. You don't need to get XidGenLock to generate transaction id.

Thus improve the performance of the database. In addition, the database will not cause transaction id fast wrap around because of the query. The transaction id value is not required for MVCC processing. When querying, get the xmin and xmax values of each currently active process

The visibility snapshot can be obtained by comparing the xmin and xmax in each tuple header with this interval. For the detailed implementation of MVCC, see "PostgreSQL MVCC source code implementation".

In addition, the timing of acquisition is also very different. The transaction implementation of PG has three layers, namely top layer, middle layer and bottom layer. Virtual transaction id is obtained in top layer. Whether it is a query or a DML operation, each

All commands have virtual transaction id. On the other hand, transaction id is obtained in bottom layer, and only when data modification is really involved. After modifying tuple, the value of transaction id is stored in tuple header

This is also what we usually call xmin or xmax.

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.