Go PostgreSQL transaction processing mechanism

Original link: http://blog.chinaunix.net/uid-20726500-id-4040024.html

The implementation principle of a transaction can be interpreted as what technology the DBMS takes to ensure the acid characteristics of the transaction. PostgreSQL's implementation techniques for acid are shown in the following table.

Table 1:4 Characteristics of a transaction acid and its implementation technology of response

ACID	Implementation technology
Atomic Nature	MVCC
Consistency	Constraints (primary key, foreign key, etc.)
Isolation of	MVCC
Durability	WAL

You can see that the support for the acid in PostgreSQL is mainly MVCC and wal two technology. MVCC and wal are two more mature technologies, usually have corresponding implementations in relational databases, but there is a big difference in how each database is implemented. The basic implementation principles of MVCC and Wal in PostgreSQL are described below.

1. MVCC

MVCC (multiversion Concurrency control) is a multi-version concurrency controller that avoids the blocking of read and write transactions and greatly improves the concurrency performance of the business compared to the usual blocking techniques. The MVCC implementation principle in PostgreSQL can be summarized as follows:

1) Multiple row versions (called tuple) in the data file that hold the same logical line

2) header record for each row version creates and deletes the ID of the row version of the transaction (respectively called Xmin and Xmax)

3) The status of each transaction (run, abort, or commit) is recorded in the Pg_clog file

4) According to the above data and apply certain rules each transaction will only see a specific row version

MVCC read and write transactions can work on separate row versions, so they can be executed concurrently in a non-conflicting situation.

Figure 1: MVCC-based Data Update example

2. WAL

When the system is unexpectedly down, you need to roll back the changes that were made to the transaction that were not completed and make sure that the changes that were made to the committed transactions have taken effect. In PostgreSQL, it is easy to do 1th with the aforementioned MVCC, as long as the status of all "Running" transactions recorded in all Pg_clog files is set to "abort", and these transactions are not completed at the end of the outage. For the 2nd, you must ensure that the modification is actually written to the persistent store when the transaction commits. However, it is time-consuming to flush the modified data from the transaction directly to the disk, and the Wal (Write-ahead Log) is introduced to solve the problem.

Wal's rationale is as follows:

1) before updating the data page, record the update into the Wal log

2) Asynchronously refreshes the dirty page of the data buffer and the Wal buffer to disk

3) Buffer Manager ensures that dirty data is never flushed to disk prior to the corresponding Wal record

4) When a transaction commits, flushes the Wal log to disk synchronously

5) When checkpoint occurs, flush all dirty pages of data buffer to disk

Figure 2: Buffer modification when data is updated

Figure 3: Disk synchronization when updating commits and checkpoint

Go PostgreSQL transaction processing mechanism