I'm well aware that InnoDB is MVCC-based. Unfortunately, its implementation of MVCC is hindered by the way the MySQL kernel handles locks, which are completely outside the domain of a storage engine.
Consider:
-- on MySQL, be sure to say "engine=innodb" before the semicolon...
CREATE TABLE locks_suck (id int primary key, val text);
-- or however you'd say "generate_series(start, end)" in MySQL...
INSERT INTO locks_suck (id) VALUES (generate_series(1, 10));
Now, say the following in one session:
BEGIN;
UPDATE locks_suck SET val = 'Transaction 1' WHERE id BETWEEN 1 AND 5;
-- Note that I haven't committed yet...
And then, in a second session:
BEGIN;
UPDATE locks_suck SET val = 'Transaction 2' WHERE id BETWEEN 6 AND 10;
That's the kind of concurrency MVCC can buy you. Readers can't block writers, writers can't block readers, and writers can only block other writers trying to write the same row.
Hey thanks for the example, I think your phrasing was a little unclear but clearly you know what you're talking about, so I reversed my downvote into an upvote. [edit: crap now I can't do that the buttons gone]
I also tried to look into the behavior and it turns out what we're running into here is a 'gap lock' necessary to support ranges in statement based replication (the default). You can change the behavior if you're using row based replication its just not the default.
For instance if the second transaction in your example was "BETWEEN 7 and 10" then both transactions would run concurrently no problem, its acting like row+1 level locking.
This appears to be an issue on how InnoDB handles range row locks, not MySQL (since it's InnoDB that's issuing that lock contention warning). It doesn't happen if you're not using ranges:
i.e. issuing in session 1
BEGIN;
UPDATE locks_suck SET val = 'Transaction 1' WHERE id = 1
and in session 2
BEGIN;
UPDATE locks_suck SET val = 'Transaction 2' WHERE id = 2
doesn't block. These locks are not issued at the MySQL layer when locks_suck is an InnoDB table, they are issued by InnoDB itself (MySQL doesn't actually handle transactions itself, so it would have to be the storage engine).
See dev.mysql.com/doc/refman/5.0/en/innodb-lock-modes.html for more info on how InnoDB issues those locks.
Consider:
Now, say the following in one session: And then, in a second session: In MySQL, you'll hang for a moment, and then see: In Postgres, you'll see: That's the kind of concurrency MVCC can buy you. Readers can't block writers, writers can't block readers, and writers can only block other writers trying to write the same row.