[serial] how to master the core technology of opengauss database? Secret 4: handle the transaction mechanism (2)

Time:2022-5-17

catalogue
Opengauss database SQL Engine

Opengauss database executor Technology

Opengauss storage technology

Opengauss transaction mechanism

Ⅰ. Opengauss database transaction overview

Ⅱ. Introduction to acid feature of opengauss transaction

1. Transaction persistence in opengauss
2. Transaction atomicity in opengauss
3. Transaction consistency in opengauss
4. Transaction isolation in opengauss
Ⅲ. Opengauss concurrency control
Ⅳ. Opengauss distributed transaction
Opengauss database security

Opengauss transaction mechanism

Introduction to acid feature of opengauss transaction

This section mainly introduces how to ensure the acid of single transaction in opengauss. On this basis, the fourth section of the article will explain how to ensure the acid of distributed transaction.

Transaction persistence in opengauss 01
Like almost all databases in the industry, opengauss ensures the persistence of transactions by writing the modifications of transactions to the database into a storage medium that can be permanently (for a long time). This process is called the persistence process of transactions. Persistence process is an essential link to ensure transaction persistence. Its efficiency has a great impact on the overall performance of the database, and often becomes the performance bottleneck of the database.

The most commonly used storage medium is disk. For a disk, each read-write operation has a “start” cost. Therefore, there is an upper limit on the number of input / output operations per second (IOPs) a disk can perform in a unit time (per second). The IOPs of HDD disk is generally less than 1000 times / s, and the IOPs of SSD disk can reach about 10000 times / s. On the other hand, if the data of multiple disk read-write requests are adjacent on the disk, they can be combined into one read-write operation, which leads to the performance of disk sequential read-write is usually much better than random read-write.

Generally speaking, especially in the OLTP scenario, users’ modifications to database data are scattered and random. If these scattered data are directly written to the disk during the persistence process, the performance of this random write is relatively poor. Therefore, the database usually adopts write ahead log (wal) to avoid random IO in the persistence process, as shown in Figure 4 (a). The so-called pre write log means that when the transaction is committed, the modification of the transaction to the database is written into a sequentially appended wal log file. Because the write order of the log operation can reach a higher performance than that of the wal operation. On the other hand, for the real modified physical data file, wait for the appropriate time to write to the disk to merge the IO operations on the data file as much as possible.

After a transaction completes the footwall operation of the log (that is, writing to disk), the transaction can complete the commit action. If the database goes down after that, the database will first recover the modification of the transaction to the database from the wal file written to the disk, so as to ensure the persistence of the transaction once committed.

The principle of database fault recovery is briefly explained below in combination with the example in Fig. 4 (b). Suppose a transaction needs to insert a row of new records into table a (corresponding data file a) and table B (corresponding data file B). Within the database, the execution sequence is as follows: (1) record the log of modifying data file a, (2) record the log of modifying data file B, (3) write a new record in data file a, (4) write a new record in data file B. In the above process, if the database goes down during the execution of step (4), the modification of data file B by the transaction may be lost in whole or in part. After the database is started again, before it can accept new business, it needs to find these possible lost modifications from the log (this operation is called log playback operation).

In the process of log playback, the database will read the contents of each log in turn according to the sequence of log records, and then judge whether the modification of the transaction recorded in the log to the database data file is consistent with the contents of the current relevant data file. If it is consistent, it means that the modification has been written into the data file before the last database shutdown, and the log modification does not need to be played back; If it is inconsistent, it means that the modification has not been written to the data file before the last database shutdown. The last database shutdown may be caused by abnormal downtime. The transaction operations corresponding to the log need to be executed again in the relevant data file to ensure successful recovery.

For this example, in the process of database recovery, first read the log of the record inserted in the data file A. after reading the data file a, it is found that the record has been included in the data file a, so the log does not need to be played back; Then read the log of the record inserted in the data file B. after reading the data file B, it is found that the newly inserted record is not contained in the data file B. therefore, it is necessary to write the record in the log to the data file B again to complete the recovery. Finally, all modifications of the transaction to the database can be recovered, and the persistence of the transaction is guaranteed.

[serial] how to master the core technology of opengauss database? Secret 4: handle the transaction mechanism (2)

(a) Relationship between wal log and data page

[serial] how to master the core technology of opengauss database? Secret 4: handle the transaction mechanism (2)

(b) Wal log and fault recovery diagram

Figure 4 Schematic diagram of wal log and transaction persistence

Transaction atomicity in opengauss 02
As shown in Figure 5, opengauss ensures the atomicity of write transactions through multiple versions of wal logs, transaction commit logs and update records.

[serial] how to master the core technology of opengauss database? Secret 4: handle the transaction mechanism (2)

Figure 5 atomicity diagram of opengauss transaction

(1) For insert transactions, for example, the following insert transactions:

START TRANSACTION;
INSERT INTO t(a) VALUES (v1);
INSERT INTO t(a) VALUES (v2);
COMMIT;

Usually, the physical organization of a record in the database is called tuple, which is similar to a structure in form. During the execution of the above insert transaction, for each newly inserted record, the unique identification of the insert transaction, that is, a globally increasing transaction ID (XID) is attached to the xmin member in the header of their tuple structure. As described in section 10.2.1, these two inserted records (tuples) together with their headers will be written to the wal log in sequence.

In the commit phase of the transaction, a transaction commit log will be inserted into the wal log to persist the commit result of the transaction, and the transaction commit result (commit or roll back) corresponding to the transaction number will be recorded in the special transaction commit log (clog). After that, if a query transaction reads these two records, it will first query the submission information corresponding to the transaction number in the record header in clog. If it is submitted and judged by visibility, these two records will be returned in the query result; If the transaction number in clog is in rollback status, or the transaction number in clog is in commit status, but the transaction number is not visible to the query, these two records will not be returned in the query result. As mentioned above, in the case of no fault, the above transaction inserting two rows of records is atomic and will not occur. Only one “intermediate state” is seen.

Next, consider the fault scenario.

§ if the database goes down before the transaction writes the commit log, these two records will be inserted into the data page during the database recovery process, but the inserted transaction number will not be identified as the commit status in the clog, and these two records will not be returned in subsequent queries.

§ if the database goes down after the transaction writes the commit log, these two records will not only be inserted into the data page during database recovery. At the same time, the inserted transaction number will be identified as submitted in clog, and the two inserted records can be seen in subsequent queries at the same time. As mentioned above, in the fault scenario, the above transaction operation of inserting two rows of records is also atomic.

(2) For delete transactions, for example:

START TRANSACTION;
DELETE FROM t WHERE a = v1;
DELETE FROM t WHERE a = v2;
COMMIT;

During the execution of the delete transaction, for each deleted record above, the transaction number of the delete transaction is appended to the xmax member in the header of their tuple. At the same time, like the insert operation, the commit status of the delete transaction is materialized through the transaction commit log and recorded in the clog. Thus, whether in the normal scenario or the fault scenario, if the subsequent query involves the above deleted records, their visibility depends on the unified state of the deletion transaction recorded in the clog, and there will be no “intermediate state” in which some records can be queried and some records cannot be queried.

(3) For update transactions, for example:

START TRANSACTION;
UPDATE t set a = v1’ WHERE a = v1;
UPDATE t set a = v2’ WHERE a = v2;
COMMIT;

In opengauss, the above update transaction is equivalent to deleting the old version records of V1 and V2, and then inserting the new version records of V1 and V2. The atomicity of the delete and insert transaction has been described in (1) and (2), so the update transaction is also atomic.

Unfinished to be continued