IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC.
(Edited: typo)
IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC.
(Edited: typo)
Regardless of that, my main point is that even reading requires transactional bookkeeping in the database - which some people don't realise. If you read a row and, do nothing and then rollback a few ms later (common in webapps) there is still bookkeeping to be done.
SQL databases are complicated and writes can happen for many reasons - if you manage to bloat the working set by reading more stuff concurrently (old rows, etc) it's not hard to imagine something having to be paged out as a consequence.
> SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm...
Right...but I have a feeling that most libraries/frameworks put you in a transaction by default and just rollback at the end of the request lifecycle.
For instance in rails and django you need to explicitly specify a transaction. Can you give an example of a framework that turns off auto commit by default and instead runs it at the end of http requests?
Making MVCC Mode 'Opt In' requires enabling Snapshot Isolation, and making it default requires another change. It's also worth noting the way MSSQL does MVCC is far more TempDB/Memory hungry than Oracle/Postgres (due to the nature of using snapshots). I've been in more than one shop that did not want to enable it due to the memory requirements.