DWQA QuestionsCategory: DatabaseThe concept of virtual transaction is encountered in postgre database. What is virtual transaction?
Ocean asked 7 months ago

background
Here is the segment information in the PG ﹣ locks table
Field name type description
Virtualxid text is the transaction virtual ID of the lock target. If the target is not a virtual transaction ID, this column is empty
Example:

locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction |       pid       |      mode       | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+-
-------------------+-----------------+-----------------+---------+----------
 virtualxid |          |          |      |       | 6/25442    |               |         |       |          |
6/25442            | 140607531513600 | ExclusiveLock   | t       | t

problem
Do not know which system table to query the information of the object represented by virtualxid?
I met the concept of virtual transaction in the database. What is virtual transaction?
Why do I need virtual transactions?
What are the benefits of virtual transactions?

1 Answers
Best Answer
The old rat in the cage answered 7 months ago

Virtual transaction is not a general concept in the field of database, it is not a user oriented concept, it is a pure PG internal concept.
In PG, all implicit or explicitRead-only transactionAre treated as virtual transactions.
The main reason why PG came up with such a concept is for performance optimization: in earlier versions (before 9.0? )When the concept of virtual transaction has not been introduced into PG, since all operations within PG must belong to an implicit or explicit transaction, the problem of allocating XID (transaction ID) will be involved. However, there are several derivative problems in the allocation of XID:

  1. The XID allocation process is locked (in the codeXidGenLock), once lock is involved, there will be competition, which will affect performance
  2. XID increases frequently

Later, it was found that there was no need to allocate XID for read-only transactions (implicit or explicit). Because read-only transactions do not change data, even if XID is assigned to read-only transactions, this XID will never be used in the transaction management of the storage layer. In addition, for an OLTP system, the number of reads in most application scenarios is larger than that of writes. Therefore, for the sake of performance optimization, PG no longer assigns XID to read-only transactions, but introduces “virtual transaction ID” ”The concept of. The cost of allocating virtual transaction ID is much less than that of XID.
More aboutVirtual transactionRefer to Gregory Smith’sPostgreSQL 9.0 performance tuningA book, which has more detailed elaboration.