Hacker News Reader

Comment by AlisdairO | Hacker News Reader

I believe that InnoDB is an MVCC implementation, so readers blocking writes shouldn't happen. Another thing to add to your list is missing window functions.

I'm not a big MySQL fan at all, but it's still leaps and bounds ahead of mongo technologically.

View on HN

Just ran into this link, which seems to describe how MVCC can sometimes not be enough.

http://ronaldbradford.com/blog/understanding-innodb-mvcc-200...

Having read through it, I rather suspect that that's not a matter of writers blocking readers or the other way round, but instead a case of writers blocking writers - he's writing a lot of data to the table, and it's highly likely that InnoDB has escalated the lock to a table lock - which effectively prevents concurrent writes.

I don't believe that InnoDB escalates locks. Quoting from the fine manual:

http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-mo...

> InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default, in the style of Oracle. The lock information in InnoDB is stored so space-efficiently that lock escalation is not needed: Typically, several users are permitted to lock every row in InnoDB tables, or any random subset of the rows, without causing InnoDB memory exhaustion.

Readers blocking writes may have been changed in a never version than what we use, but it still exist in at least some versions of 5.x.

My apologies - you're right. In the general case readers don't block writes, but InnoDB does use share locks on some foreign key interactions and when using the SERIALIZABLE isolation level.

Reply on news.ycombinator.com