MOT Concurrency Control Mechanism ​

After investing extensive research to find the best concurrency control mechanism, we concluded that SILO-based on OCC is the best ACID-compliant OCC algorithm for MOT. SILO provides the best foundation for MOT's challenging requirements.

The following topics describe MOT's concurrency control mechanism.

MOT Local and Global Memory ​

SILO manages both a local memory and a global memory, as shown in.

• Global memory is long-term shared memory is shared by all cores and is used primarily to store all the table data and indexes
• Local memory is short-term memory that is used primarily by sessions for handling transactions and store data changes in a primate to transaction memory until the commit phase.

When a transaction change is required, SILO handles the copying of all that transaction's data from the global memory into the local memory. Minimal locks are placed on the global memory according to the OCC approach, so that the contention time in the global shared memory is extremely minimal. After the transaction change has been completed, this data is pushed back from the local memory to the global memory.

The basic interactive transactional flow with our SILO-enhanced concurrency control is shown in the figure below:

Figure 1 Private (Local) Memory (for each transaction) and a Global Memory (for all the transactions of all the cores)
![]figures/private-(local)-memory-(for-each-transaction)-and-a-global-memory-(for-all-the-transactions-of-all-t.png "private-(local)-memory-(for-each-transaction)-and-a-global-memory-(for-all-the-transactions)-of-all-t")

For more details, refer to the Industrial-Strength OLTP Using Main Memory and Many-cores document^[Comparison: Disk vs. MOT^].

MOT SILO Enhancements ​

SILO^[Comparison: Disk vs. MOT^] in its basic algorithm flow outperformed many other ACID-compliant OCCs that we tested in our research experiments. However, in order to make SILO a product-grade mechanism, we had to enhance it with many essential functionalities that were missing in the original design, such as:

• Added support for interactive mode transactions, where transactions are running SQL by SQL from the client side and not as a single step on the server side
• Added optimistic inserts
• Added support for non-unique indexes
• Added support for read-after-write in transactions so that users can see their own changes before they are committed
• Added support for lockless cooperative garbage collection
• Added support for lockless checkpoints
• Added support for fast recovery
• Added support for two-phase commit in a distributed deployment

Adding these enhancements without breaking the scalable characteristic of the original SILO was very challenging.

MOT Isolation Levels ​

Even though MOT is fully ACID-compliant (as described in the section), not all isolation levels are supported in openGauss 1.0. The following table describes all isolation levels, as well as what is and what is not supported by MOT.

Table 1 Isolation Levels

The following table shows the concurrency side effects enabled by the different isolation levels.

Table 2 Concurrency Side Effects Enabled by Isolation Levels

In the near future release, openGauss MOT will also support both SNAPSHOT and SERIALIZABLE isolation levels.

MOT Optimistic Concurrency Control (OCC) ​

The concurrency control (CC) module provides all transactional requirements for the main memory engine. The CC module mainly provides various levels of isolation support for the main memory engine.

OCC and Pessimistic Two-phase Locking (2PL) ​

The difference between pessimistic 2PL and OCC lies in the use of pessimistic and optimistic methods for transaction integrity, respectively.

Disk-based tables use a pessimistic method, which is the most common database method. The MOT engine uses an optimistic method.

If a conflict occurs, the main difference between the pessimistic and optimistic methods is as follows:

• A pessimistic method causes the client to wait.
• An optimistic method causes one of the transactions to fail, making the client have to retry the failed transaction.

OCC Method (Used in MOT)

The OCC method detects conflicts when conflicts occur and perform validation checks during committing.

The OCC method is less expensive and typically more efficient because transaction conflicts are not common in most applications.

When the REPEATABLE READ isolation level is enforced, the function difference between the optimistic and pessimistic methods is larger. When the SERIALIZABLE isolation level is enforced, the function difference is the largest.

Pessimistic Method (Not Used in MOT)

The pessimistic 2PL method uses locks to prevent potential conflicts. A lock is used when a statement is executed, and the lock is released when the transaction is committed. Disk-based row store uses this method and adds the multi-version concurrency control (MVCC).

In the 2PL algorithm, when a transaction is writing to a row, other transactions cannot access the row. When a row is being read, other transactions cannot overwrite the row. Each row is locked for read and write during access. The lock is released when the transaction is committed. These algorithms require a solution to handle and avoid deadlocks. Deadlocks can be detected by calculating the period in the wait-for graph. Deadlocks can be avoided by using TSO^[Comparison: Disk vs. MOT^] to preserve time series or by using some kind of fallback scheme.

Encounter-time Locking (ETL)

Another method is ETL, which handles the read operation in an optimistic way but locks the data assessed by the write operation. Therefore, write operations from different ETL transactions are aware of each other and can decide to abort. The experiment^[Comparison: Disk vs. MOT^] proves that the ETL improves the OCC performance in the following two ways:

• First, ETL detects conflicts early and typically increases transaction throughput. This is because the transaction does not perform useless operations. (Generally) Conflicts found during committing cannot be resolved without aborting at least one transaction.
• Second, ETL runs efficiently in read-after-write (RAW) mode, eliminating the need for expensive or complex mechanisms.

Conclusion:

OCC is the fastest option for most workloads^[Comparison: Disk vs. MOT^][Comparison: Disk vs. MOT^]. We have found this in the preliminary study phase.

One reason is that when each core executes multiple threads, the lock is likely to be held by the swap thread, especially in interactive mode. Another reason is that pessimistic algorithms involve deadlock detection (which incurs overhead) and typically use read-write locks (which are less efficient than standard spin locks).

We chose Silo^[Comparison: Disk vs. MOT^] because it is simpler than other existing options, such as TicToc^[Comparison: Disk vs. MOT^], while maintaining the same performance for most workloads. ETL is sometimes faster than OCC, but it introduces a false abort that can confuse the user, while OCC aborts only during committing.

Differences Between OCC and 2PL ​

The following are user experience differences between pessimistic (for disk-based tables) and optimistic (for MOTs) when a session updates the same table at the same time.

In this example, the following test command is used:

table "TEST" – create table test (x int, y int, z int, primary key(x));

This example describes two aspects of the same test: user experience (operations in this example) and retry requirements.

Example of a Pessimistic Method for Disk-based Tables

The following is an example of a pessimistic method (non-MOT). Any isolation level may apply.

The following two sessions execute transactions that attempt to update a single table.

After WAIT LOCK occurs, session 2 is being suspended until session 1 is committed.

However, both sessions succeed and no abort occurs (unless the SERIALIZABLE or REPEATABLE-READ isolation level is applied), which causes the entire transaction to need to be retried.

Table 1 Pessimistic method code example

Example of an Optimistic Method for MOTs

Here is an example of an optimistic method.

An MOT is created and then two concurrent sessions update the same MOT at the same time.

create foreign table test (x int, y int, z int, primary key(x));

• The advantage of OCC is that there is no lock before COMMIT.
• The disadvantage of OCC is that if another session updates the same record, the update may fail. If the update fails (at all supported isolation levels), the entire session #2 transaction must be retried.
• Update conflicts are detected by the kernel through the version check mechanism during committing.
• Session 2 will not wait for its update operation and will abort due to a conflict detected during committing.

Table 2 Optimistic method code for MOTs