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