4 ms·
Innodb 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 lock
by hilbertseries 6y ago
Innodb 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?