MOT Concurrency Control Mechanism
Tags: opengauss
Published: 2023-04-20 04:37:33

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.

With the release of openGauss 5.0 the MOT now includes support for MVCC, which among other benefits reduces the contention between read and update transactions thus reducing transaction aborts that come with OCC method.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ssuwkq0btdt0g2amun29.png)

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)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1b50s17ukbwb8jykym3d.png)

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

**· MOT SILO Enhancements**
SILO in its basic algorithm flow outperformed many other ACID-compliant OCCs that we tested in our research experiments. However, in order to make it 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
**·** Multi Version Concurrency Control (MVCC) support was added (openGauss 5.0).
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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b6li8p74tfkm9fvna8pd.png)

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

Table 2 Concurrency Side Effects Enabled by Isolation Levels

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6pixvjz0sq77vnem94fq.png)