2

From what I understand you InnoDB supports

  1. Row Level Locking
  2. 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.

https://stackoverflow.com/questions/6321647/innodbs-row-locking-the-same-as-mvcc-non-blocking-reads https://en.wikipedia.org/wiki/Lock_(database)

Multiversion Concurrency Control

  1. Writers don't block readers

  2. Readers don't block anyone, and don't get blocked by anyone.

(https://stackoverflow.com/questions/22923127/mvcc-row-locking-vs-textbook-transaction-behavior)

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?

4

MVCC applies to isolation levels read-committed and repeatable read (default).

You don't need to specify anything for both of these features to work together. Maybe one way to think about it, is that row level locking is important so that you can update multiple rows at a time, and MVCC is so that the updates don't affect read operations at all.

  • Thanks for your response. What I don't understand is that you still have a row level locking? and a row level locking usually blocks reads, so the MVCC prevents it? Can you still call that a 'locking' since a lock should prevent reads? Or do I misunderstand the locking concept? – TheOddGuy Apr 3 '16 at 14:52
  • The locking is still required, because it is needed there to prevent modifications (updates) to that row. If you want to trigger a non MVCC read, you can use SELECT * FROM my_table WHERE x=y FOR UPDATE. This will block on any row locks. – Morgan Tocker Apr 3 '16 at 15:43
  • FOR UPDATE is saying "I intend to change this row, please block others from changing it; Meanwhile, block my read if someone else already grabbed this row". Without FOR UPDATE, you can read whatever rows you like, even as they are being changed. Your value is some 'old' version of that row, as in MVCC. – Rick James Apr 4 '16 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.