Rather than locking rows or tables (etc), you keep versions on rows, and allow isolated changes to versioned "duplicates" of the rows in transactions. There is a process that relatively quickly checks for conflicts during a commit -- is there a "gap" in the version numbers of rows or some such thing. If inconsistency would result, the first commit (already) wins, and the second gets rolled back. Otherwise, the replacement (or new) rows become the new "master" version.
The commit check can become a bottleneck, but it shouldn't deadlock.
Almost forgot to add: PostgreSQL has been using MVCC for quite some time.
My view of MySQL was discolored pretty badly when I needed an SQL database back in 2001 to prototype some stuff from home, and discovered how "gappy" MySQL was at the time: rollback did nothing; referential integrity not enforced; lack of transaction isolation. It felt like XBase hidden under SQL syntax (something to which I did not want to regress).
OTOH, MySQL ran on Windows, and PostgreSQL didn't (at the time), so I guess that was "the win" for many, alas. Both DBs have become much more like each other in the intervening decade, though, which is good news.
I think developers should be more aware of how the DBMS works under the hood.
Edit: In my case, I still use row-level lock when implementing the state machine pattern on a model. It enforces the application-level-consistency of the model state, preventing double updates in case of high concurrency, in which the second (concurrent) update would break the application logic.
You are confusing two concepts: locking used by the implementation to provide isolation and atomicity, and locking used by the application to provide consistency.
Consistency is a separate concern from Isolation and Atomicity. With the default isolation level (repeatable read) it is frequently necessary to use row-level locks to ensure consistency with concurrent transactions. This is an application-level concern, and an interactions with the isolation level rather than an interaction with the implementation. Short of SSI (serializability), hazards exist which can have counterintutive impacts on data consistency. The implementation's use of MVCC is irrelevant.
Locking is also frequently used in applications to help ensure external consistency, but that's a pattern with real and significant pitfalls that should be avoided.
I'm not going to claim to understand the entire topic of DB locking, but I intended to refer to what happens within the DB on your behalf when the application is running a transaction without any explicit locks. If I am within a transaction, and repeat a read for some reason, I expect the same result unless my process made modifications. I forget the name of that level, but that's what I expect.
My gripe with record and related level locking comes from past pain. At one past job, our DBA spent a lot of time in Sybase (a non-MVCC DB) constantly twiddling locking granularity levels to keep processes out of each others' way. (as in deadlock)
Rather than locking rows or tables (etc), you keep versions on rows, and allow isolated changes to versioned "duplicates" of the rows in transactions. There is a process that relatively quickly checks for conflicts during a commit -- is there a "gap" in the version numbers of rows or some such thing. If inconsistency would result, the first commit (already) wins, and the second gets rolled back. Otherwise, the replacement (or new) rows become the new "master" version.
The commit check can become a bottleneck, but it shouldn't deadlock.
Almost forgot to add: PostgreSQL has been using MVCC for quite some time.
My view of MySQL was discolored pretty badly when I needed an SQL database back in 2001 to prototype some stuff from home, and discovered how "gappy" MySQL was at the time: rollback did nothing; referential integrity not enforced; lack of transaction isolation. It felt like XBase hidden under SQL syntax (something to which I did not want to regress).
OTOH, MySQL ran on Windows, and PostgreSQL didn't (at the time), so I guess that was "the win" for many, alas. Both DBs have become much more like each other in the intervening decade, though, which is good news.