- InnoDB does not use simple row locking mechanism. It stores old versions of changed rows in “rollback segment” or “undo space” (like Oracle)
- MVCC allows to avoid row locks in some cases + have lower overhead.
- Attention – different transactions can see different data in the same table at the same time!
- There are “pesimistic” and “optimistic” variants of MVCC
- InnoDB stores for every row 2 hidden values – system version number of insertion and deletion
- MVCC has big impact on indexes in InnoDB – index stores info about all row versions available !
- MVCC can cause problems on system with heavy updates on table(s) on which we run at the same time very slow analytical queries. In this situation we will force engine to store huge amount of row versions and it will cause big overhead.
- If “rollback segment” fits into memory then usage will be very quick. If not then we will see many waits on CPUs because of I/O operations.
- System used only for reporting without updates will have no problems with MVCC because there will be no undo info for this data.