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.