PolarDB:Lizard transaction system

Limits

The database engine must be compatible with MySQL 8.0.

How it works

Relation databases use the Multi Version Concurrency Control (MVCC) mechanism to determine the visibility of data based on its committed version. To align with this, the Lizard standalone transaction system adopts the System Commit Number (SCN) for indicating the sequence of transaction commits. Furthermore, the system features a specially designed transaction slot for the durable retention of commit version numbers.

Write transactions:

• When a transaction starts, apply for a transaction slot and record its address as UBA.

• During the transaction, enter the two fields (SCN=NULL, UBA) for the modified record.

• When committing the transaction, obtain its SCN and add it to the transaction slot. Then, ensure that the transaction status is completed, and return the commit information of the transaction to the customer.

Read transactions:

• When the query is started, start the transaction vision to obtain the current SCN from the SCN generator as the vision of the query.

• When the query is in progress, find the transaction slot based on the UBA of the transaction in the row record, and obtain the transaction status and commit number.

• Compare SCNs in the record and view to determine the visibility.

SCN-based transaction performance optimization

Compared with the open source InnoDB transaction system of MySQL, the Lizard transaction system brings huge advantages:

• Eliminates the access dependency on the global structure, which significantly mitigates read/write conflicts.

• Upgrades the view to the vision, which has only one SCN number rather than the active transaction ID array, making transmission easier.

• Supports custom FlashBack queries.

However, it also has some disadvantages. When a transaction is committed, only the transaction slot is changed, and the SCN in the row record is always NULL. Therefore, during each visibility comparison, you need to query the transaction slot based on the UBA to confirm the SCN. To reduce the repeated query of transaction slots, cleanouts are introduced to the Lizard transaction system. Cleanouts are categorized into commit cleanouts and delayed cleanouts.

Commit cleanouts

During the modification process of a transaction, some records are collected. After the transaction is committed, partial collected records are backfilled based on the committed SCN. To ensure the speed of commission, only a small number of records are backfilled based on the current number of records and the load capacity of the system, and the result is quickly committed and returned to the customer.

Delayed cleanouts

In the query process, the system checks the transaction slot based on the UBA to determine the transaction status and the version number. If the transaction has been committed, the system tries to help clean out the row record, which is called delayed cleanout. Then, in the next query, the system can directly query the SCN in the row record to determine the visibility, without the need to query the transaction slot.

Transaction slot reuse

The transaction slot cannot be expanded indefinitely, so the reusing solution is adopted to avoid space bloating. Transaction slots keep stored in a free list. During transaction slot allocation, transaction slots in the free list are prioritized for reuse.

In the case of frequent access to the free list to obtain slots, the system needs to access multiple data pages, which brings huge overhead. In order to avoid accessing multiple data pages, the system adds the transaction slot page to the cache table, and then directly obtains the desired slot from the cache table. This greatly reduces the overhead caused by reading multiple data pages.

Performance of the SCN transaction system

Although cleanouts have certain disadvantages, they are used during the query process but are not used during hot spot competition. The test results show that the SCN transaction system has higher throughput than the traditional InnoDB transaction system.

Compared with MySQL-8032, Lizard SCN offers 30% performance improvement and 53% latency reduction.