Vlad: How does MVCC work

Optimistic vs Pessimistic

Pessimistic vs Serializable

Lost Update In Oracle

My simple understanding of MvCC.

  1. as the names says, it’s for multi concurrency request handling.

  2. it’s the mechanism for handling concurrency on same record(s).

  3. when talking concurrency, lost update always needs to be considered.

  • Locking: Pessmistic and Optimistic Locking

    Pessmistic: avoid race conditions.

    Optimistic: allow conflicts happen.

  • MVCC is kind of Optimistic mechanism.

  1. MVCC provide multi version of data(snapshot) for multi transactions.

  2. in transaction, the visibility of the version-data is depending on the isolation level.

  3. the higher isolation level is, the lower performance.

  4. deadlock cases:

    transaction A holds record A waiting record B, transaction B holds record B waiting record A.

    make multi records update in same order.

  5. make transaction small, avoid external call. (Transactional-Outbox-Pattern)