4 ms·
> in SQL even reads create locks. IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC. (Edited: typo)
by nine_k 6y ago
> in SQL even reads create locks.
IIRC, of all popular SQL databases, only MySQL uses locks at all. Postgres, MS SQL, Oracle all use MVCC.
(Edited: typo)
- hilbertseries 6y agoInnodb does not create locks for reads, unless in a transaction. > 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.html https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.htm...
- calpaterson 6y ago> Innodb does not create locks for reads, unless in a transaction. 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.
- hilbertseries 6y agoI’ve worked with many libraries and frameworks and I haven’t seen transactions by default. Given how easy it is to deadlock yourself this way, for instance by naively making batch reads in random order. I doubt many libraries would make transactions the default. 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?
- calpaterson 6y agoMySQM? MySQL presumably? Anyway MVCC is implemented via locks (among other things, like copying, transaction ids, visibility maps, etc). 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.
- spacemanmatt 6y agoBut it shouldn't result in a write to disk unless a page is dirtied.
- calpaterson 6y agoI wasn't talking about writes but as an aside: I wouldn't bet on that. 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.
- spacemanmatt 6y agoQuite agreed. It's still way harder and way less frequent for read activity to force much write activity, although not hard to imagine.
- csharptwdec19 6y agoMSSQL will lock on Reads by default, and can(will) escalate locks to entire pages for reads. 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.