The Concurrency Control Module (CC Module for short) provides all the transactional requirements for the Main Memory Engine. The primary objective of the CC Module is to provide the Main Memory Engine with support for various isolation levels.
Optimistic OCC vs. Pessimistic 2PL
The functional differences of Pessimistic 2PL (2-Phase Locking) vs. Optimistic Concurrency Control (OCC) involve pessimistic versus optimistic approaches to transaction integrity.
Disk-based tables use a pessimistic approach, which is the most commonly used database method. The MOT Engine use an optimistic approach.
The primary functional difference between the pessimistic approach and the optimistic approach is that if a conflict occurs –
· The pessimistic approach causes the client to wait.
· The optimistic approach causes one of the transactions to fail, so that the failed transaction must be retried by the client.
Optimistic Concurrency Control Approach (Used by MOT)
The Optimistic Concurrency Control (OCC) approach detects conflicts as they occur, and performs validation checks at commit time.
The optimistic approach has less overhead and is usually more efficient, partly because transaction conflicts are uncommon in most applications.
The functional differences between optimistic and pessimistic approaches is larger when the REPEATABLE READ isolation level is enforced and is largest for the SERIALIZABLE isolation level.
Pessimistic Approaches (Not used by MOT)
The Pessimistic Concurrency Control (2PL or 2-Phase Locking) approach uses locks to block potential conflicts before they occur. A lock is applied when a statement is executed and released when the transaction is committed. Disk-based row‑stores use this approach (with the addition of Multi-version Concurrency Control [MVCC]).
In 2PL algorithms, while a transaction is writing a row, no other transaction can access it; and while a row is being read, no other transaction can overwrite it. Each row is locked at access time for both reading and writing; and the lock is released at commit time. These algorithms require a scheme for handling and avoiding deadlock. Deadlock can be detected by calculating cycles in a wait-for graph. Deadlock can be avoided by keeping time ordering using TSO or by some kind of back-off scheme.
Encounter Time Locking (ETL)
Another approach is Encounter Time Locking (ETL), where reads are handled in an optimistic manner, but writes lock the data that they access. As a result, writes from different ETL transactions are aware of each other and can decide to abort. It has been empirically verified that ETL improves the performance of OCC in two ways –
· First, ETL detects conflicts early on and often increases transaction throughput. This is because transactions do not perform useless operations, because conflicts discovered at commit time (in general) cannot be solved without aborting at least one transaction.
· Second, encounter-time locking Reads-After-Writes (RAW) are handled efficiently without requiring expensive or complex mechanisms.
Conclusion
OCC is the fastest option for most workloads. This finding has also been observed in our preliminary research phase.
One of the reasons is that when every core executes multiple threads, a lock is likely to be held by a swapped thread, especially in interactive mode. Another reason is that pessimistic algorithms involve deadlock detection (which introduces overhead) and usually uses read-write locks (which are less efficient than standard spin-locks).
We have chosen Silo because it was simpler than other existing options, such as TicToc, while maintaining the same performance for most workloads. ETL is sometimes faster than OCC, but it introduces spurious aborts which may confuse a user, in contrast to OCC which aborts only at commit.
OCC vs 2PL Differences by Example
The following shows the differences between two user experiences – Pessimistic (for disk-based tables) and Optimistic (MOT tables) when sessions update the same table simultaneously.
In this example, the following table test command is run –
This example describes two aspects of the same test – user experience (operations in the example) and retry requirements.
Example Pessimistic Approach – Used in Disk-based Tables
The following is an example of the Pessimistic approach (which is not Mot). Any Isolation Level may apply.
The following two sessions perform a transaction that attempts to update a single table.
A WAIT LOCK action occurs and the client experience is that session #2 is stuck until Session #1 has completed a COMMIT. Only afterwards, is Session #2 able to progress.
However, when this approach is used, both sessions succeed and no abort occurs (unless SERIALIZABLE or REPEATABLE-READ isolation level is applied), which results in the entire transaction needing to be retried.
Table 1 Pessimistic Approach Code Example
Example Optimistic Approach – Used in MOT
The following is an example of the Optimistic approach.
It describes the situation of creating an MOT table and then having two concurrent sessions updating that same MOT table simultaneously –
· The advantage of OCC is that there are no locks until COMMIT.
· The disadvantage of using OCC is that the update may fail if another session updates the same record. If the update fails (in all supported isolation levels), an entire SESSION #2 transaction must be retried.
· Update conflicts are detected by the kernel at commit time by using a version checking mechanism.
· SESSION #2 will not wait in its update operation and will be aborted because of conflict detection at commit phase.
Table 2 Optimistic Approach Code Example – Used in MOT
Top comments (0)