From what I understand you InnoDB supports
- Row Level Locking
- MVCC (Multiversion Concurrency Control)
Row Level Locking
Locking is for when multiple writers are trying to update the same rows. Only one writer can update a row at a time, and the first one to update the row locks it until they commit the change. Other writers have to wait until the first writer commits. But at least with row-level locking, they only have contention if they're updating the same row.
A read lock can be used to prevent other users from reading a record (or page) which is being updated, so that others will not act upon soon-to-be-outdated information.
Multiversion Concurrency Control
Writers don't block readers
Readers don't block anyone, and don't get blocked by anyone.
Those two are the opposite of each other.
My question is: when does locking or mvcc occur? Where do I need to specify which one the database should use?